New Power BI Reports from a Golden Dataset - Excelerator BI

New Power BI Reports from a Golden Dataset

This is a further (third) update/edit to an article I first wrote on 18th April 2017 and then updated on 30th Jan 2018.  When I update my articles I simply change the original post with the latest data – that way only the current/latest version is available preventing people stumbling on old advice.

As Power BI becomes more and more pervasive in the business world, I am being increasingly asked “How can I build 1 master dataset, use it as the 1 version of the truth, but still allow people to do their own thing”?  My concept of the Golden Dataset (you heard the term here first, in 2017) will allow you to do just that – read on to find out how.

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.  This is made worse by the fact that you can publish the same data model to multiple workspaces at PowerBI.com.  Note: data model and dataset are essentially the same thing – I refer to data model when using Power BI Desktop, but when it is published to PowerBI.com it is officially called a dataset.

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 single master 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.  This will use less space but also eliminate the proliferation of slightly different versions of the data model and minimise the effort to keep the models up to date.
  • You can maintain and improve your single data model in the Golden Dataset master PBIX and make the changes available to all users and dependent reports.
  • 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 – local to their thin workbook.
  • 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 (kinda like using an SSAS Tabular data model) without having to invest in all that infrastructure.

This is how it works.

Publish Your Golden Dataset

There are actually a few ways you can do this.  I learnt options 2 and 3 below the last time I updated this article (from comments from my blog readers).

Your options are:

  1. Publish your Golden Dataset direct to a Power BI App Workspace.  1 App Workspace has the only copy of the Golden Dataset.
  2. Save your Golden Dataset to OneDrive for Business and import to one or more App Workspaces from there.
  3. Save your Golden Dataset to SharePoint Online and import to one or more App Workspaces from there.

The advantage of options 2 and 3 above is that you can have 1 dataset, and then load it to many workspaces while still having a single dataset to maintain.  If you set it up this way, you will end up with something like I have illustrated in the image below.  The advantage – there is only 1 dataset to maintain – the Golden Dataset – but it can be deployed to as many different App Workspaces as needed.

image

Loading From OneDrive

To demo how this works, I first saved an Adventure Works PBIX to my OneDrive for Business folder on my PC.  If you do this in your environment, you will probably want to save your PBIX to a shared OneDrive folder that is available to everyone that needs to use it from PowerBI.com.  Or you can save it to a SharePoint document library that is available to others to access.

image

I then created 2 new Test App Workspaces at PowerBI.com.  To load the PBIX file from OneDrive into each Power BI workspace, I simply

  1. Went to the workspace
  2. Selected Get Data
  3. Files

image

On the next screen, I couldn’t see my OneDrive for Business folder option (my personal OneDrive for Business folder).  The OneDrive option (1 below) is actually a dedicated OneDrive folder for this App Workspace.  But that doesn’t mean I can’t access files from my personal OneDrive for Business folder. To do this, I selected the SharePoint option (2 below).

image

When asked for the URL, I simply cut and paste the URL from my browser that points to my OneDrive account.  You can tell by the address in the URL that OneDrive is actually integrated with SharePoint – which is why this works.

image

I then repeated the process for App Workspace Test 2, essentially giving me 2 “copies” of the one master Golden Dataset – one copy loaded to each workspace.

I stress again, in this demo I am connecting to my Golden Dataset from my personal OneDrive for Business folder. If you are doing this inside your enterprise, you would probably use a shared OneDrive folder accessible to those that need the Golden Dataset, or a SharePoint document library.

Making Changes to The Golden Dataset

The real benefits of this approach are realised when you make changes to the Golden Dataset.  To demonstrate how this works, when I first saved the PBIX to my personal OneDrive for Business folder on my PC, it had a table called “test”.  You can see this table visible at PowerBI.com in App Workspace Test 1 below.

image

I went to my PC (local copy of my OneDrive for Business), opened the Golden Dataset, deleted the test table, saved the file and waited.  I checked OneDrive online and saw the updated copy of the file appeared almost immediately (keep in mind this test workbook is small, at just 2MB.  A bigger workbook will take longer to upload).  It took a bit longer for the Golden Dataset to flow through to the App Workspaces in Power BI.  I seem to remember hearing that this replication takes place every hour or so (maybe if someone knows the exact cycle you could let me know in the comments below).  This is automatic – you do not need to install a gateway to make this work.

Time to Create a New Thin 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 in Power BI Desktop, 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 (Adventure Works Test) in workspace Test 1 shown above and then created a report from there.  I saved the thin Power BI Desktop file with the name Golden Dataset Demo.  The new thin workbook does not have a data view or relationship view, only the report view as shown below.  Also the thin workbook doesn’t have a copy of the data, so it will only work when you have direct access to PowerBI.com (keep that in mind if you want access when you are out of Internet coverage).

image

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).
If, down the track someone updates the main dataset with a measure with the same name as the one you created locally, your local measure will show an error in your thin workbook and the new measure will appear, working in place of your local version.

One of my readers, 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

Publish the Thin Workbook

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 thin workbook containing a report – both pointing to the Golden Dataset in the same workspace.  After publishing I could see the new report (as highlighted below).

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 while in PowerBI.com (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

Now for some really good news.  It is possible to repoint an existing thin Power BI Desktop workbook to a different dataset/workspace. Now of course you wouldn’t want to repoint a Sales report to a Finance dataset.  So if you take this Golden Dataset approach and later change your mind which workspace to point to, you can easily repoint the thin workbook to a new dataset.

To test this out I did the following.

  1. I went to Edit Queries in my Thin Workbook
  2. Data Source Settings
  3. From there I was able to change the source and point the Thin workbook to another dataset.

image

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 also save the PBIX file to OneDrive or SharePoint with the added benefit that you can have many workspaces using the one Golden Dataset.
  • 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 effectively creating new thing workbooks.
    • Directly create new reports online in the 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.  This can be good if you started your journey without using a Golden Dataset but now want to backtrack and sort this out.

Let me know what you think in the comments below.  I also would love to hear any other ideas you have on how to get the most from the Golden Dataset.

81 thoughts on “New Power BI Reports from a Golden Dataset”

  1. Interesting question. I can’t say I have ever tested this, but I am surprised to hear it. There is no data in the thin workbook, but it would store the last query in the visuals, I guess. What happens when you interact with the workbook. It then must send a refresh request.

    1. I ended up working with Microsoft Power BI Support. It took a week or more of testing with them. We found that the underlying Master Data Set, if stored on one-drive must be refreshed a minimum of 5 times during the day, even though the underlying data itself had no changes. Now any measure changes in the master set will be immediately updated in the thin client report. Also, If I add/modify/delete visuals in my thin client report, they will now update on the Power BI Service immediately. It was hard to problem solve as the thin client always was updated with the values from the data from the master data set, just new measures and visuals would take a long time. I was waiting up to 5 hours sometimes for a visual modification to show up on my thin client report.

      1. Hi Angela. I am really interested to find out more about this. What do you mean it must be refreshed a minimum of 5 times per day? Do you mean if you refresh it once, then nothing happens?

  2. If I open my thin report on my Power BI Desktop, why does it not update automatically from the Golden Dataset?

  3. I have gone with option #2 of the golden dataset and it has worked very well until recently. Before my golden dataset grew in size, my thin report would update pretty quick. All of my reports are created through Power BI Desktop. Now I am waiting up to 2 or more hours. This has proven a little frustrating as I update or add measures. I can’t utilize the revisions or updates on my thin report visuals for awhile. Would option 1 work quicker? Also, if I went to option 1, can you create a report on the Golden Dataset and save it to a different workspace. I’m not clear on this one.

    1. Yes, option 1 would make the published version immediately available (after it is published). All you lose is the ability to automatically publish to more than one workspace. But you can still manually publish to multiple. You should be able to switch – just publish it manually. If the dataset is duplicates ( same name, twice), you should be able to repoint your existing thin workbook to the new one.

  4. Patrick McMonagle

    Hi Matt, thanks for sharing the approach – just what I’d been looking for.
    Trying to implement it now, creating some thin workbooks but have been experiencing some issues while creating thin workbooks in the Desktop. Just wondering if you’d experienced anything similar or had any ideas?
    “Cannot load model. We couldn’t connect to your model in the Power BI service. The dataset may have been deleted, renamed, moved, or it is possible that you don’t have permission to access it. Failed to connect to the URL ‘https://api.powerbi.com’ white attempting to call cluster connection”

    I cannot see what I have changed to affect this, and it seems intermittent across thin workbooks. I’m guessing it could just be a temporary connection error to the website, though the most frustrating part is any combination of trying to ‘Cancel’ or ‘X’ the dialog box does not work, so each time requires a forced shutdown of the desktop. Luckily recovered files are pretty good but would be good to understand what’s happening.

    Thanks, Patrick

    1. No, I haven’t seen this before. If you are sure you have not changed anything, I suggest you use the diagnostic tools inside desktop and create a support request. File\Options and Settings\Options\Diagnostics

  5. Hi Matt
    Super useful trick!
    Thanks for sharing.

    If you set everything up like this, do you know a way to keep track of which dataset & reports actually link back to a certain ‘golden dataset’? Can we see the original link or something somewhere?

    Thanks!
    KR
    Tom

  6. Calvin Francart

    We have actually been talking about this solution for our instance of PBI – so very, very glad to come across your blog. But something came up today that I was wondering if you have any experience with. We have a group of datasets that feed individual reports. The report author left the company, and when their AD account was removed, we found ourselves having to scramble to assign ownership of every dataset to someone else because the reports could not load. Have you ever tried assigning ownership of a Golden Dataset to a non-person AD account? Just wondering if assigning to a group would allow anyone within that group to make changes rather than having to go to just one person all the time – and also not having to manage the ownership in the event they exit.

    1. I’m glad this has helped Calvin. Yes what you describe is a common problem. There is a solution to moving ownership using the Rest APIs. https://docs.microsoft.com/en-gb/rest/api/power-bi/ I am not an expert and have never used the API, but I understand you can solve most issues like the one you refer to. Regarding of assigning ownership to a non-person, that may be a licensing issue. Regardless, I think if you follow my advice and always use a workspace with multiple administrators then you wont have a problem. This problem only occurs when people share from their own MyWorkspace. I always recommend people never do that, for this exact reason.

  7. Hi Matt,
    Given that MS has introduced Dataflows, will Dataflow replace as the solution for Golden dataset?
    What scenarios do you think the solution outlined in theis blog will be still useful?
    I am trying to understnad scenarios where the solution here can be used.
    Thanks,
    Anand

  8. Hi Matt,
    How is this Golden Data approach different from creating an App or App workspace?
    Or when should we consider App vs. your suggested approach #2 or #3?

    1. One thing Microsoft is often bad at, is its naming of things. I think App Workspace is a dumb name. It is just a workspace – a place to collaborate with others. An App is a “package” of a dataset, reports and dashboards packaged up as an “app” that can be distributed to others, including people outside your organisation. The Golden Dataset is something different. It is a “master” version of the data model that can be deployed into 1 or more Apps and 1 or more Workspaces without having to create duplicate copies. You create 1 golden dataset and the reuse it in may places. Sharepoint and OneDrive keep all uses in sync.

  9. This is a further (third) update to an article …
    Interesting information, I definitely like the idea of a master data set that everyone works from. (not too keen on giving private data to MS, Azure, yes I’m paranoid… )

    You said this is the third version of the article. Presumably it would be useful to read the earlier articles, but like too many other authors you make it too hard to find. You provide the dates of the earlier articles, but when I look on your blog list, THERE ARE NO DATES, just titles! WHY NO LINKS in this article to the ones you refer to. It is so easy for you to do …

    1. Fair point Ron. The issue is that I didn’t make it clear – obviously. I edited the original articles and re-released them. The old versions therefore don’t exist anymore – that is why there are no links and you can’t find them. I do this deliberately because the alternative is I would have stale content (in this case 3 articles about the Golden Dataset) and I run the risk that people don’t read the latest and greatest. I will try to make it clearer that the originals have been edited.

  10. You are right, these options don’t auto refresh. You need to do it manually, or you can use a tool like power update. I’m thinking now that you could create the golden dataset with connections to dataflows, and schedule dataflows to keep the data refreshed. I might write an update about that.

  11. This is a great article – maybe there is a new way doing this with Power BI dataflows?

    It wasn’t that clear to me – how do you refresh your golden dataset for options 2 and 3?
    I really just want to create this one ‘golden’ dataset and publish it to Powerbi.com and then have reports in 2 other workspaces using it.

  12. One thing to note is that if you click “save as” on a thin report connected to a golden dataset in the Power BI service, then the new report will no longer be thin as it will have copied and attached the golden dataset to itself. I found this to be a problem when I published a report on a clients tenancy. He made a copy of the report by clicking “save as” and then he did a bit of work on this new copy. His work had to be later redone as you don’t seem to be able to separate the two again

  13. I guess that the new Composite Model functionality is another answer to the question “How can I build 1 master dataset, use it as the 1 version of the truth, but still allow people to do their own thing?” Because now you can use DirectQuery to provision the equivalent of the ‘Golden Dataset’, while still allowing users the flexibility of doing additional modelling via Import.

    1. @Jeff Weir – I was thinking the same thing. Many of my users “do their own thing” but just adding that one or two additional Excel worksheets that are there to just enrich the golden dataset with a few columns of their own. In Excel they used these sheets for “VLOOKUP” purposes. Prior to the July 2018 update, doing the live connection to the Power BI Service meant that users were unable to add their own imported data to this published data model (just as you said.) So that has caused me to shy away from recommending this as a best practice for everyone.

      A possible enhancement to this approach could be to store Power BI template files (.pbit) of your saved pbix files in SharePoint as well. If users needed to get a local copy of the golden dataset, they could then get this pbit and refresh. However, they would have to know that they were making a copy of the golden dataset (a fool’s gold dataset?) and there would need to be a clear method to handle these multiple data models with slight mutations.

    2. There is a BIG difference between Direct Query mode and Import Mode, and I wouldn’t want to give up the benefits of Import just to use it as a control mechanism for standardisation – JMO

  14. Kamil Kaleciński

    One thing that worries me from the very beginning – Once I will change my position in the company, is there a way to smoothly transfer the dataset (either loaded to OneDrive or directly to PBI account) to one of my colleagues account, without losing highly developed set of workspaces, reports and group of recipients?

    1. Always create a new App Workspace. That way admin rights can be shared and transferred. Use an internally public OneDrive or Sharepoint folder to store the Golden Dataset. Do those things and all will be fine. Even if you do it in your personal workspace, Power BI admins have tools to make changes and repoint workspaces and workbooks

    1. Report server is a whole different topic. You need 1) Report Server (of course) and 2) the specifically edition of power bi desktop that is used to publish to the server

      1. I have both conditions, the Reports server and the PowerBI desktop optimized for the report server.
        I am not able to import the dataset to the server.

        1. Sorry for the confusion in my reply. You “publish” a report to report server by using the optimised Power bi desktop version. But report server is not Powerbi.com, and I guess you can’t use his technique with report server.

  15. Thanks Matt … this is a very helpful article.
    I’ve been using a golden dataset for a while now – loading it to 3 different workspaces and having a separate refresh schedule in each workspace. Had no idea till I found your article that it could be a single dataset “serving” the 3 workspaces.
    I’m a bit unsure how this would work with refreshing the dataset via a PBI Gateway. Jerry’s comment earlier suggests this can work, but how would one configure this? Would you configure the dataset in all 3 workspaces to refresh via the Gateway? Or, if you setup the refresh schedule in just one of the workspaces, would all 3 workspaces reflect the refreshed data? Has anyone tried this?

    1. When you load to OneDrive and then import to a workspace, the workspace has its own copy of the data set (3 workspaces means 3 copies – hence 4 in total). Power BI and OneDrive talk to each other. When the OneDrive version is updated, any workspace versions will automatically be updated. So you have 2 options. 1) refresh the OneDrive version and hav eit auto flow through, or 2) configure the gateway for all workspace copies (3 gateway configurations in your case). OneDrive doesn’t have the ability to refresh itself. The file needs to be “opened” and then refreshed and saved. You could do this with Power Update ($500) or just use the gateway on all copies.

  16. Is the only difference between storing the model (ie dataset) in the cloud (onedrive/sharepoint) verses on premise with a gateway (when you want to publish to multiple workspaces): When changes are made to the model the cloud options will automatically update the model in ALL workspaces and the on premise version would have to be published manually to each workspace where the model has been previously published?
    There is no difference in the refresh scheduling, correct?
    For both on premise and cloud datasets deployed to multiple workspaces you still have to setup maintain the refresh schedules in each separate workspace, correct?
    There is no difference in permissions to make changes to the dataset, correct?
    The users have to have edit permissions in the workspace, they therefore can download the dataset, make changes and republish and override it?
    Thank you.

  17. Hi Matt,
    Great article.
    I just want to add some demo I have done so far based on your article.

    I’m always looking for this kind of Golden data set, but also a Golden reports (then customize for each app). So with the help of your article, I finally “almost” there. I have tried to pass by the tabular model but all the reports are disappear (pbix > Azure Analysis Services > obtain tabular model) so I dont really appreciate this method.
    So my approach to have golden dataset and reports is :
    1/ I have a golden dataset with all the reports for different group of users but each group will use only a subset of reports and I dont want to have them in different places so I have only one pbix file (the viz works perfectly with RLS so I dont need to apply filter on group of business users)

    2/ With the help of your article, the onedrive master pbix file synchronizes with their “Import pbix” ‘s file. But I dont want them to sync (I dont want the users see reports that are not belong to their business) so I turn off the sync One Drive option, activate Schedule Refresh.

    3/ By doing that, every time I want to modify the report, I can just go and modify the original and then activate sync, and by the end I will delete the non-necessary reports for this app.

    Let me know what do you think about this approach!
    Thanks again for this great article.
    Dung Anh

  18. Nice article, Matt. I work in an organisation that hasn’t yet fully come to exploit what Power BI has to offer and hence didn’t really have the chance to see this being used. I created something similar to what you have described before the connect to Power BI feature was made available

    The inability to view the data model and the relationships will be a pain-point to create custom measures. You mentioned about migrating measures to the Golden Dataset? Is this something as trivial as sending an email to the “owner” of the Golden Dataset to add in the desired measures? Or is it something that will be uploaded to the thin report on PowerBI.com

    Also, if the golden dataset as row level security enforced, would that affect creating custom measures in some way?

    Thanks

    1. Yes, I think the process to migrate measures could be a simple as an email. They key is that everyone needs to know what to do. As for RLS, that is a great question – I have never tested this, but I assume that the new thin workbook would have the golden dataset RLS applied for the user of the thin workbook. Nothing else would really make any sense from a design perspective.

  19. I voted for the idea to make a schema visible in these cases. In any case, a couple of best practice suggestions would be to (a) always avoid hiding key fields and (b) maybe also duplicate the golden model side by side as a fully imported dataset option. To often analysts just want to make small variations but will abandon the golden model if they can’t and then will have a dead end.
    By the way I tested importing all tables directly into Power Pivot tables from a golden model and it works fine; just fYI but again would need the keys to enable relationships to be recreated.

  20. Hi Matt,
    I think this is a really good idea and a great concept. But it feels like Microsoft has some way left before this can be fully used in production. For example, in my case I have 7 similar reports, with only minor modifications. I want to base all the reports on the same data model, for all the benefits you mention above, but also to minimize the workload on the database.

    However, I want some differences in e.g. target values etc. that I wanted set up as parameters. But to my understanding, this cannot be done when designing a “thin” report. I also have some measures designed as indexes, based on the target values. The only solution I can come up with is to hardcode the values into the measures in each report, but it doesn’t seem like a very sustainable alternative. Do you have any ideas how I can tackle this?

    1. I’m not sure what you mean by “measures designed as indexes”. Yes parameters are part of Power Query so cant be in a thin workbook. For your hard coded measures, how about you load a table of all the possible values. Eg with 2 columns (Report name, value). Place a Report level filter in each report as appropriate to match each report. Then a generic measure myMeasure = selectedvalue(table[Value]) should work for all reports, and you can just change the values in the source table when needed.

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

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

      1. 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?

        1. 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?

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

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

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

      1. I have been circulating a pbit thin template with a screen grab of the relationships diagram on page 1. Users then save a thin pbix.

  24. 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?!

      1. 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….

  25. 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!

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

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

  28. 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 ?

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

      1. @mim – Voted – The problem with the voting system is the same idea is there is several places.
        For example the Original Idea by Avi Singh mentioned connecting to both Power BI and Power BI Desktop.
        Microsoft conveniently forgot the Power BI Desktop part and declared the idea as completed.
        And Avi did not use his good offices with MS to push the agenda further

  30. Gary Lanzafama

    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.

  31. “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+

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

    1. Yes I agree Joe. I just called it a dataset because that is the term used by Microsoft on the PowerBI.com It is a golden data model until it is published to PowerBI.com and then it becomes a Golden Dataset

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

Leave a Comment

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

Scroll to Top