The December 2020 version of Power BI desktop has just been released, and it is undoubtably the most important release since the first version way back in 2015. The super feature that has been released is an update to composite models using direct query of online datasets. The implications of this release are massive. Anyone with Power BI Desktop can now build their own, local version of a data model and enhance it with their own additional data without the need to have edit access to original data model. Oh, and it is a Pro feature, not a Premium feature!
Locally enhanced data models
Let me give you an example using Adventure Works. Assume my standard Adventure Works database is built and maintained by IT (shown below). It is the one version of the truth and everyone relies on that data.
But I’m special; I have a need to do some analysis using some additional data as well as this main, published data model shown above. Let’s say I want to overlay my draft budget data for next year over the historical data and do some analysis before I submit the budget for approval. The budget data is not part of the data model (not yet, anyway). The way this problem has been solved (since 1985, anyway) is to click “export to Excel” and then mash the data together in Excel. With the introduction of composite models with direct query over online datasets, that has now changed, forever.
This is a preview feature (as at Dec 2020), so you need to turn it on (#1 below). While you are there, make sure you turn on “small multiples” too (#2 below).
Once turned on, I can then create a new, thin workbook connected to a data model hosted at PowerBI.com (as shown below). Note, this must be a shared workspace. It does not work with “my workspace” with the implication that you cannot do this with a free account, you must have a Pro account.
And I can see the data model in the model view (shown below). What is new is that the data and query options in the ribbon are enabled.
In this demo, I want to combine my budget spreadsheet into the model, so I clicked on Get Data\From Excel. I was prompted with this message. I clicked “Add a local model”.
I selected my budget data to import, then got the following message.
This makes sense. When ever you join data from different sources together, you create the potential that Power BI will send your data to the other source for the purpose of generating a query at the other source. This potentially exposes your new local data. I don’t think it is a big issue inside an organisation, but you should be aware.
The new table was loaded. Note the colour coding below. Blue tables (ie #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 the data view, you can only see the local tables (#1 below). You cannot see the tables from the dataset (#2 below).
I don’t know if this will change – I hope it will. If you can’t see what is in the columns of the online tables, it can be tricky to work out which columns to use for joins and also which columns to be used in your measures. It also means that you cannot add a calculated column to an online table. And you also can’t see the measure formulas. It is very hard to use measures as inputs to new measures in your local model if you can’t look at the formulas.
From here it is possible to build a bespoke version of the data model using the local data. I was able to add
- a new calculated table Product Categories = ALL(Products[Category]) so I could use it as a bridging table in my model (shown in #1 below, but be sure to read the last section about bugs too),
- a 1 to many relationship from this local bridging table to my local bugdet table (#2 below),
- a many to many relationship from my online calendar table to my local budget table (#3 below).
With the local model variations built, I could then set about building a report using the data from both sources.
A Few Bugs
At the time of writing, I noticed this message in the bottom right hand corner of Power BI Desktop. When I clicked it, nothing happened.
There were 2 other things I noticed. The formatting I had set on the measures on the online dataset did not flow through to the local model. I was able to format them again locally, however. Not ideal, but it worked. The other issue was that my calculated table in my model didn’t work. When I tried to use the Category Column from the bridging table, it simply didn’t work. You can see below that the calendar table is filtering the sales table, but the Category column from the calculated table is not.
I decided to test a local version of the data. I deleted the ALL(Products[Category]) local calculated table and loaded the data manually using Enter Data. This time it worked just fine.
Keep in mind this is still in preview mode, and some things still need some polish.
What do you think?
What do you think about this feature? Do you agree with me that it is game changing? How will you use this feature? Leave your comments below.