To place issues straight: I cannot talk about learn how to optimize DAX Code at present.
Extra articles will comply with, concentrating on frequent errors and learn how 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 Energy Bi.
The identical structure applies to Tabular fashions in SQL Server Evaluation Providers.
Any Tabular mannequin has two Engines:
- Storage Engine
- Method 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 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 knowledge 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 knowledge, xmSQL helps SUM, MIN, MAX, COUNT, and DCOUNT (Distinct Rely).
Then it helps GROUP BY, WHERE, and JOINs.
It would assist you probably have a fundamental understanding of SQL Queries whenever you attempt to perceive xmSQL. In case you don’t know SQL, it will likely be useful to be taught 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 is going to 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 consequence will be retrieved from the cache.
Method Engine
The Method Engine is the DAX engine.
All capabilities, which the Storage Engine can’t execute, are executed by the Method Engine.
Normally, the Storage Engine retrieves the information from the information mannequin and passes the consequence to the Method Engine.
This operation known as materialization, as the information is saved in reminiscence to be processed by the Method Engine.
As you’ll be able to think about, it’s essential to keep away from massive materializations.
The Storage Engine can name the Method Engine when an xmSQL-Question accommodates capabilities that the Storage Engine can’t execute.
That is operation id referred to as CallbackDataID and needs to be prevented, if doable.
Crucially, the Method engine is single-threaded and has no Cache.
This implies:
- No parallelism by utilizing 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 doable 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 proper engine completes the work within the least period of time.
And that is one other story that may fill whole books.
However how can we see how a lot time is utilized by every Engine?
Getting the Efficiency knowledge
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.
In case you can’t 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 knowledge 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 also be activated.
Once 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 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 we’ve DAX Studio put in on our machine, we are going to discover it within the Exterior Device Ribbon:
DAX Studio will routinely 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 nicely:
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, we’ve to begin 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 most effective observe to make sure a constant start line 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 variety of info, which we are going to discover subsequent.
Decoding the information
On the left facet of Server Timings, we are going to see the execution timings:
Right here we see the next numbers:
- Complete – The whole 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 Complete time due to the parallel execution utilizing a number of CPU Cores - FE – The time spent by the Method Engine (FE) and the share of the overall execution time
- SE – The time spent by the Storage Engine (FE) and the share of the overall execution time
- SE Queries – The variety of Storage Engine Queries wanted for the DAX Question
- SE Cache – The usage of Storage Engine Cache, if any
As a rule of thumb: The bigger the share of Storage Engine time, in comparison with Method 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 consists of some statistical columns:
- Line – Index line. Normally, we is not going to see all of the strains. However we are able to see all strains 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 inside 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 Method engine (The variety of rows in your entire 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 a giant 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 accommodates a CallbackDataID.
We are able to see that, on this case, an IF() operate is pushed to the Method Engine (FE), because the SE can’t course of this operate. However SE is aware of that FE can do it. So, it calls the FE for every row within the consequence. On this case, over 12 million instances.
As we are able to see from the timing, this takes a variety of time.
Now we all know that we’ve written dangerous DAX Code and the SE calls the FE many instances to execute a DAX operate. 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 instances, which could possibly be significantly better.
What it ought to seem like
The DAX question accommodates solely the Question created by Energy BI Desktop.
However typically, we want the Code of the Measure.
DAX Studio provides a characteristic referred to as “Outline Measures” to get the DAX Code of the Measure:
- Add considered one of two clean strains within the Question
- Place the cursor on the primary (empty) line
- Discover the Measure within the Information 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 check them to the unique Information:
Now, your entire question took solely 55 ms, and SE created a materialization of solely 19 Rows.
The parallelism is at 2.6 instances, which is best than 1.9 instances. It seems to be just like the SE didn’t want that a lot processing energy to extend parallelism.
It is a excellent signal.
The optimization labored very nicely after taking a look at these numbers.
Conclusion
We want some info when we’ve 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 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 accessible in DAX Studio, I can write additional articles to point out learn how to optimize DAX code, what you must keep away from, and why.
I’m wanting ahead to the journey with you.
Obtain DAX Studio without cost right here: https://www.sqlbi.com/instruments/dax-studio/
Free SQLBI Instruments Coaching: DAX Instruments Video Course – SQLBI
SQLBI provides DAX-Optimization coaching as nicely.
I take advantage of the Contoso pattern dataset, like in my earlier articles. You may obtain the ContosoRetailDW Dataset without cost from Microsoft right here.
The Contoso Information will be freely used underneath the MIT License, as described right here.