Rajendra Gupta

Direct Query Mode in Power BI for Azure Analysis Services

April 19, 2021 by

In this article, we will explore Direct Query Mode in Power BI for Azure Analysis Services.

Introduction

Azure Analysis Services offer you to migrate your on-premise SSAS models in the Azure cloud using platform-as-a-service architecture. You can deploy tabular models in compatibility level 1200 or higher. In the article, Azure Analysis Services and Power BI Live connections, we implement the following.

  • Deployed an Azure Analysis Service instance
  • Deployed a sample Adventureworks model from the Azure portal
  • Developed a data model in the Visual Studio 2019
  • I worked with a live data connection for the Azure Analysis database

In this article, we will discuss the DirectQuery model for Azure Analysis Services and its implementation. Before you move further, deploy your azure analysis service database and model in the Azure portal.

The previous article explored the Azure Analysis Services tabular model and connected model with Power BI Desktop (PBD)using the live connection. It gives the below options for data model connection.

  • Import
  • Connect live

In the article, An overview of Power BI data models, we discussed the DirectQuery model. In this model, Power BI (PBI) does not import data locally and executes queries directly in the relational databases.

Power BU direct query mode

As shown below, PBI does not support the DirectQuery feature for the analysis service database. In December 2020 Power BI release, Microsoft released a preview feature for Power BI that allows you to run a direct query for Analysis Services databases.

Analysis Services databases

Once you connect using the live connection for the analysis services database, you get information: Live connection: Connected as shown below.

View data tables

While you are having a live connection to Azure SSAS, you cannot add another data source in the report. In this live connection, you work with a remote model. Therefore, you cannot make changes such as additional measures, columns. For example, if we require data from an additional data source, such as Microsoft Excel, click on Excel data source, and Power BI Desktop gives the following error message.

Add a local model

To use the direct query feature, first, download the latest version of PBD. In this article, I use December 2020 release.

Desktop version

Launch PBD and navigate to File -> Options and Settings -> Preview features. In the preview features, put a check on DirectQuery for Power BI datasets and Analysis Services.

  • Note: DirectQuery for Power BI datasets and Analysis Services is a preview feature and might get significant changes before it moves to General Availability

Enable preview feature

It is a preview feature, therefore, restart PBD after enabling it.

Restart message

Now, relaunch PBI, make a live connection to the analysis service database. Now, the heading changes to Live Connection : Connected: Make Changes to this model.

Live connection

Click on the hyperlink Make Changes to this model, and you get a warning message. As per the warning, currently, you are having a live connection to a remote model. If we want to change the model or add data from additional sources, it uses a DirectQuery connection. It is not possible in a remote data model; therefore, click on the Add a local model.

Add a local model for directquery

It refreshes your data model and status changes to Storage mode: DirectQuery.

View Storage Mode

In the DirectQuery mode, click on the model tab from the vertical left-hand menu. Here, you can view existing relationships, create new or modify relationships.

View models

Click on the upgrade now. It modifies visuals for displaying the model tables. You can move the slider for Show the database in the header when applicable.

View the change in the above and below image. The table header contains the data source and the data model type.

Show the database in the header

Hover the mouse pointer to the table header, and we get the following details.

  • Object name
  • Storage: DirectQuery ( modified storage model)
  • Data Source: It verifies that you are connected to a SQL Server Analysis Services database
  • Server: It is the azure server connection string. We can note here that the data model has the read-write connection string to the azure analysis database
  • Database: Analysis services tabular model database

View details in model

Now, if we try to add another analysis services database connection, you get the below modes:

  • Import
  • DirectQuery

Input analysis service details

Designing a DirectQuery model in Visual Studio

We can design a data model or modify the existing model for DirectQuery. For example, in the previous article, we deployed [azuressas] data model. Open this Visual Studio model, go-to solution explorer and right-click on Model.bim and go to its properties.

Designing a DirectQuery model

Once You switch to DirectQuery mode, your data model gets refresh, and you do not get any data preview in the Visual Studio.

Refresh data model

Now, let’s process your data model. In the data processing, it shows status as Success, but it transferred 0 rows. Strange, right!

Data processing

Now, we can deploy this project from Visual Studio to Azure Analysis service. In the solution explorer, right-click on [azuressas] model and Deploy it.

Build and Deploy resources

Provide credentials for data source validations in the deploy process.

Data Source credentials

It successfully deploys metadata in the DirectQuery model.

Deploy metadata status

If you compare it with the previous article’s live connection, it deployed metadata and objects. In the DirectQuery model, all queries are sent to the native data source for processing because of its nature.

Deployment status

If we switch off the DirectQuery model in the Visual Studio and process it, you can view the number of rows transferred.

Switch off Direct Query model

Switch to DirectQuery model using SSMS

Connect to azure analysis service using SQL Server Management Studio and open the model properties. It shows default mode – Import, as shown below.

View analysis service DB properties

Click on the Import and switch mode to DirectQuery, as shown below.

View DB properties and change default mode

  • Note: It is recommended to deploy the DirectQuery model in the Visual Studio instead of modifying it directly into the SSMS. If we change it directly in SSMS, and your model has incompatible features, it might not report any error

Limitations of DirectQuery model

Azure Analysis Services DirectQuery model has certain limitations. Therefore, you should examine the requirements and limitations before switching to the DirectQuery model.

  • In the DirectQuery models, we can use data from a single relational database such as Azure SQL Database, SQL Server, Azure Synapse Analytics, Oracle, and Teradata
  • We cannot use stored procedures
  • It does not support calculated tables
  • In this mode, the Analysis services convert the DAX formulas, measures into SQL statements. If the DAX formula contains few elements that are not valid SQL syntax, you get validation errors. Therefore, you need to write a new DAX formula or use a compatible function

Composite Model in Power BI

Previously, with the live connection for the azure analysis service in PBD, we cannot add data from another data source in the same report. Once we switch to a direct query model, you can bring data from separate data sources and define models’ relationships.

In the composite model, PBI does the following.

  • You can combine metadata from multiple DirectQuery sources
  • It allows you to combines data from DirectQuery sources and import data

Composite Model

The relationship can be between cross data sources with the following cardinality:

  • Many-to-many
  • Many-to-one
  • One-to-one

The Power BI desktop status bar shows storage mode Mixed if it has a combination of DirectQuery and import tables.

Conclusion

In this article, we explored DirectQuery mode for the Azure Analysis Service database in the PBD. We can switch the model in SSMS or Visual Studio from Live connection to Direct query. This feature is in preview mode in December 2020 Power BI Desktop release and might go under changes in upcoming versions before general availability.

Table of contents

An overview of Power BI data models
Azure Analysis Services and Power BI Live connections
Direct Query Mode in Power BI for Azure Analysis Services

Rajendra Gupta
Azure, PowerBI

About Rajendra Gupta

As an MCSA certified and Microsoft Certified Trainer in Gurgaon, India, with 13 years of experience, Rajendra works for a variety of large companies focusing on performance optimization, monitoring, high availability, and disaster recovery strategies and implementation. He is the author of hundreds of authoritative articles on SQL Server, Azure, MySQL, Linux, Power BI, Performance tuning, AWS/Amazon RDS, Git, and related technologies that have been viewed by over 10m readers to date. He is the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups. Based on his contribution to the SQL Server community, he has been recognized with various awards including the prestigious “Best author of the year" continuously in 2020 and 2021 at SQLShack. Raj is always interested in new challenges so if you need consulting help on any subject covered in his writings, he can be reached at rajendra.gupta16@gmail.com View all posts by Rajendra Gupta

2,640 Views