To place issues straight: I can’t focus on how one can optimize DAX Code at the moment.
Extra articles will comply with, concentrating on frequent errors and how one can keep away from them.
However, earlier than we will perceive the efficiency metrics, we have to perceive the structure of the Tabular mannequin in Power Bi.
The identical structure applies to Tabular fashions in SQL Server Evaluation Companies.
Any Tabular mannequin has two Engines:
- Storage Engine
- Components Engines
These two have distinct properties and fulfill totally different duties in a Tabular mannequin.
Let’s examine them.
Storage Engine
The Storage Engine is the interface between the DAX Question and the information saved within the Tabular mannequin.
This Engine takes any given DAX question and sends queries to the Vertipaq Storage engine, which shops the information within the information mannequin.
The Storage Engine makes use of a language referred to as xmSQL to question the information mannequin.
This language relies on the usual SQL language however has fewer capabilities and helps solely easy arithmetic operators (+, -, /, *, =, , and IN).
To combination information, xmSQL helps SUM, MIN, MAX, COUNT, and DCOUNT (Distinct Depend).
Then it helps GROUP BY, WHERE, and JOINs.
It’ll assist when you have a primary understanding of SQL Queries while you attempt to perceive xmSQL. In case you don’t know SQL, it is going to be useful to study the fundamentals when digging deeper into analyzing bad-performing DAX code.
Crucial reality is that the Storage Engine is multi-threaded.
Due to this fact, when the Storage Engine executes a question, it’ll use a number of CPU-Cores to hurry up question execution.
Lastly, the Storage Engine can Cache queries and the outcomes.
Consequently, repeated execution of the identical question will pace up the execution as a result of the end result might be retrieved from the cache.
Components Engine
The Components Engine is the DAX engine.
All features, which the Storage Engine can not execute, are executed by the Components Engine.
Normally, the Storage Engine retrieves the information from the information mannequin and passes the end result to the Components Engine.
This operation known as materialization, as the information is saved in reminiscence to be processed by the Components Engine.
As you possibly can think about, it’s essential to keep away from massive materializations.
The Storage Engine can name the Components Engine when an xmSQL-Question comprises features that the Storage Engine can not execute.
That is operation id referred to as CallbackDataID and must be averted, if attainable.
Crucially, the Components engine is single-threaded and has no Cache.
This implies:
- No parallelism through the use of a number of CPU Cores
- No re-use of repeated execution of the identical question
This implies we wish to offload as many operations as attainable to the Storage engine.
Sadly, it’s unattainable to instantly outline which a part of our DAX-Code is executed by which Engine. We should keep away from particular patterns to make sure that the proper engine completes the work within the least period of time.
And that is one other story that may fill complete books.
However how can we see how a lot time is utilized by every Engine?
Getting the Efficiency information
We have to have DAX Studio on our machine to get Efficiency Metrics.
We are able to discover the obtain hyperlink for DAX Studio within the References Part beneath.
In case you can not set up the Software program, you may get a transportable DAX model from the identical web site. Obtain the ZIP file and unpack it in any native folder. Then you can begin DAXStudio.exe, and also you get all options with out limitations.
However first, we have to get the DAX Question from Energy BI.
First, we have to begin Efficiency Analyzer in Energy BI Desktop:
As quickly as we see the Efficiency Analyzer Pane, we will begin recording the efficiency information and the DAX question for all Visuals:
First, we should click on on Begin Recording
Then click on on “Refresh Visuals” to restart the rendering of all Visuals of the particular web page.
We are able to click on on one of many rows within the listing and see that the corresponding Visible can be activated.
After we increase on one of many rows within the report, we see a couple of rows and a hyperlink to repeat the DAX question to the Clipboard.
As we will see, Energy BI wanted 80’606 milliseconds to finish the rendering of the Matrix Visible.
The DAX question alone used 80’194 milliseconds.
This can be a extremely poor-performing measure used on this visible.
Now, we will begin DAX Studio.
In case we’ve DAX Studio put in on our machine, we’ll discover it within the Exterior Software Ribbon:
DAX Studio will robotically be related to the Energy BI Desktop file.
In case that we should begin DAX Studio manually, we will manually connect with the Energy BI file as properly:
After the connection is established, an empty question is opened in DAX Studio.
On the underside a part of the DAX Studio Window, you will note a Log part the place you possibly can see what occurs.
However, earlier than pasting the DAX Question from Energy BI Desktop, we’ve to start out Server Timings in DAX Studio (Proper high nook of the DAX Studio Window):
After pasting the Question to the Empty Editor, we’ve to Allow the “Clear on Run” Button and execute the question.
“Clear on Run” ensures the Storage Engine Cache is cleared earlier than executing the Question.
Clearing the Cache earlier than measuring efficiency metrics is the very best follow to make sure a constant start line for the measurement.
After executing the question, we’ll get a Server Timings web page on the backside of the DAX Studio Window:
Now we see a whole lot of data, which we’ll discover subsequent.
Decoding the information
On the left aspect of Server Timings, we’ll see the execution timings:
Right here we see the next numbers:
- Whole – The overall execution time in milliseconds (ms)
- SE CPU – The sum of the CPU time spent by the Storage Engine (SE) to execute the Question.
Normally, this quantity is bigger than the Whole time due to the parallel execution utilizing a number of CPU Cores - FE – The time spent by the Components Engine (FE) and the proportion of the whole execution time
- SE – The time spent by the Storage Engine (FE) and the proportion of the whole execution time
- SE Queries – The variety of Storage Engine Queries wanted for the DAX Question
- SE Cache – Using Storage Engine Cache, if any
As a rule of thumb: The bigger the proportion of Storage Engine time, in comparison with Components Engine time, the higher.
The center part reveals an inventory of Storage Engine Queries:
This listing reveals what number of SE Queries have been executed for the DAX Question and contains some statistical columns:
- Line – Index line. Normally, we won’t see all of the traces. However we will see all traces by clicking on the Cache and Inner buttons on the highest proper nook of the Server Timings Pane. However we won’t discover them very helpful, as they’re an inner illustration of the seen queries. Generally it may be useful to see the Cache queries and see what a part of the question has been accelerated by the SE Cache.
- Subclass – Usually “Scan”
- Period – Time spent for every SE Question
- CPU – CPU Time spent for every SE Question
- Par. – Parallelism of every SE Question
- Rows and KB – Measurement of the materialization by the SE Question
- Waterfall – Timing sequence by the SE Queries
- Question – The start of every SE Question
On this case, the primary SE Question returned 12’527’422 rows to the Components engine (The variety of rows in all the Reality desk) utilizing 1 GB of Reminiscence. This isn’t good, as massive materializations like these are efficiency killers.
This clearly signifies that we made an enormous mistake together with your DAX Code.
Lastly, we will learn the precise xmSQL Code:
Right here we will see the xmSQL code and attempt to perceive the Drawback of the DAX Question.
On this case, we see that there’s a highlighted CallbackDataID. DAX Studio highlights all CallbackDataID within the Question textual content and makes all queries within the question listing daring, which comprises a CallbackDataID.
We are able to see that, on this case, an IF() perform is pushed to the Components Engine (FE), because the SE can not course of this perform. However SE is aware of that FE can do it. So, it calls the FE for every row within the end result. On this case, over 12 million occasions.
As we will see from the timing, this takes a whole lot of time.
Now we all know that we’ve written unhealthy DAX Code and the SE calls the FE many occasions to execute a DAX perform. And we all know that we use 1 GB of RAM to execute the question.
Furthermore, we all know that the parallelism is only one.9 occasions, which may very well be a lot better.
What it ought to appear like
The DAX question comprises solely the Question created by Energy BI Desktop.
However normally, we want the Code of the Measure.
DAX Studio affords a function referred to as “Outline Measures” to get the DAX Code of the Measure:
- Add one among two clean traces within the Question
- Place the cursor on the primary (empty) line
- Discover the Measure within the Knowledge Mannequin
- Proper-click on the Measure and click on on Outline Measure
5. If our Measure calls one other Measure, we will click on on Outline Dependent Measures. On this case, DAX Studio extracts the code of all Measures utilized by the chosen Measure
The result’s a DEFINE assertion adopted by a number of MEASURE Statements containing the DAX code of our responsible Measure.
After optimizing the code, I executed the brand new Question and took the Server Timings to match them to the unique Knowledge:
Now, all the question took solely 55 ms, and SE created a materialization of solely 19 Rows.
The parallelism is at 2.6 occasions, which is best than 1.9 occasions. It seems just like the SE didn’t want that a lot processing energy to extend parallelism.
This can be a superb signal.
The optimization labored very properly after taking a look at these numbers.
Conclusion
We want some data when we’ve a gradual Visible in your Energy BI Report.
Step one is to make use of Efficiency Analyzer in Energy BI Desktop to see the place time is spent rendering the results of the Visible.
After we see that it takes a lot time to execute the DAX Question, we want DAX Studio to seek out out the issue and attempt to repair it.
I didn’t cowl any strategies to optimize DAX on this article, because it wasn’t my goal to do it.
However now that I’ve laid down the inspiration to get and perceive the efficiency metrics out there in DAX Studio, I can write additional articles to point out how one can optimize DAX code, what it is best to keep away from, and why.
I’m trying ahead to the journey with you.
Obtain DAX Studio without spending a dime right here: https://www.sqlbi.com/tools/dax-studio/
Free SQLBI Instruments Coaching: DAX Tools Video Course – SQLBI
SQLBI affords DAX-Optimization coaching as properly.
I exploit the Contoso pattern dataset, like in my earlier articles. You’ll be able to obtain the ContosoRetailDW Dataset without spending a dime from Microsoft here.
The Contoso Knowledge might be freely used underneath the MIT License, as described here.
