I'm a big fan of reusing Power BI datasets whenever possible to minimize redundant datasets floating around the organization. It's less maintenance, less chance of calculation differences, and less data refreshes to schedule. And, this technique is a way to separate who handles report creation vs. creating the dataset, calculations, and relationships (which is typically far fewer people).

Info verified as of: May 20, 2017

In this post I'm referring to datasets which are imported into Power BI (thus requiring a data refresh to update the imported data). We're already "reusing" a dataset which is in DirectQuery or SSAS Live Connection mode, so those are useful techniques too -- just not applicable to this particular post.

To reuse an imported dataset, there are three options I'm aware of:

Report in the Power BI Service. This refers to using the web interface for creating a new report separate from the original PBIX file.
Analyze In Excel. This refers to creating an Excel report and can currently be used by anyone with read or edit access to the dataset. Hence, very useful for self-service BI purposes.
Power BI Service Live Connection. This refers to creating a Power BI Desktop report. This option can currently only be used by people with edit permissions on the dataset (so not appropriate for broad self-service BI reporting at this time).

Report in the Power BI Service

In the Power BI Service, if you have edit permissions, the option to create a report is on the Actions menu. It will open up a blank report connected to the dataset:

When you save the new report, it will appear as another report although it shares the same exact underlying dataset. This is a great way to divide up reporting needs for different people, yet use the same exact dataset.

That was the simple one. Next we have...

Analyze In Excel

In the Power BI Service, if you have edit permissions, the option to use Analyze In Excel is on the Actions menu:

If you're a read only user, it's not as prominently displayed. However, it works the same. You can find it through the "Related Content" pane:

The first time you'll be prompted to download and install an Analysis Services OLEDB driver which handles connectivity back to the Power BI dataset in the Service:

The next thing to know is that the connection will be stored in a separate .ODC file (short for Office Data Connection). You'll want to keep all of your .ODC files in a single location, and only have one .ODC file per connection (this makes it easy to change the data connection info later if you need to).

From there, you can create pivot tables, charts, etc like normal in Excel. The data connection properties inside of Excel will look like this:

If things don't work, you might want to check that this option in the Power BI Admin portal hasn't been turned off for Analyze In Excel (though this Admin portal setting is applicable only to datasets where the underlying data is SSAS in Live Connection mode):

More info about Analyze In Excel is here: https://powerbi.microsoft.com/en-us/documentation/powerbi-service-analyze-in-excel/ - the post has more details about requirements for Excel version, the need for a measure in the dataset, etc.

Note that you can't publish an Analyze In Excel workbook back to the Power BI Service (because workbooks in Power BI are only supported if it has imported data in the workbook). Maybe we'll get this feature in the future, because having one place to publish would be very nice.

One more option which is similar but not quite...

Power BI Service Live Connection

For this one, we start inside of Power BI Desktop. As of the time I'm writing this, it's still a preview feature which needs to be enabled first in the Options menu:

To get started, visit the Get Data menu. You can locate the "Power BI Service" option under Online Services:

Here's where things differ a LOT from Analyze In Excel.

If you are a read-only user, you'll see your list of workspaces. However, you won't see any datasets to choose from. That is because currently you are required to have edit privileges on the dataset in order to use this feature.

However, if you do have edit permissions, you can select the dataset. Then it will open a blank report with a connection back to the dataset:

Note that you cannot edit queries or datasets or relationships - just the report. Perfect. That means it looks like it's behaving exactly like the earlier two options discussed.

However, since edit permissions are needed currently in order to use this feature effectively, it's not able to be widely used by self-service BI users. I hope it ends up being the equivalent of Analyze In Excel, for Power BI Desktop instead.

More info about Power BI Service Live Connection is here: https://powerbi.microsoft.com/en-us/blog/connecting-to-datasets-in-the-power-bi-service-from-desktop/.

That's it. You keep thinking about ways to reduce the number of duplicate / similar datasets and I'll be a happy girl.

You Might Also Like...

Why a Semantic Layer Like Azure Analysis Services Is Relevant