Put things straight: I won’t discuss how to optimize DAX code today.
There will be more articles focusing on common mistakes and how to avoid them.
However, before we understand performance metrics, we need to understand the architecture of tabular models in Power BI.
The same architecture applies to the tabular model in SQL Server Analytics Service.
Any tabular model has two engines:
- Storage Engine
- Formula Engine
These two have different properties and perform different tasks in the tabular model.
Let’s investigate them.
Storage Engine
The storage engine is the interface between DAX queries and data stored in the tabular model.
The engine takes any given DAX query and sends the query to the Vertipaq storage engine, which stores data in the data model.
The storage engine uses a language called XMSQL to query the data model.
The language is based on a standard SQL language, but has less functionality and supports only simple arithmetic operators (+, -, /, *, =, and).
To aggregate data, XMSQL supports SUM,,,,, MIN,,,,, MAX,,,,, COUNTand DCOUNT (Unique count).
Then it supports GROUP BY,,,,, WHEREand JOINs.
This will help if you have a basic understanding of SQL queries when you try to understand XMSQL. If you don’t know SQL, it’s helpful to learn the basics when analyzing poorly performing DAX code.
The most important fact is that the storage engine is multi-threaded.
Therefore, when the storage engine executes a query, it will use multiple CPU cores to speed up query execution.
Finally, the storage engine can cache queries and results.
Therefore, repeating the same query will speed up execution because the results can be retrieved from the cache.
Formula Engine
The formula engine is a DAX engine.
All functions that the storage engine cannot perform are performed by the formula engine.
Typically, the storage engine retrieves data from the data model and passes the results to the formula engine.
This operation is called implementation because the data is stored in memory for processing through the formula engine.
You can imagine that avoiding large amounts of substances is crucial.
When XMSQL Query contains functions that the storage engine cannot perform, the storage engine can call the formula engine.
This is called the operation ID CallbackDataID If possible, it should be avoided.
Crucially, the formula engine is single threaded and has no cache.
This means:
- No parallelism is used
- Repeated execution without repeated execution of the same query
This means we want to offload as many operations as possible to the storage engine.
Unfortunately, it is impossible to directly define which part of the DAX code is executed by which engine. We must avoid using specific modes to ensure the correct engine is done with the shortest time.
This is another story that can fill the entire book.
But how do we see how much time each engine is used?
Get performance data
We need to have DAX Studio on the machine to get performance metrics.
We can find the download link for DAX Studio in the reference section below.
If you can’t install the software, you can get a portable DAX version from the same site. Download the zip file and untie it in any local folder. You can then launch daxstudio.exe and you can get all the features unlimited.
But first, we need to get the DAX query from Power BI.
First, we need to start the performance analyzer on the Power BI desktop:
Once we see the Performance Analyzer pane, we can start recording performance data and DAX queries for all visual effects:
First, we have to click Start recording
Then click Refresh Visuals to restart all visuals on the actual page.
We can click on a row in the list and notice that the corresponding visual effects are also activated.
When we expand a row on one line in the report, we see some rows and a link that copies the DAX query to the clipboard.
As we can see, Power BI takes 80’606 milliseconds to complete rendering of matrix vision.
DAX query alone used 80’194 milliseconds.
This is a highly performant measure used in this visual effect.
Now we can start DAX Studio.
If we have DAX Studio installed on the machine, we will find it in the external tools ribbon:
DAX Studio will automatically connect to Power BI desktop files.
If we have to start DAX Studio manually, we can also manually connect to the Power BI file:
After establishing the connection, open an empty query in DAX Studio.
At the bottom of the DAX Studio window, you will see a log section where you can see what happens.
However, before pasting the DAX query from the Power BI desktop, we have to start the server time in DAX Studio (top right corner of the DAX Studio window):
After pasting the query into an empty editor, we have to enable the Clear Run button and execute the query.
Clear Run ensures that the storage engine cache is cleared before the query is executed.
Clearing the cache before measuring performance metrics is a best practice to ensure a consistent starting point for measurements.
After executing the query, we will get the server timing page at the bottom of the DAX Studio window:
Now, we see a lot of information, and we will explore next.
Explain the data
On the left side of server time, we will see the execution time:
Here we see the following numbers:
- Total – Total execution time of milliseconds (MS)
- SE CPU – The sum of CPU time spent by the storage engine (SE) to execute a query.
Typically, this number is greater than the total time due to parallel execution using multiple CPU cores - FE – Percentage of the time spent on the recipe engine (FE) and the total execution time
- SE – Percentage of the time spent by the storage engine (FE) and the total execution time
- SE Query – Number of Storage Engine Queries required for DAX Query
- SE Cache – Use storage engine cache, if any
According to thrift: The greater the percentage of stored engine time, the better.
The middle section shows the storage engine query list:
This list shows how many SE queries are executed by the DAX query and contains some statistical columns:
- Line – Index Line. Normally, we don’t see all the lines. However, we can see all the rows by clicking the cache and internal buttons in the upper right corner of the Server Timer pane. But we won’t find them very useful because they are internal representations of visible queries. Sometimes, look at cache queries and see which part of the query is accelerated by SE Cache.
- Subclass – Usually “scan”
- Duration – Time for each SE query
- CPU – Time spent per SE query
- par. – Parallelism of each SE query
- Row and KB – Materialized Size of SE Query
- Waterfall – timing order of SE query
- Query – The beginning of each SE query
In this case, the first SE query returns 12’527’422 rows back to the formula engine (number of rows in the entire fact table) using 1 GB of memory. This is not very good because such a large amount of substance is a performance killer.
This clearly shows that we made a big mistake with your DAX code.
Finally, we can read the actual XMSQL code:
Here we can see the XMSQL code and try to understand the problem with DAX query.
In this case we see that there is a highlighted CallbackDataid. DAX Studio highlights all CallbackDataids in the query text and boldly makes all queries in the query list with a callbackDataid.
We can see that in this case a if() function is pushed towards the formula engine (Fe) because SE cannot handle this function. But SE knows that FE can do it. Therefore, it calls FE for each row in the result. In this case, more than 12 million times.
As we can see from the timing, this takes a lot of time.
Now, we know that we have written bad DAX code and SE calls FE multiple times to perform DAX functions. And we know we use 1 GB of RAM to perform queries.
Furthermore, we know that parallelism is only 1.9 times, which is probably much better.
What should it be like
DAX queries only contain queries created by Power BI desktop.
But in most cases, we need the code for that metric.
DAX Studio provides a feature called “Define metric” to get the DAX code for that metric:
- Add one of two blank lines to the query
- Place the cursor on the first (empty) line
- Find this metric in the data model
- Right-click the action, and then click Define action
5. If our measure calls another measure, we can click Define the relevant measure. In this case, DAX Studio extracts the code for all measures used by the selected measures
turn out DEFINE Declaration, then one or more MEASURE A statement containing the DAX code for our guilty measures.
After optimizing the code, I executed new queries and spent the server time comparing them to the original data:
Now the entire query takes only 55 milliseconds, while SE creates only 19 rows of implementations.
Parallelism is 2.6 times, greater than 1.9 times. It seems that SE doesn’t require much processing power to increase parallelism.
This is a good sign.
After looking at these numbers, the optimization works very well.
in conclusion
When the visual efficiency in your Power BI report is slow, we need some information.
The first step is to use the Performance Analyzer in the Power BI desktop to see when the visual results are rendered.
When we see that it takes a lot of time to execute a DAX query, we need DAX Studio to find out the problem and try to solve it.
In this article, I’m not covering any way to optimize DAX, as this is not my purpose.
However, now that I have laid the foundation for getting and understanding the performance metrics available in DAX Studio, I can write more articles to show how to optimize DAX code, what you should avoid, and why.
I look forward to the journey with you.
Download Dax Studio for free here:
Free SQLBI Tool Training: DAX Tool Video Course – SQLBI
SQLBI also provides DAX optimization training.
I use the contoso sample dataset like I did in previous posts. You can download Microsoft’s Contosoretaildw dataset for free here.
As described below, CONTOSO data can be used for free under the MIT license.
