DirectQuery for Power BI dataset: How does it work?

How DirectQuery to Power BI dataset works

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.

Prerequisite

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.

DirectQuery to Power BI dataset is not supported for datasets hosted in My Workspace

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.

Get data from Power BI dataset

This will give you a normal live connection to the Power BI dataset.

Live connection to Power BI dataset

You can now get data from another dataset:

Get data from another dataset

Or you can click on Make changes to this model.

Make changes to the live Power BI dataset connection 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.

Add a local model (DirectQuery to Power BI dataset)

This action will change the storage mode of your Power BI dataset tables to DirectQuery;

DirectQuery to Power BI dataset

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.

Changes in the Model using DirectQuery to Power BI dataset

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.

Composite model using DirectQuery to Power BI dataset

Creating a relationship between different data sources is possible

creating relationship in composite model using DirectQuery to Power BI dataset

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”.

There is no gateway error for DirectQuery to Power BI dataset

To solve this error, go to the setting of the dataset.

Dataset settings

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.

fix the gateway error for DirectQuery to Power BI dataset

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.

set up data source credentials for Power BI datasets

Select OAuth2, and enter the Power BI account credentials that have access to the datasets.

Enter credentials for DirectQuery to Power BI dataset

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).

Lineage View

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.

Lineage view for Power BI dataset

After all steps above, the report will show successfully;

composite model report showed without any error

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:

error when sharing a composite model with DirectQuery to Power BI dataset connection

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.

Manage permissions of the source Power BI datasets

If the user doesn’t have Build access, enable it for the user, and then the access should be fine.

Add build access to the Power BI data set

With the Build access, the user can see the report properly.

Build access to the Power BI dataset

Video

Study more

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.

Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
Reza Rad
Trainer, Consultant, Mentor
Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for nine continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.

10 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.

    • Hi Daniel
      Thanks.
      I think the reason behind having a build access level is more of a licensing challenge. Because the users would require PRO license.
      Cheers
      Reza

      • 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.

  • 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.

    Take care,

    Leland

    • 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.
      Cheers
      Reza

  • 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.

    Thanks

    Chrias

    • 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
      Cheers
      Reza

Leave a Reply

%d bloggers like this: