Blog

In December 2020, Microsoft launched a preview feature in Power BI they called “DirectQuery for Power BI datasets and Analysis Services“. Most of the Power BI community had heard of this as “composite models” and were looking forward to getting it. Marco Russo, co-author of “The Definitive Guide to DAX” called it “The holy grail of business intelligence for 20 years. The ultimate feature of semantic models.” Over at Power BI tips they were equally effusive saying “Microsoft has done it again. They have added a great feature in the Power BI desktop release for December 2020“. Many experienced Power BI users were calling it a “game changer” and “the best new feature in Power BI ever” and generally getting very excited.

But a new Power BI user might be forgiven for asking what all the fuss is about, what is this “DirectQuery” “Composite model” thing, and how do you take advantage of it. Well, if that’s you, I’ll try and explain.

It’s all about the Data Model.

The core data repository for Power BI is the “Data Model” this is a construct built of tables of data and related together. The neat thing about Power BI is that these data tables can come from many differing sources. You can take data from Excel Spreadsheets, SQL Databases, CSV files, SharePoint lists and many other locations, and then relate them one to another. The Customers table will relate to the Transactions table and the Transactions table will relate to the Products table, and so on, building a model of the underlying data that Reports and Dashboards can analyse.

Image

If you’re created a Report with Power BI you’ve worked with a Data Model and may be aware how important it is. I find myself constantly saying to Training course attendees that “Time spent designing and working on your Data Model is time well spent“. To re-enforce how important it is to build the Data Model correctly, I often say “Get the Data Model right and the Reports just fall out of it” and “If you can’t get what you want from a Power BI Report, the chances are that the underlying Data Model is a fault“.

Reuse that Data Model.

So if you’ve got a good Data Model it’s a useful thing and you should re-use it as much as possible. Publish it to your Power BI tenant as a Dataset and you can share it with others, and build more Reports and Dashboards based on it. In Power BI desktop you can connect to a Dataset held in the Power BI cloud, and then build your Report on it. This is great, it encourages Data Model reuse (after all you’ve spent all that time extracting, cleaning and relating that data, you should use it again and again).

Image

The whole thing or nothing.

That was about the only problem with connecting to a Power BI Dataset, it was all or nothing. Up to now connecting to a Dataset connected to the whole thing (in this case 6 tables and the relationships between them) but then it also stopped any further data selection. You’ll see in the screenshot above the rest of “get data” is greyed out, I can’t add any other data sources to my Power BI Dataset. This is great if I want to work with just the data model, but I can’t add my own tables to the data model. Say I had a spreadsheet of sales targets for my Salespeople, I can’t bring that in and measure this data against the sales targets, I’ve got to go back to the original data and build another data model. This shortcoming has now been addressed in the recent update to Power BI desktop.

It’s a Preview.

Once you’ve got the Power BI desktop December update (downloaded from here or subscribe to Power BI desktop in the Microsoft Store) you’ll find that things are just the same. The reason for this is that “Composite Models” are a Preview feature, this means you’ve got to go to the Options in Power BI desktop and set the “Preview Features” there.

Image

Note that the feature is called “DirectQuery for Power BI datasets and Analysis Services”, quite a mouthful, I’ll refer to it as “DirectQuery” from here on.

Whilst you’re in Preview features you may want to turn on other new facilities, it’s your choice. However whatever features you set you’ll have to restart Power BI Desktop for the changes to take effect.

Add your data to the existing Dataset.

Then, once you’ve turned the DirectQuery feature on you can connect to the Power BI Dataset as before, but now the options to add more data are not greyed out. So you can go and connect to more data, I’m going to select an Excel spreadsheet. You’ll see when you do so a prompt that this will require a Direct Query connection to add a local model to the .pbix. Click “Add a local model” and proceed.

Image

Now you can select your other data source to add to the Data Model, you may also see a further prompt. This just calls to your attention that the owners and administrators of the main data model may see data that you’re adding in to the model.

Image

Once that’s done you will have your data loaded, together with the Data Model from the Dataset you connected to. Now you can go and look at the model view.

In model view, you may see a prompt to “Upgrade to the new model view”. Go ahead and click Upgrade if you want, that will change the data model view, importantly adding some nice features for viewing the new composite models

Image

Build your new Composite model.

With that done, you can relate your new tables in the normal way, much as I have here. The new model view shows the original Dataset tables with a blue top bar, and you can see I’ve added a “Targets” table, related one-to-one with the “SalesPeople” table.

Image

Now I can build my report on the new composite data.

What does it all mean again?

OK, lots of steps to do this I know, but really it’s quite straightforward. What this means is you can have an organisational “One Version of the truth” an agreed, validated Dataset which contains the main corporate data and relationships, and users can mix this with their own data. It takes self-service Business Intelligence one step further. Users no longer have to ask someone to add their particular data to the corporate dataset, or have to replicate existing data to mix it with their own. They can use “DirectQuery” to access the corporate, verified data, and add their own data to it, without compromising the original. As the use of Power BI grows within an organisation this will prove very useful. If you are already sharing Datasets this makes it even more powerful. If you’re not currently sharing Datasets maybe these new “composite models” will be the incentive you need to get started.

What do you think?

Let me know what you think of “composite models” in the comments below. Will your organisation use them? Are you already using them? Feel free to contact us if we can help with any aspect of Training or Consulting on Power BI.