In this tutorial, you’ll learn how a DAX query is executed in Power BI DAX Studio. This tutorial presents two flowcharts. The first flowchart shows the basic process behind an execution of a DAX query. Meanwhile, the second flowchart gives a distinct example to better visualize the process.
Flowchart Of A DAX Query In Power BI
This flowchart shows how DAX Studio executes a DAX query.
The first step is, of course, writing the DAX query in Power BI DAX Studio. When you click the Run option, the DAX Parser kicks in. It checks whether the code you’ve written is valid or not. This includes checking if the query is incomplete in any way, such as having a missing syntax or brackets.
Once the DAX Parser check is complete, the Formula Engine then converts your DAX query into a set of Query Plans. This includes both the Logical and Physical Query Plan.
After the Logical Query Plan is built, the Formula Engine retrieves the data from the Storage Engine. The Storage Engine then builds the Physical Query Plan. Afterwards, the result is returned back to the end user.
There are two ways to return data to the Formula Engine. The Formula Engine can either send the request to the In Memory Columnar database, VertiPaq, or to the relational data source.
Once VertiPaq receives a request from the Formula Engine, two things happen: VertiPaq performs a scan and executes an xmSQL query. The data cache is sent back to the Formula Engine which in turn, sends the result back to the caller.
In case of DirectQuery, the Analysis Services forwards the request from the Formula Engine to the Data Source. Then, the data source executes the native language, prepares a data cache, and sends it back to the Analysis Services.
Then, Analysis Services sends the data cache to the Formula Engine. This engine treats the data cache and sends it to the end user. In case of a composite model, a partial request is sent to VertiPaq which will then be sent to the DirectQuery.
Example Of A DAX Query In Power BI
Let’s look at an example to better understand the process.
For instance, you want to write a DAX code which calculates the Total Sales amount if the product color is “Red.”
When you Run this code, the DAX Parser will first check if you made any mistakes in the code.
If all’s good, the Formula Engine will then prepare the Logical Query Plan and retrieve the data cache from the Storage Engine. Once the Storage Engine receives the request, a pseudo SQL language called xmSQL creates a query.
Then the xmSQL code retrieves the data cache and sends it to the Formula Engine.
The data cache can either be single or multiple columns. Once the Formula Engine receives the data cache, it iterates it row by row.
Depending on the type of query received by the Formula Engine, the result will either be returned in the form of a scalar value or table.
***** Related Links *****
DAX Query In Power BI – Logical & Physical Query Plans
Query Performance And DAX Studio Setup
DAX Query Structure In Power BI – Two Engines & DAX Studio
Conclusion
This was a quick overview of how a DAX query is executed in Power BI DAX Studio. Hopefully, the flowcharts presented in this tutorial helped you understand the logical flow of a DAX query in tabular models.
It may be slightly confusing if you’re new to the concept of queries. But once you get a grasp of how it works, it will be easier to understand and implement.
Enterprise DNA Experts
