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.
13 thoughts on “DirectQuery for Power BI dataset: How does it work?”
I had been struggling to understand some behavior on topic around sharing and security for this feature. Many thanks for such a great post in right time.
@Reza, do you know whether there is a plan to make the recipient of report to be able to see report without “build” right from source power bi dataset? A “read” right is much make sense to me because a center dataset is normally not open for all in big organization but just few report/data model builder. Report viewer is only required “read” right to see contents.
I think the reason behind having a build access level is more of a licensing challenge. Because the users would require PRO license.
Thanks Reza for reply. This is too bad. Our central dataset is only given build right for some key report visualizers, with their report users also required build right for purpose to see the reports created by visualizers I doubt how much we can apply this feature although all users have pro license.
I also hope this becomes available easier for more users 🙂
This is an amazing article and you got out ahead of so much of the additional steps/errors involved in sharing a composite model – really haven’t seen anyone else posting about these steps so it’s much appreciated.
I’ve been able to successfully follow everything on here – just wanted to thank you for your great work.
Thanks Leland for your kind words
As always great post and thx for insight in SQLSat…
As there is a very big hype about “composite”, I set some opposed aspects, to maybe balance this discussion, as many user will demand this feature immediatly without evaluating: =>
Would be interested on your opinion.
Hi Oliver. Nice blog article and analysis
The composite model to AS is still at the first step of its journey. I believe many possible problems with it will get fixed through the upcoming releases.
Fantastic article Reza,
I would like to use a PowerBI Dataset in Dual Mode so that I can facilitate using Aggregations but this does not seem to be possible.
Is that correct.
Having the dual mode might be possible later.
But the use of aggregations is not limited to having dual storage mode.
the dual storage mode makes it just easier. there is something called aggregation awareness in Power BI that requires the dual storage mode etc.
if you implement aggregated tables yourself and also do the aggregation switch using DAX, you’ll be fine
“and also do the aggregation switch using DAX”
I am very curious to know what you mean by that, Reza?
There are a number of functions you can use to aggregate the data of another table, such as GroupBy and Summarize
Ah, ok, clear. Thnx Reza.