To place issues straight: I can’t talk about how you can optimize DAX Code at the moment.
Extra articles will observe, concentrating on frequent errors and how you can 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 Energy 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 completely 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 known 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’ll assist in case you have a fundamental understanding of SQL Queries if you attempt to perceive xmSQL. For those who don’t know SQL, will probably be useful to be taught the fundamentals when digging deeper into analyzing bad-performing DAX code.
A very powerful truth is that the Storage Engine is multi-threaded.
Subsequently, when the Storage Engine executes a question, it should 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 velocity 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 capabilities, which the Storage Engine can not execute, are executed by the System Engine.
Normally, 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 possibly can think about, it’s essential to keep away from massive materializations.
The Storage Engine can name the System Engine when an xmSQL-Question comprises capabilities that the Storage Engine can not execute.
That is operation id known as CallbackDataID and ought to be prevented, if potential.
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 need to offload as many operations as potential 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 under.
For those who can not set up the Software program, you may get a conveyable 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 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 couple 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.
This can be a extremely poor-performing measure used on this visible.
Now, we are able to 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 linked to the Energy BI Desktop file.
In case that we should begin DAX Studio manually, we are able to manually connect with the Energy BI file as effectively:
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 notice 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 prime 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 one of the best observe to make sure a constant place to begin 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 loads of info, which we’ll discover subsequent.
Decoding the info
On the left facet of Server Timings, we’ll see the execution timings:
Right here we see the next numbers:
- Whole – 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.
Normally, this quantity is larger than the Whole 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 exhibits a listing of Storage Engine Queries:
This record exhibits what number of SE Queries have been executed for the DAX Question and contains some statistical columns:
- Line – Index line. Normally, 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 – Dimension 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 Truth 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 along 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 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 record daring, which comprises 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 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 outcome. On this case, over 12 million occasions.
As we are able to see from the timing, this takes loads of time.
Now we all know that we’ve written dangerous 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 just one.9 occasions, which might be significantly better.
What it ought to appear like
The DAX question comprises solely the Question created by Energy BI Desktop.
However typically, we want the Code of the Measure.
DAX Studio provides a function known as “Outline Measures” to get the DAX Code of the Measure:
- Add considered one of 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 occasions, which is best than 1.9 occasions. It appears to be like 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 effectively after taking a look at these numbers.
Conclusion
We’d like some info 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.
Once we see that it takes a lot time to execute the DAX Question, we want DAX Studio to search 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 inspiration to get and perceive the efficiency metrics accessible in DAX Studio, I can write additional articles to indicate how you can optimize DAX code, what you must 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/instruments/dax-studio/
Free SQLBI Instruments Coaching: DAX Instruments Video Course – SQLBI
SQLBI provides DAX-Optimization coaching as effectively.
I exploit the Contoso pattern dataset, like in my earlier articles. You may obtain the ContosoRetailDW Dataset without spending a dime from Microsoft right here.
The Contoso Knowledge will be freely used underneath the MIT License, as described right here.
