This is an update to an article I first wrote on 18th April 2017. At that time, connecting Power BI Desktop directly to the Power BI Service was just a preview feature. This feature is now generally available and hence it seems like a good time to update this post and talk about why you should be considering using this technique.
The Problem – Too Many Data Models
Power BI has so many great features it seems almost sacrilegious to discuss problems, however one of the problems that can occur in the world of Self Service BI is the proliferation of different versions of essentially the same data model. If you think about it, Self Service BI using your own data models has the potential to be the antithesis of Enterprise BI. Instead of having one single data model that is used for all reporting (Enterprise BI), you can build individual bespoke data models each time you have a new problem. This is both powerful and problematic. If you are not careful, before too long you can have an unwieldy mess of uncontrollable and unmanageable data models that are essentially the same at the core but with different reports sitting over the top. My suggested solution to this problem is to create a “Golden Dataset” and use it as a data source in Power BI Desktop (and Power BI Service). It is a great approach. Let me explain.
What is the Golden Dataset?
The Golden Dataset is a master Power BI Data Model that is loaded to PowerBI.com with or without a report attached. (Actually currently you must have at least a single page in your report, even if it is blank, but you can delete it once loaded to PowerBI.com if you like). Once you have your Golden Dataset loaded to PowerBI.com, you can build as many new ‘thin’ reports as you need directly from Power BI Desktop by connecting Power BI Desktop directly to PowerBI.com with Get Data -> Power BI Service.
Note: A thin workbook is one that doesn’t contain a data model itself, but links into a central data model (in this case stored on PowerBI.com).
The advantages of taking this approach are:
- You only have one dataset/data model to maintain (and use less space).
- You can maintain and improve your single data model in the Golden Dataset and make the changes available to all users and dependent reports instantly.
- You (and everyone else with access rights) can simply build new ‘thin’ reports connected to the Golden Dataset as needed.
- Anyone building a new report from the Golden Dataset will be able to add their own “local measures”.
- With the implementation of a suitable business process, the best local measures created by users can be retrospectively migrated into the Golden Dataset for the benefit of all.
- You get the benefits of an enterprise solution (e.g. using an SSAS Tabular data model) without having to invest in all that infrastructure.
In this blog article I am going to show you how it works.
Publish Your Golden Dataset
The first thing to do is to publish a Golden Dataset to PowerBI.com. You will need to ensure you login from Power BI Desktop and PowerBI.com with the same login ID.
I have used one of my old Adventure Works files for this article (I called it Sample Power BI Workbook.pbix) and published it into an App Workspace. Anytime you intend to share a dataset with other users in the organisation you should use an App Workspace and not your personal Workspace. This implies of course that this technique only works for sharing with others if you have a pro account. I called the App Workspace in this demo the Golden Dataset Demo.
Time to Create a New Power BI Desktop Report
Time to create a new thin report using Power BI Desktop. To do this, I simply opened a new blank pbix file and selected “Power BI Service” as the data source (shown below).
If you are not logged in when you do this, you will be prompted to log in before proceeding. Once logged in, you will be presented with a list of datasets that you can access from your various workspaces (see my demo examples below).
In my example, I selected the Golden Dataset (Sample Power BI Workbook) shown above and then created a quick report, then saved the Power BI Desktop file with the name Golden Dataset Demo. Note the database icon in the image above indicating this is a dataset. This gave me a new report in a new Power BI Desktop file as shown below (after I added some visuals). In this thin workbook there is no data view or relationship view.
I then published this thin workbook to PowerBI.com (the same workspace as before). I therefore have both a desktop file AND a new PowerBI.com report – both pointing to the Golden Dataset. After publishing I could see the new report (as highlighted below).
As mentioned earlier, one of the benefits of this approach is that a user can connect to the Golden Dataset but still create local measures when needed. Here is my thin Power BI Desktop workbook below (#1 below). Notice I can right click on a table in the Fields List and create a new Measure (#2 below).
Any new measures you create in a thin workbook are stored as part of the thin workbook and are not visible in the Golden Dataset. (Note, you can’t create calculated columns or new tables, only measures).
edit: 31 Jan 2018. Sean commented below that it is difficult to write local measures if you can’t see the relationship view. This is an excellent point and it really needs to be addressed. I have created a new idea at ideas.powerbi.com to have a read only view of the relationships. Please vote for it here https://goo.gl/1XYdiu
Power BI Service Report
Of course you can use the Golden Dataset to create a PowerBI.com service only report (as you can with any dataset). To do this, simply click on the dataset (1 below) and then build your report as needed.
After you save the report (top right hand corner of PowerBI.com) you will see your new report in the reports list on the left
And you can download a desktop version of this report by clicking File\Download Report as shown below.
Switch Reports to a Different Golden Dataset
Just as I was wrapping up this post, it occurred to me that it may be possible to repoint an existing thin Power BI Desktop workbook to a different dataset. To test this out I did the following.
- I opened the Golden Dataset in Power BI Desktop
- I logged out of the [email protected] Power BI Account and logged in with main Power BI account credentials.
- I uploaded a second copy of the Golden Dataset to the personal workspace of my second account.
- I then opened the original thin workbook in Power BI Desktop. Not surprisingly it couldn’t find the Golden Dataset. But then the following dialog appeared prompting me to select a new data source.
- I selected the copy of the Golden Dataset (shown as 2 above) and it then worked perfectly as before.
Summary of Key Points
- You can upload a PBIX file to the service and use it as a Golden Dataset (one dataset to rule them all).
- You can then create as many new reports as you want directly connected to this Golden Dataset.
- Connect a new blank Power BI Desktop file.
- Directly with Power BI Service.
- If you create a thin workbook connected to the golden dataset and create a report, you can publish it to the same app workspace and it will work.
- Anyone with access to the workspace can download a copy of the thin report to their desktop and successfully interact with it on Power BI Desktop. If you later remove their access rights to the workspace, then the downloaded workbook will stop working – very cool.
- You can modify and update your Golden Dataset and republish at any time. Any thin workbooks that you have created connected to the Golden Dataset will be able to access the changes you have published –sweet again.
- Any user with access rights can build local measures to a thin Power BI Desktop workbook without impacting the Golden Dataset.
- With a suitable business process, end users can advise the owner (admin) of the Golden Dataset of any useful local measures that have been created. The administrator can then migrate these measures to the Golden Dataset for the benefit of everyone.
- If needed, you can repoint any thin workbook to a different copy of the Golden Dataset if needed.