What is DirectQuery for Power BI datasets?
With DirectQuery for Power BI datasets and Analysis Services, you can use DirectQuery to connect to Power BI datasets. Report authors who want to combine the data from their enterprise semantic model with other data they own, such as an Excel spreadsheet, or want to personalize or enrich the metadata from their enterprise semantic model, will find this feature especially useful.
Requirements & Limitations: Things to Know Beforehand
Not from My Workspace
The first thing you must consider is that this feature doesn’t work for the datasets in My Workspace. Right now, if you have the dataset hosted in My Workspace, then having a connection to that will be a regular Live connection, and you won’t be able to combine other data sources.
DirectQuery to Power BI dataset is not supported for datasets hosted in My Workspace.
Enable the Preview Feature
Since the functionality is currently in preview, you must first enable it. To do so, in Power BI Desktop, go to File > Options and Settings> Options, and in the Preview features section, select the DirectQuery for Power BI Datasets and Analysis Services checkbox to enable this preview feature. You may need to restart Power BI Desktop for the change.
Tenant administrators can turn Direct Query connections to Power BI datasets on or off in the admin portal. While this is enabled by default, disabling it will prevent users from publishing new composite models on Power BI datasets to the service.
How to create a direct query to PowerBI Dataset?
Having a DirectQuery connection to a Power BI dataset is simple. You can start by getting Data from the Power BI dataset.
Select the Power BI Dataset which was required from the list.
This will give you a regular live connection to the Power BI dataset.
Adding Additional data sources to the model
Get data from another dataset, Or 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.
Connect to your data option will appear; select the table from the dataset which you want to connect to the other source.
Add a local model (DirectQuery to Power BI dataset)
This action will change the storage mode of your Power BI dataset tables to DirectQuery;
Select web from the get data option.
Enter the URL for the shared folder.
Select the required option for Sign in. We have used the organizational account option.
Select the table which you want to load. We load the Branch_Data table.
All selected tables get loaded into our Power BI. We can add other sources in the same way. ( In this case, we used an Excel file, a SharePoint file ) along with the SharePoint and Power BI datasets, which we loaded in earlier steps.
Loaded Sale Excel.
Multiple Direct Query connections are supported.
You can have part of the model from one Power BI dataset and another from another. 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.
Direct Query to PowerBI Dataset Vs. Live Connection to PowerBI Dataset
You cannot do anything with a Live connection to the Power BI dataset. The only thing you can do is create report-level measures. Anything else has to be done in the original model.
With Direct Query to Power BI dataset, you can add other data sources, columns, measures, tables, etc. In other words, you can build a new model on top of the existing one with the changes you want.
Creating a relationship between different data sources is possible.
We can create relationships in the composite model using Direct Query to Power BI dataset, just like creating a relationship between two data source tables.
Publishing to PowerBI Service
When published to service, the new data model requires a little more steps to be followed compared to other 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.” Set up proper gateway connections in case you have any on-premise sources.
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 with access to the datasets.
Enter credentials for DirectQuery to Power BI dataset
The credentials entered here are to create the connection. The access to the data would be based on the logged-in user’s access to the data.
Limitations and Considerations
(i) How Row Level Security Works
RLS configuration of a Power BI dataset remains in that dataset. It cannot be edited or changed in the chained dataset (A chained dataset uses another as a DirectQuery).
Here I have a Power BI dataset with RLS configuration in the model:
Static RLS defined in a Power BI dataset
The above dataset is published to a workspace in the Power BI service, and users are configured to have access to the roles.
Now in a chained dataset (DirectQuery to Power BI dataset), you can only configure new RLS rules on the imported tables.
This means you cannot edit the RLS configuration of the DirectQuery tables.
In our case, we can only apply security SharePoint Excel file table data but not Sales Target Dataset as it is coming directly from Direct Query Power BI Dataset.
Actual View of the Report using Composite Model published to Power BI Service.
User View of Report in Power BI Service
User Aman can only see the data which is related to his branch.
(ii) How Object Level Security Works
OLS is defined for and applied to the source model. It cannot be described as a composite model built on the source model.
When a composite model is built on top of an OLS-protected Power BI dataset or Analysis Services model via a Direct Query connection, the source model’s schema is copied into the composite model. What gets copied depends on what the hybrid model author is permitted to see in the source model according to the OLS rules that apply there. The data itself isn’t copied over to the composite model – instead, it’s constantly retrieved via Direct Query from the source model when needed. In other words, data retrieval always returns to the source model, where OLS rules apply.
Since OLS rules don’t secure the composite model, the objects consumers of the hybrid model see are those that the mixed model author could see in the source model rather than what they might have access to.
In our case, Branch Data Table and Sale Table.
Now we restricted users by applying OLS using a tabular editor by limiting access to the Branch Data table.
Now we created another report using Direct Query Source Security PBI Dataset as Direct query, added Sharepoint Excel File as Additional Source, and Published the report to Power Service, which looks like below.
Now when the User who is restricted with our Role where we denied access to the Branch Data table opens Power BI Report, it looks below
- They see this error because the data comes from the Branch table in this visual.
(iii) Other Limitations
Unsupported data sources: Using any of the following sources as a DirectQuery source isn’t supported:
- SQL Server Analysis Services (SSAS)
- SAP HANA
- SAP Business Warehouse
- Real-time datasets
- Sample Datasets
- Excel Online Refresh
- Import Excel / CSV files
- Usage metrics (My workspace)
Date Fields: Unexpected behavior when using a date hierarchy. To resolve this issue, use a date column instead. After adding a date hierarchy to a visual, you can switch to a date column by clicking on the down arrow in the field name and then clicking on the name of that field instead of using Date Hierarchy.
- Take Over of Datasets: Take over of a dataset that uses the DirectQuery to other datasets feature isn’t currently supported.
- All credentials must be set to build reports in the Power BI service on a composite model based on another dataset. On the refresh credential settings page for Analysis Services sources, the following error will appear even though the credentials have been set:
- Using ALLSELECTED with a DirectQuery source results in incomplete results.
- During preview, the maximum length of a chain of models is three. Extending beyond the chain length of three isn’t supported and results in errors.
- The connection to a Power BI dataset won’t be shown in Power Query.
If you rename a workspace after the DirectQuery connection has been set up, you’ll need to update the data source in Power BI Desktop for the report to continue working.
Chaining
Datasets, and the datasets and models on which they’re based, form a chain. This process, called chaining, lets you publish a report and dataset based on other Power BI datasets, a previously impossible feature.
Discourage DirectQuery connections to a dataset using PowerBI Desktop
- To discourage DirectQuery connections to a dataset, go to File > Options and settings > Options > Current File > Published dataset settings.
- check the Discourage DirectQuery connections to this dataset box on this page, and select OK.
Tenant Considerations
Any model with a DirectQuery connection to a Power BI dataset or Analysis Services must be published in the same tenant. This is especially important when accessing a Power BI dataset or an Analysis Services model using B2B guest identities, as depicted below.
In the diagram, Ash works with Contoso and is accessing data provided by Fabrikam. Using Power BI Desktop, Ash creates a DirectQuery connection to an Analysis Services model hosted in Fabrikam’s tenant.
To authenticate, Ash uses a B2B Guest user identity (step 1 in the diagram).
If the report is published to Contoso’s Power BI service (step 2), the dataset published in the Contoso tenant cannot successfully authenticate against Fabrikam’s Analysis Services model (step 3). As a result, the report won’t work.
Since the Analysis Services model used is hosted in Fabrikam’s tenant, the report must also be published in Fabrikam’s tenant. After successful publication in Fabrik’s tenant (step 4), the dataset can access the Analysis Services model (step 5), and the report will work properly.
Reference: Using DirectQuery for datasets and Analysis Services (preview) – Power BI | Microsoft Learn
