In my previous article and video, I explained why DirectQuery for a Power BI dataset is an important feature. In this blog and video, I explain how this feature works step by step.
Before understanding how this feature works, I recommend reading below to understand WHY this is a game-changing important feature:
Not from My Workspace
The first thing you need to consider is that this feature doesn’t work for the datasets located in My Workspace. Right now, if you have the dataset hosted in My Workspace, then having a connection to that will be a normal Live connection and you won’t be able to have other data sources combined with it.
This limitation appears to be lifted soon, based on what mentioned in Microsoft’s documentation. However, even if this is not a limitation when you read this blog post, I strongly advise you to not do it.
Imagine someone built a dataset and published it to My Workspace. Then others used that dataset to build something on top of it. Then if that person leaves the organization, it is hard to get hold of the original dataset. Having a dataset that is used as a source of other datasets in My Workspace is not recommended.
How to create a DirectQuery to Power BI dataset?
Having a DirectQuery connection to a Power BI dataset is simple. You can start from Get Data from Power BI dataset.
This will give you a normal live connection to the Power BI dataset.
You can now get data from another dataset:
Or you can click on Make changes to this model.
Using any of the two options above, you will get the message that this operation will create a local dataset copy with the ability to change.
This action will change the storage mode of your Power BI dataset tables to DirectQuery;
DirectQuery to Power BI dataset, vs Live Connection to Power BI dataset
With Live connection to Power BI dataset, you cannot do anything, the only thing you can do is to create report level measures. Anything else has to be done in the original model.
With DirectQuery to Power BI dataset, you can add other data sources, add columns, measures, tables etc. In other words, you can build a new model on top of the existing one with the changes you want.
Multiple DirectQuery connections are supported
You can have part of the model coming from one Power BI dataset, and another coming from another dataset. You can also have some other tables imported. The new model view of Power BI shows these very nicely with different color headings for each category.
Creating a relationship between different data sources is possible
Publish to Service
The new data model when published to service, requires a bit of steps compared to other types of Power BI datasets. here you can see the steps to go through:
Error: There is no gateway
For the visuals that have anything from the DirectQuery Power BI dataset, you may see an error saying “There is no gateway”.
To solve this error, go to the setting of the dataset.
Remember that you need gateway for any datasource which is located on-premises and Imported. select that in the gateway. Do not select any gateway options for your Power BI datasets.
If you are new to the gateway, then read my article here to understand how it works.
Data Source Credentials
After setting up the gateway, you need to set the credentials for the Power BI datasets.
Select OAuth2, and enter the Power BI account credentials that have access to the datasets.
The credentials entered here is just to create the connection. The access to the data would be based on the logged-in user access to the data (I’ll explain that in another blog/video about Row-Level Security in this type of connection).
You can also see the lineage view from a link here which shows you all the data sources that you have for your composite Power BI model.
After all steps above, the report will show successfully;
Sharing the report
There is also a tip when you share the report. This type of report can be shared like any other report. However, sometimes, depends on the sharing configurations, the user gets an output like this:
Build access to the source Power BI datasets
This is happening often when the user that the report is shared with doesn’t have access to one of the Power BI datasets. You can go to each dataset one by one, and go to Manage Permission of that dataset.
If the user doesn’t have Build access, enable it for the user, and then the access should be fine.
With the Build access, the user can see the report properly.
This blog/video was about how the experience of the Composite model with DirectQuery to Power BI dataset connection is and what are things to consider. If you are interested in understanding WHY this feature is an important milestone in developing Power BI solutions, read my article here.
In the next articles and videos, I’ll explain some of the limitations for calculations and RLS in this type of composite model. Stay tuned. Let me know your thoughts and questions in the comments below.