New Power BI Reports from a Golden Dataset

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?

Golden DatasetThe 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).

image

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).

image

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.

image

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).

Local Measures

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.

image

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

image

And you can download a desktop version of this report by clicking File\Download Report as shown below.

image

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.

      1. I opened the Golden Dataset in Power BI Desktop
      2. I logged out of the [email protected] Power BI Account and logged in with main Power BI account credentials.
      3. I uploaded a second copy of the Golden Dataset to the personal workspace of my second account.
      4. 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.

      5. 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.
Share?

Comments

  1. Matt, yes Excellent, but i am chasing a different scenario, I Want to query the “Golden” dataset from Excel/Powerquery , not live connections but load some data into a local Powerpivot Model.
    Live query is nice and all, but it is a pro feature as you have pointed out, it requires a shared workspace.

    i tried to play with the string connections but not luck so far.

    but i agree it is a game changer for pro users.

  2. Would you consider this a golden “Data model” instead of a dataset? I’ve usually referred to a dataset as a single table…

  3. “however from experience loading data from a SSAS model into Power Pivot is pretty painful.” yes indeed, but it is one of the most cost effective way to distribute large numbers of reports, of course assuming you have Excel 2013+

  4. Yes! It’s a game changer. An issue I have with a single model is the number of measures has increased over time, which can be confusing for the developer and user. I am hoping that this will allow a “modular” approach. Each report can focus on a specific dimension table or Time Intelligence each with a more limited number of measures.

  5. The Game changers are yet to come.
    1. Connect to Data model in Power BI Desktop file from Excel (w/o all the hacks)
    2. Connect on an Excel/Access DB stored in One Drive personal
    3. Password protect the Queries / Data model – to give some sense on security.

  6. Excellent new feature! Very well described. Casual users can benefit from Power users’ modelling skills much better now ! It sounds like it is Microsoft’s goal for each enhancement of Power BI. Power users drive the development and production that Casual users can enjoy peacefully.
    When you talk about a “shared workspace”, it is actually an Office 365 Group accessible in any 0365 application right ?

  7. You can create a content pack of the dataset and report that connects to the service and share the pack with the new group. The data refreshed in the original dataset and group is reflected automatically in the new group via content pack.

  8. My wish for the next advancement on this feature is to allow connection to multiple “golden dataset” in the same report, to allow the most flexibility in data steward process, with PBI as the convenient single platform to manage the data modeling of various data sources.

  9. Take a bow Sir!

    This is fantastic and I’ve felt in my bones I’ve needed to arrive at this place for a while now. I don’t have the facility to set up SSAS and nor do I have the inclination

    This is a superb alternative and does the job very nicely indeed!

  10. Sorry, the reason I turned off from this idea in the past is that the the measures in the golden model weren’t ‘exposed’ for usage, is this now the case?

    They are when you use Analyse in Excel so I’m hoping yes?!

      • By exposed, i mean you can see the list of pre-defined measures created in the Golden Report from within a thin PBI desktop file following connection. (in the same way as you can see them in Excel). Let me try….

  11. I like this idea, but have a hard time seeing how end users will be able to write anything other than the most basic reports and measures if they don’t understand the structure of the model. This puts a real burden on the model designer to put a tremendous amount of attention to, for example, naming fields and tables to relay that information. Even then, I’d have a concern that they may create a measure that does not work as they expect and, as a result, report incorrect BI because of it.

    • The way I dealt with one of those issues was to duplicate some measures in the form of a ‘semantic later’, if you will. A set of friendly names for users completely separate to the base measures as I call them

  12. Not sure if this is already well know, in my scenario I am making this Golden Dataset available to use in multiple App Workspaces, to maximize the application of “single source of truth”. This is done by using Sharepoint to house the Golden Dataset (PBIX), then in each of the App Workspaces use Get Data –> Get Files –> Sharepoint Teamsites, then point to where the PBIX file is uploaded.

      • Hi Matt, I haven’t see any documentation or blog from anyone on this scenario, it seems pretty basic so I just assumed it was common knowledge. The setup is pretty straightforward, using Sharepoint as the cloud storage for PBIX files, and by using the Get Files from Sharepoint method as I mentioned, in each App Workspace, the Golden Dataset is now available for use in each App Workspace, and subsequently production Apps. Future updates in the Golden Dataset (data refresh, measures, relationships) will reflect to each App Workspace. If this is not common knowledge, it might be great if you can mention this in your blog post, and also recommend Microsoft to add to their documentations. What do you think?

        • I agree. I will check it out and update this article. Are you using SharePoint Online? I don’t use it much, so this is part of the reason I don’t know about it. Are you just sharing to a SharePoint Library?

          • Hi Matt, for smaller datasets I am using a data gateway to refresh the dataset in Sharepoint. The dataset appears in PBI Service the same way as if it were an direct publish from desktop, and has the same refresh options on the Service.
            I have a couple more complex datasets that the data gateway can’t handle just yet due to timeout issue, so I am manually refreshing them in Desktop on a daily basis, then upload the PBIX file to Sharepoint. The Sharepoint file will sync with PBI Service within an hour, then the updated data will show up in the Service for various Apps and Reports.

            Our team hasn’t moved into more enterprise-grade platforms (such as Informatica or SSAS) for dataset management yet, so this solution with PBIX and Sharepoint works well for now to manage our datasets.

  13. Maybe I’m missing something but you can connect to a golden dataset if your starting from fresh with a new pbix, but what happens if you already have a pbix and you simply want to cherry pick some data / measures from another data model? My attempts at this proved unsuccessful (cannot recall the message, something about multiple online sources) but am happy to be proved wrong.

Leave a Reply to sam Cancel reply

Your email address will not be published. Required fields are marked *

Ten Things I Wish I Knew When I Started With Power Pivot.   Learn  Without The Pain!

x