DirectQuery for Power BI dataset: How does it work?

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.

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

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.

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.

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

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 12 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, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
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.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.