• Home
  • About Us
  • Contact Us
  • Privacy Policy
  • Special Offers
Business Intelligence Info
  • Business Intelligence
    • BI News and Info
    • Big Data
    • Mobile and Cloud
    • Self-Service BI
  • CRM
    • CRM News and Info
    • InfusionSoft
    • Microsoft Dynamics CRM
    • NetSuite
    • OnContact
    • Salesforce
    • Workbooks
  • Data Mining
    • Pentaho
    • Sisense
    • Tableau
    • TIBCO Spotfire
  • Data Warehousing
    • DWH News and Info
    • IBM DB2
    • Microsoft SQL Server
    • Oracle
    • Teradata
  • Predictive Analytics
    • FICO
    • KNIME
    • Mathematica
    • Matlab
    • Minitab
    • RapidMiner
    • Revolution
    • SAP
    • SAS/SPSS
  • Humor

Reusing Datasets Imported to the Power BI Service

May 21, 2017   Self-Service BI

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.

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:

  1. Report in the Power BI Service. This refers to using the web interface for creating a new report separate from the original PBIX file.
  2. 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.
  3. 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:

 Reusing Datasets Imported to the Power BI Service

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:

PowerBI AnalyzeInExcel Reusing Datasets Imported to the Power BI Service

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:

 Reusing Datasets Imported to the Power BI Service

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:

 Reusing Datasets Imported to the Power BI 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:

 Reusing Datasets Imported to the Power BI Service

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

 Reusing Datasets Imported to the Power BI Service

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:

PowerBIServiceLiveConnection Option Reusing Datasets Imported to the Power BI Service

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

 Reusing Datasets Imported to the Power BI Service

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:

 Reusing Datasets Imported to the Power BI Service

Let’s block ads! (Why?)

Blog – SQL Chick

Datasets, imported, Power, Reusing, service
  • Recent Posts

    • Now get Mind Map View of your Dynamics 365 CRM Connections in a single view with latest Map My Relationships features!
    • Potatoes for Brains
    • How to Prepare for Microsoft Certification Exams
    • The Missing Link: Blockchain for Digital Supply Chains
    • Incoming White House science and technology leader on AI, diversity, and society
  • Categories

  • Archives

    • January 2021
    • December 2020
    • November 2020
    • October 2020
    • September 2020
    • August 2020
    • July 2020
    • June 2020
    • May 2020
    • April 2020
    • March 2020
    • February 2020
    • January 2020
    • December 2019
    • November 2019
    • October 2019
    • September 2019
    • August 2019
    • July 2019
    • June 2019
    • May 2019
    • April 2019
    • March 2019
    • February 2019
    • January 2019
    • December 2018
    • November 2018
    • October 2018
    • September 2018
    • August 2018
    • July 2018
    • June 2018
    • May 2018
    • April 2018
    • March 2018
    • February 2018
    • January 2018
    • December 2017
    • November 2017
    • October 2017
    • September 2017
    • August 2017
    • July 2017
    • June 2017
    • May 2017
    • April 2017
    • March 2017
    • February 2017
    • January 2017
    • December 2016
    • November 2016
    • October 2016
    • September 2016
    • August 2016
    • July 2016
    • June 2016
    • May 2016
    • April 2016
    • March 2016
    • February 2016
    • January 2016
    • December 2015
    • November 2015
    • October 2015
    • September 2015
    • August 2015
    • July 2015
    • June 2015
    • May 2015
    • April 2015
    • March 2015
    • February 2015
    • January 2015
    • December 2014
    • November 2014
© 2021 Business Intelligence Info
Power BI Training | G Com Solutions Limited