To place issues straight: I cannot focus on easy methods to optimize DAX Code at this time.
Extra articles will comply with, concentrating on widespread errors and easy methods to keep away from them.
However, earlier than we are able to 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 Providers.
Any Tabular mannequin has two Engines:
- Storage Engine
- System 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 info saved within the Tabular mannequin.
This Engine takes any given DAX question and sends queries to the Vertipaq Storage engine, which shops the info within the information mannequin.
The Storage Engine makes use of a language referred to as xmSQL to question the info 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 Rely).
Then it helps GROUP BY, WHERE, and JOINs.
It can assist in case you have a fundamental understanding of SQL Queries while you attempt to perceive xmSQL. In case you don’t know SQL, will probably be useful to study the fundamentals when digging deeper into analyzing bad-performing DAX code.
An important truth is that the Storage Engine is multi-threaded.
Subsequently, 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 outcome will be retrieved from the cache.
System Engine
The System Engine is the DAX engine.
All features, which the Storage Engine can’t execute, are executed by the System Engine.
Often, the Storage Engine retrieves the info from the info mannequin and passes the outcome to the System Engine.
This operation is named materialization, as the info is saved in reminiscence to be processed by the System Engine.
As you’ll be able to think about, it’s essential to keep away from giant materializations.
The Storage Engine can name the System Engine when an xmSQL-Question incorporates features that the Storage Engine can’t execute.
That is operation id referred to as CallbackDataID and needs to be averted, if attainable.
Crucially, the System 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 inconceivable 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 right 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’t set up the Software program, you will get a transportable DAX model from the identical website. 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 are able to 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 record and see that the corresponding Visible can be activated.
Once we develop on one of many rows within the report, we see a number of rows and a hyperlink to repeat the DAX question to the Clipboard.
As we are able to see, Energy BI wanted 80’606 milliseconds to finish the rendering of the Matrix Visible.
The DAX question alone used 80’194 milliseconds.
It is a extremely poor-performing measure used on this visible.
Now, we are able to begin DAX Studio.
In case now we have DAX Studio put in on our machine, we are going to discover it within the Exterior Instrument Ribbon:
DAX Studio will robotically be linked to the Energy BI Desktop file.
In case that we should begin DAX Studio manually, we are able to manually hook up 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’ll be able to see what occurs.
However, earlier than pasting the DAX Question from Energy BI Desktop, now we have to begin Server Timings in DAX Studio (Proper high nook of the DAX Studio Window):
After pasting the Question to the Empty Editor, now we have 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 one of the best follow to make sure a constant place to begin for the measurement.
After executing the question, we are going to get a Server Timings web page on the backside of the DAX Studio Window:
Now we see a whole lot of data, which we are going to discover subsequent.
Deciphering the info
On the left facet of Server Timings, we are going to see the execution timings:
Right here we see the next numbers:
- Complete – The entire execution time in milliseconds (ms)
- SE CPU – The sum of the CPU time spent by the Storage Engine (SE) to execute the Question.
Often, this quantity is larger than the Complete time due to the parallel execution utilizing a number of CPU Cores - FE – The time spent by the System Engine (FE) and the share of the full execution time
- SE – The time spent by the Storage Engine (FE) and the share of the full 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 share of Storage Engine time, in comparison with System Engine time, the higher.
The center part reveals a listing of Storage Engine Queries:
This record reveals what number of SE Queries have been executed for the DAX Question and contains some statistical columns:
- Line – Index line. Often, we is not going to see all of the traces. However we are able to see all traces by clicking on the Cache and Inner buttons on the highest proper nook of the Server Timings Pane. However we is not going to 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 System engine (The variety of rows in the complete Reality desk) utilizing 1 GB of Reminiscence. This isn’t good, as giant materializations like these are efficiency killers.
This clearly signifies that we made a giant mistake together with your DAX Code.
Lastly, we are able to learn the precise xmSQL Code:
Right here we are able to see the xmSQL code and attempt to perceive the Downside 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 record daring, which incorporates a CallbackDataID.
We are able to see that, on this case, an IF() perform is pushed to the System Engine (FE), because the SE can’t course of this perform. However SE is aware of that FE can do it. So, it calls the FE for every row within the outcome. On this case, over 12 million instances.
As we are able to see from the timing, this takes a whole lot of time.
Now we all know that now we have written unhealthy DAX Code and the SE calls the FE many instances 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 instances, which may very well be significantly better.
What it ought to appear to be
The DAX question incorporates solely the Question created by Energy BI Desktop.
However usually, we’d like the Code of the Measure.
DAX Studio presents a characteristic referred to as “Outline Measures” to get the DAX Code of the Measure:
- Add one in all 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 are able to 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, the complete question took solely 55 ms, and SE created a materialization of solely 19 Rows.
The parallelism is at 2.6 instances, which is healthier than 1.9 instances. It appears just like the SE didn’t want that a lot processing energy to extend parallelism.
It is a excellent signal.
The optimization labored very properly after taking a look at these numbers.
Conclusion
We want some data when now we have a sluggish 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.
Once we see that it takes a lot time to execute the DAX Question, we’d like 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 purpose to do it.
However now that I’ve laid down the muse to get and perceive the efficiency metrics accessible in DAX Studio, I can write additional articles to indicate easy methods to optimize DAX code, what it is best to keep away from, and why.
I’m wanting 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 presents DAX-Optimization coaching as properly.
I take advantage of 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 will be freely used below the MIT License, as described here.
