DirectQuery to Power BI Datasets

A new feature has been added to the Power BI Desktop toolset and it’s a very nice one. The official name is “DirectQuery for PBI datasets and AS”. It allows you to create a connection to an existing model (either Analysis Services or a Power BI dataset) and link tables from that model into your existing model. In other words, you can mix datasets. This is great if for example you need data from an existing dataset in your model, but you don’t have access to the source data. You could already connect to an existing model before, but than you were re-using the entire model as a live connection. Now you can also link to a single table for example. Great stuff.

As usual, to access a preview feature you need to enable it in the settings:

Suppose we have created this model with one fact table and one dimension:

We would like to do some analysis on those tables, but oh no, we forgot to include a date dimension! (and like the good developers we are, we already disabled the auto date table feature in Power BI Desktop) But suppose someone shared a model in the Power BI service that contains a date table. We can now just re-use that specific table in our model, using a DirectQuery connection. This means the data from the date table is not imported in our own model, but it rather sits on the server waiting to be queried. We can create such a connection using the Power BI datasets button in the Data part of the ribbon:

You can also find it in the Power Platform section of the Get Data wizard:

You’ll get a list of all datasets you can access (it seems datasets in My Workspace are exclude though):

Select the one you need, and click Create. Instead of creating a live connection and getting all the tables, you can now select individual tables from the dataset:

You might get a warning about a potential security risk because you are using someone else’s data:

The table is now added to the model, and you can create relationships between the new table and your existing tables:

And you can also use it in your visualizations of course:

In the bottom right corner, you can see you’re using data from different models:

It is worth pointing out that if you start with an empty model and you connect through the same “Power BI datasets” button like before, it will create a live connection and import all tables, just like it always did before this feature. In the bottom right corner however, you can now change the storage mode to DirectQuery so you can import other sources. Just click on “Make changes to this model”.

Or, if you try to import some other data, you’ll get this pop-up:

This seems a nice feature to break the boundaries between data silos. However, I can imagine there’s some performance impact when mixing data sources. I’d still prefer to have all of the data imported in its own model for maximum performance, but you know, you can’t always get what you want…

Official docs: Using DirectQuery for Power BI datasets and Azure Analysis Services (preview)