I’m here to talk about Power BI datasets. Currently, when you connect to a Power BI dataset in the Power BI Service, it will be in live connect mode, which limits what you can do with that data model. I’d like to tell you about leveraging existing Power BI datasets in a way that is not supported out of the box at this time.
In that live connect mode, you’ll be able to add additional measures, but you can’t add calculated columns or external data sources like when using a traditional Analysis Services model. Below is a workaround that you can use to meet ad hoc reporting needs, where you can leverage existing data models for users that do not have edit permissions to the underlying dataset itself or to the data source.
- The first step in leveraging an existing Power BI dataset is to pull it into Excel. This allows us to flatten out the data, de-normalize it within an Excel Pivot Table and use that table as a data source for Power BI.
- If we want to pull that data back into Power BI and add additional data, we start by analyzing in Excel and that will download a file for us.
- Next, we can open our data connected Excel file. As I open this, it will be live connected to our data model. I can then generate a Pivot Table to flatten out the data that I’m interested in. In my demo, I bring in the total sales and a couple dimensions from the sales territory like sales territory country and sales territory group.
- I also want to restrict this by a period of time, so I’ll bring in the calendar year. For this dataset, the last year of data is 2008 but that is only a partial year. I’ll bring in all of 2007 and I need to be sure I bring in enough granularity so I can see that 2008 is only a partial year. I also bring in the month.
- At this point I have a dataset that is de-normalized but not Power BI friendly yet. I will need to flatten it out into a simple table.
- To flatten it, I’ll go into the Pivot Table design and turn off my subtotals first and then my grand totals. Under Report Layout, I click on Show in Tabular Form and then click Repeat all Items and Labels.
- Now, I have a table that is Power BI friendly. This way I don’t have to do a lot of manipulation to work with it.
- The final step back in Power BI Desktop is to connect and import the Pivot Table that I created by using the Excel Connector. Then by pointing to the file that I just saved, I’m able to access the Pivot Table that is connected to our Power BI dataset.
- The way that I formatted the Pivot Table allows us to see the data in a nice, clean table. But keep in mind that as I’m loading this data, it is not pulling in a fresh copy of information from the Power BI Service directly. Instead, it’s pulling in the static copy that exists in that Power BI Pivot Table.
- So, I can’t refresh the data from here. If I want to get an updated set of data from the Power BI Service, I will have to go back to the Excel Pivot Table that is connected to the Power BI dataset and refresh it from there. Once I hit refresh, it will bring in an updated copy of that information via the Excel Pivot Table.
- However, the main reason I’ve done this is to do some ad hoc data modeling and reporting off that existing Power BI dataset. I can also import additional data elements from SQL Server or bring in additional Excel Pivot Tables and join that data to this ad hoc model to meet my immediate reporting needs.
This simple work around gives you some flexibility in being able to leverage your existing Power BI datasets. In the future, I believe this type of work around will be unnecessary as we’ll be able to import existing Power BI datasets and add other elements to it natively out of the box. But until then, this workaround will allow you to get more out of what you have already built.
Need further help? Our expert team and solution offerings can help your business with any Azure product or service, including Managed Services offerings. Contact us at 888-8AZURE or [email protected].