⚠ Note

This preview has ended and the feature was officially released. Please refer to the announcement blog post.

In Nov 2018, we released composite models, which allow you to seamlessly add multiple DirectQuery sources and imported data to your models. We have heard your feedback that you would like to do this with sources you previously live connected to, like Power BI datasets and Azure Analysis Services sources.

We are excited to announce that DirectQuery for Power BI datasets and Azure Analysis Services is now in preview! This long-awaited addition allows report authors to use DirectQuery to connect to Azure Analysis Services or Power BI Datasets and, if they want to, combine it other DirectQuery and imported data.

This feature is ideal for report authors who want to combine the data from their enterprise semantic model with other data they may own like an Excel spreadsheet, or who want to personalize or enrich the metadata from their enterprise semantic model.

To enable this feature, you will need to enable the preview feature switch in the Report options dialog.

Alberto Ferrari had this to say about this feature: “We got used to monthly updates with Power BI, but not all the months are the same. Guys, the December 2020 version of Power BI is an historical milestone in the development of Business Intelligence. Historical. Milestone. I am not saying this lightly; I am old enough to have seen many things happen in the Business Intelligence world. Some were nice, some were cool… this is neither nice nor cool: this is huge: finally, can seal the marriage between self-service and corporate BI”.

Feel free to leave feedback at this Power BI Community forum post.

Using DirectQuery for live connections

Using DirectQuery for Power BI datasets and Azure Analysis Services requires that your report has a local model. You can start from a live connection and add upgrade to a local model or start with a DirectQuery connection or imported data, which will automatically create a local model in your report.

To see what kind of connections are being used in your model, you can look in the status bar in the bottom right corner of PBI Desktop. If you are only connected to an Azure Analysis Services source, you may see a message like the following:

If you are connected to a PBI dataset, you will see a message that lets you know what PBI dataset you are connected to:

If you want to make customizations to the metadata of the fields in your live connected dataset, you can click “Make changes to this model” in the status bar, or you can click the button with the same name in the ribbon.

In Report View, you will see the “Make changes to this model” button in the Modeling tab of the ribbon, and in the Model View, you will see a button with the same name in the Home tab of the ribbon.

Once you click that button, a dialog will pop-up confirming that it will add a local model. Press “Add a local model” to unlock capabilities to create new columns or modify the metadata for fields from Power BI datasets or Azure Analysis Services.

When connected live to an Analysis Services source, there is no local model. To use DirectQuery for live connected sources like Power BI datasets and Azure Analysis Services, you will need to add a local model to your report. Keep in mind that when you publish a report with a local model to the service, a dataset for that local model will be published a well. This is the same behavior as when you publish a report with imported data to the service.

Chaining

With this feature, you can publish a report and a dataset that is based on other Power BI datasets, which was previously not possible. Together, datasets and the datasets and models they are based on form a chain.

For example, imagine your colleague publishes a Power BI dataset called Sales and Budget that is based on an Azure Analysis Services model called Sales, and combines it with an Excel sheet called Budget.

When you publish a new report (and dataset) called Sales and Budget Europe that is based on the Sales and Budget Power BI dataset published by your colleague, making some further modifications or extensions, you are effectively adding a report and dataset to a chain of length three, which started with the Sales Azure Analysis Services model and ends with your “Sales and Budget Europe Power BI dataset. See the image below:

The chain shown above is of length three, which is the maximum length during preview of this feature. Extending further, beyond the chain length of three is currently not supported and will result in errors.

Security Warning

Please note that when you use this feature, you will see the following security warning.

Data may be pushed from one data source to another, which is the same security warning for combining DirectQuery and import sources in a data model (a feature that was introduced in Nov 2018).

To learn more about this behavior, please visit this article on using composite models in Power BI Desktop.

Features & Scenarios to Try

As this feature is currently in preview, we are eager to collect as much feedback as we can. Here are some things you can try to explore this feature for yourself:

• Connecting to data from various sources: Import (such as files), Power BI datasets, Azure Analysis Services
• Creating relationships between different data sources
• Writing measures that use fields from different data sources
• Creating new columns for tables from Power BI datasets of Azure Analysis Services
• Creating visuals that use columns from different data sources

Beginning with the April 2021 version of Power BI Desktop, you can also connect to a perspective when making a DirectQuery connection to an Azure Analysis Services model, if a perspective is available.
Please leave feedback at this Power BI Community forum post.

Behavior to note

Here are a couple of things that you might run into that is good to know:

• If you refresh your data sources and there are errors with conflicting field/table names, Power BI will resolve the errors for you.
• You cannot edit, delete or create new relationships in the same Power BI dataset or Analysis Services source. If you have edit access to these sources, you can make the changes directly in the data source instead.
• To build reports in the Power BI service on a composite model that is based on another dataset all credentials will have to be set. On the refresh credential settings page, for Azure Analysis Services sources, the following error will show even though the credentials have been set:
  
  As this is confusing and incorrect, this is something we will take care of soon.
• To be able to make a DirectQuery connection to a Power BI dataset your tenant needs to have ‘Allow XMLA Endpoints and Analyze in Excel with on-premises datasets‘ enabled. For premium capacities, the “XMLA endpoint” should be set to “Read-only” or “Read/Write”.
• If using a classic workspace in combination with this feature it is not sufficient to set permissions on the dataset itself. For classic workspaces, all users accessing reports that leverage this feature must be members of the workspace, Consider upgrading classic workspaces to new workspaces to avoid this situation.
• RLS rules will be applied on the source on which they are defined but will not be applied to any other datasets in the model. RLS defined in the report will not be applied to remote sources, and RLS set on remote sources will not be applied to other data sources.
• KPIs, row level security, and translations will not be imported from the source in this preview build. We plan to add support for these properties when making this feature generally available.
• You may see some 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 clicking on the name of that field instead of using “Date Hierarchy”:
  

This behavior will be addressed later in the preview.
For more information on using date columns versus date hierarchies, visit this article.

• You may see some less useful error messages when trying to use AI features with a model that has a DirectQuery connection to Azure Analysis Services. We plan to address them soon.
• Using ALLSELECTED with a DirectQuery source results in incomplete results.
• Filters / Relationships:
  • A filter that is applied from a data source to a table from another DirectQuery source can only be set on a single column. This might be supported in the future.
  • Cross-filtering two tables in a DirectQuery source by filtering them with a table outside of the source is a bad design and is not supported.
  • A filter can only touch a table once. Applying the same filter to a table twice, through one of more tables outside of the DirectQuery source, is not supported.
• During the preview, the maximum length of a chain of models is three. Extending further, beyond the chain length of three is currently not supported and will results in errors. We are considering increasing the maximum chain length in the future.
• A “discourage chaining” flag can be set on a model to prevent a chain from being created or extended. Read more in our documentation.
• The connection to a Power BI dataset will not be shown in Power Query.

Limitations

• Parameters for database and server names are disabled. There are plans to support parameters in the future.
• Defining RLS on tables from a remote source is not supported. There are no plans to support this in the future.
• Using the following sources as a DirectQuery source is not supported:
  • SQL Server Analysis Services (SSAS) Tabular models before version 2022. Support for SSAS Multidimensional models is not planned.
  • SAP HANA
  • SAP Business Warehouse
  • Real-time datasets
  • Sample Datasets
  • Excel Online Refresh
  • Data imported from Excel or CSV files on the Service
  • Content Packs (deprecated)
  • Usage Metrics
• Using DirectQuery on datasets that connect to SSAS using a live connection is not supported.
• Using DirectQuery on datasets from “My workspace” is not supported. This will be resolved shortly after public preview.
• Using Power BI Embedded with datasets that include a DirectQuery connection to an Azure Analysis Services model is not supported.
• Calculation groups on remote sources are not supported, with undefined query results.
• For now, calculated tables are not supported in the Service when using this feature.
• If you rename a workspace after the DirectQuery connection has been set up you will need to update the data source in Power BI Desktop for the report to continue working.
• APR (automatic page refresh) is only supported for some scenarios depending on the data source type. You can find more information in the APR documentation.

• Take-over of a dataset which leverages a DirectQuery connection to a dataset isn’t currently supported.

• As with any DirectQuery data source, hierarchies defined in an Azure Analysis Services model or Power BI dataset will not be shown when connecting to the model or dataset in DirectQuery mode using Excel.

• Please see our docs for an important consideration when working with OLS.

Tenant considerations

Any model with a DirectQuery connection to a Power BI dataset or to Azure Analysis Services must be published in the same tenant, which is especially important when accessing a Power BI dataset or an Azure Analysis Services model using B2B guest identities, as depicted in the following diagram. See Guest users who can edit and manage content to find the tenant URL for publishing.

Consider the following diagram. The numbered steps in the diagram are described in paragraphs that follow. Please note that this information applies to Power BI datasets as well.

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 Azure Analysis Services model that is 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 Azure Analysis Services model (step 3). As a result, the report will not work.

In this scenario, since the Azure Analysis Services model used is hosted in Fabrikam’s tenant, the report also must be published in Fabrikam’s tenant.

After successful publication in Fabrikam’s tenant (step 4) the dataset can successfully access the Azure Analysis Services model (step 5) and the report will work properly.

Please take the preview for a spin and feel free to leave feedback at this Power BI Community forum post. We are looking forward to your feedback!