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.
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.
Once you connect using the live connection for the analysis services database, you get information: Live connection: Connected as shown below.
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.
To use the direct query feature, first, download the latest version of PBD. In this article, I use December 2020 release.
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
It is a preview feature, therefore, restart PBD after enabling it.
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.
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.
It refreshes your data model and status changes to Storage mode: DirectQuery.
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.
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.
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
Now, if we try to add another analysis services database connection, you get the below modes:
- Import
- DirectQuery
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.
Once You switch to DirectQuery mode, your data model gets refresh, and you do not get any data preview in the Visual Studio.
Now, let’s process your data model. In the data processing, it shows status as Success, but it transferred 0 rows. Strange, right!
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.
Provide credentials for data source validations in the deploy process.
It successfully deploys metadata in the DirectQuery model.
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.
If we switch off the DirectQuery model in the Visual Studio and process it, you can view the number of rows transferred.
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.
Click on the Import and switch mode to DirectQuery, as shown below.
- 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
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 |
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am 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 my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023
- Learn the PostgreSQL COALESCE command - January 19, 2023