New Power BI Reports from a Golden Dataset

Last week I wrote an article at PowerPivotPro.com explaining how to create a new report on PowerBI.com that connects to an existing dataset already loaded on PowerBI.com.  I then discovered (thanks to reader feedback) that Microsoft has already delivered a preview of a new feature called “Power BI Service Live Connection”. This new feature allows direct connection of a new Power BI Desktop report connected to a dataset loaded on PowerBI.com.  Believe me – this is another game changing feature the continues to fill out the capability of this fabulous suite of tools.  I think I missed this because I was so excited about the new Quick Measures and Query by Example that were also delivered at the same time.

Golden DatasetI am calling this new features “The Golden Dataset” (you heard it here first).  This is a dataset that is loaded to Power BI with or without a report attached. (Actually currently you must have a report, even if it is blank, but you can delete it once loaded if you like).  Once you have your Golden Dataset loaded, you can build as many new reports as you need as ‘thin’ reports by first connecting to your Golden Dataset loaded on PowerBI.com directly from Power BI Destkop.    Believe me, this is the way it will be done most of the time moving forward.  The main advantage is that you only have 1 dataset to maintain (one for each set of data of course).  You can maintain and improve your model in the Golden Dataset and then simply build the reports you need from there.

In this blog article I am going to show you how it all works with a series of test scenarios.

First Turn On the Preview Feature

Before you can use this new feature, you first must make sure you have the latest version of Power BI Desktop (April 2017+).  Then you must turn on the preview feature.

File\Options and Settings\Options and turn on the feature as shown below.

image

Once this is done, restart Power BI Desktop.

Create a New Report

Create a new blank report and select “Power BI Service” as shown below – super exciting don’t you think?!

image

You will be presented with a list of datasets that you can access.  Firstly I am connecting to a Dataset in myWorkspace as you can see below.

image

I created a simple report that points to the dataset above, then published to PowerBI.com.  After publishing I could see the new report (as shown below).  Note that this has been published in the myWorkspace for the user called Training20

image

Download for Backup

I then immediately downloaded the new report from the service back to my PC using the Download PBIX feature.  As you can see below, this file is only 7KB, so that is a clear indication that the PBIX download did not copy the data model, but instead retains a direct connection to the dataset on the service.

image

 

I was able to open this copy I downloaded (in Power BI Desktop) and interact with the data just fine – sweet.

Switch Users

I then logged out of Power BI Desktop and logged in as a different user.  Remember I published the dataset to the myWorkSpace for user Training20.  When I logged in to Power BI Desktop as user Training19, and then reopened the Test 1.PBIX file, I got the following message.

image

Excellent – just as I would have expected.

Create a Dashboard and Share

The next thing I did was to pin a tile to a new Dashboard and shared that Dashboard with user Training19.

image

I closed Power BI Desktop and then reopened it as Training19.  I still couldn’t access the report – I was expecting that maybe I should be able to see it actually.  I then logged into PowerBI.com as Training19.  I was able to see the dashboard.  When I clicked on the dashboard, I was able to see the report as shown below.

image

 

I then decided to close PowerBI desktop and reopen it – to see if I could now connect (after accessing the dashboard).  No I couldn’t – still no banana.  OK, no harm done here I guess, but I figure if I can access the report (via the Dashboard) in Power BI service, why not here? Anyway, that is how it works.

Publish This Report to a Shared Workspace

The next thing I did was to change my Test 1 report to Test 2, and then tried to publish it to a shared workspace that both Training20 and Training19 both have access to.  I kept the original link to the dataset in my Power BI Desktop workbook ie my Test 2 report was still connected to a dataset in myWorkspace for user Training20.  My plan was to then publish this to a shared workspace but I wasn’t able to.  That looks like a good design to me.  It seems you can only publish one of these thin reports to the same workgroup where you dataset is located.

Publish a New Report and Dataset to a Shared Workspace

The next thing I did was to load a new Golden Dataset to a shared workspace.  You can see my workspace below (1) and the new dataset (2) and report(3).

image

I then deleted the report (3 above) so I was just left with the Golden Dataset.  My Report Author Demo workspace is shared with user Training19 already so it will be there for Training19 when I need it.

I copied my Test 2 workbook and wanted to re-point the workbook/report to the new workspace, but I wasn’t able to make the change.  I hope Microsoft changes this in the future.  I can’t see any reason why I should not be able to re-point this workbook to a different copy of the same dataset in a different location.  I did some chatter on Twitter about a way to hack this by renaming your PBIX file to a ZIP file and editing the Connection string.  I took a peek and it looks doable, but I haven’t done it.

image

So I created a new Report Test 3 connected to my Golden Dataset and published it.  Once again I was not asked which workspace – it just published to the workspace where the source Golden Dataset was located.

I then signed in as the other user Training19 and I was able to see the report just fine.  I also then downloaded a copy of the report (as Training19) to my desktop and opened it in Power BI Desktop signed in as Training 19.  This time I had no problems interacting with the report as Training 19 given I have been given shared access via the service.

Summary of Key Points

  • You can now upload a PBIX file to the service and have it as a Golden Dataset (one ring to rule them all).
  • You can then create as many new reports as you want directly connected to this Golden Dataset.
  • When you publish these new “thin” reports to the service, they will be published to the same WorkGroup where the Golden Dataset resides.  For this reason you should load your Golden Dataset to a shared workgroup assuming you want to share it (Pro feature).
  • Anyone with access to the report 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 service, 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.
  • If you want to share reports with others, then load to a shared workspace and give them access.  This is good general advice anyway.

What do you think?

What do you think of this new feature?  Do you agree this will change everything – again?  How do you plan to use this new feature?

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…

    • 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

  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.

Leave a 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