The December 2020 version of Power BI Desktop has just been released, and it is by far the most important version since the first version in 2015. The super feature that was released is an update of the composite models by directly querying sets of online data. The implications of this release are enormous. Anyone with Power BI Desktop can now create their own local version of a data model and enhance it with their own additional data without the need to have edit access to the original data model. Oh, and did I mention it? It's a Pro feature, not a Premium feature!
Locally enhanced data models
Let me give you an example using Adventure Works. Suppose TI builds and maintains my standard Adventure Works database (shown below). It is the only version of the truth and everyone trusts that data.
But I am special; I need to do an analysis using some additional data as well as this published main data model shown above. Let's say I want to overlay my draft budget data for next year on top of the historical data and do some analysis before submitting the budget for approval. The budget data is not part of the data model (at least not yet). The way this problem has been solved (since 1985, anyway) is to click "export to Excel" and then combine the data in Excel. With the introduction of compound models with direct query on online data sets, that has changed forever.
This is a preview feature (as of December 2020), so you need to turn it on (# 1 below). While you're there, be sure to also turn on the “small multiples” (# 2 below).
Once powered on, I can create a new slim workbook connected to a data model hosted on PowerBI.com (as shown below). Please note that it must be a shared workspace. It doesn't work with "my workspace."
And I can see the data model in the model view (shown below). What's new is that the query and data options on the ribbon are enabled.
I want to combine my budget spreadsheet into the model, so I clicked Get Data From Excel. I was asked for this message. I clicked "Add a local member."
I selected my budget data to import, then I received the following message.
This makes sense. Every time you join data from different sources, you create the ability for Power BI to send your data to the other source in order to generate a query on the other source. This potentially exposes your new local data. I don't think it's a big problem within an organization, but you have to be aware.
The new table was loaded. Please note the color coding below. The blue tables (i.e # 1 below) are part of the online dataset and the other one (# 2 below) is my locally added file (Budget). Also note that the data view is now visible (# 3 below).
When in data view, you can only see local tables (# 1 below). You can't see the dataset tables (# 2 below).
I don't know if this will change, I hope it will. If you can't see what's in the columns of online tables, it can be difficult to determine which columns to use for joins and also which columns to use in your measures. It also means that you cannot add a calculated column to an online table.
From here, I can create my own custom version of the data model using my local data. I was able to add a new calculated table Product Categories = ALL (Products [Category]) so I can use it as a bridge table in my model (# 1 below), a 1 to many ratio from this local bridge table to my table local bugdet (# 2 below) and a many-to-many relationship from my online calendar table to my local budget table (# 3 below).
With the local model variations created, I was able to start creating a report using the data from both sources.
At the time of writing this article, I noticed this message in the lower right corner of Power BI Desktop. When I clicked on it, nothing happened.
There were 2 other things that I noticed. The format I had set for the measures in the online dataset did not flow to the local model. However, I was able to reformat them locally. Not ideal, but it worked. The other problem was that my calculated table in my model did not work. When I tried to use the category column from the bridge table it just didn't work. You can see below that the calendar table is filtering the sales table, but the Category column of the calculated table is not.
I decided to try a local version of the data. I removed ALL (Products [Category]) local calculated table and loaded the data manually using Enter Data. This time it worked fine.
Note that this is still in preview mode, and some things still need to be polished.
What you think?
What do you think of this feature? Do you agree with me that it is changing the rules of the game? How will you use this feature? Leave your comments below.