To place issues straight: I cannot focus on how one can optimize DAX Code at the moment.
Extra articles will observe, 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
- 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 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 should assist when you have a primary understanding of SQL Queries if you attempt to perceive xmSQL. When you don’t know SQL, will probably be useful to study the fundamentals when digging deeper into analyzing bad-performing DAX code.
A very powerful reality 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 velocity up the execution as a result of the outcome will be retrieved from the cache.
Method Engine
The Method Engine is the DAX engine.
All capabilities, which the Storage Engine can not execute, are executed by the Method Engine.
Normally, the Storage Engine retrieves the info from the info mannequin and passes the outcome to the Method Engine.
This operation known as materialization, as the info is saved in reminiscence to be processed by the Method Engine.
As you possibly can 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 not execute.
That is operation id known as CallbackDataID and needs to be averted, if doable.
Crucially, the Method 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 doable to the Storage engine.
Sadly, it’s inconceivable to straight 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 total 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 will discover the obtain hyperlink for DAX Studio within the References Part under.
When you can not set up the Software program, you may 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 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 will click on on one of many rows within the checklist and spot that the corresponding Visible can also be activated.
Once we broaden on one of many rows within the report, we see just a few 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.
It is a extremely poor-performing measure used on this visible.
Now, we will begin DAX Studio.
In case now we have DAX Studio put in on our machine, we’ll discover it within the Exterior Device Ribbon:
DAX Studio will mechanically be linked to the Energy BI Desktop file.
In case that we should begin DAX Studio manually, we will 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 possibly can see what occurs.
However, earlier than pasting the DAX Question from Energy BI Desktop, now we have to start out 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 the very best apply 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 numerous data, 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 full 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 Method 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 Method Engine time, the higher.
The center part exhibits a listing of Storage Engine Queries:
This checklist exhibits what number of SE Queries have been executed for the DAX Question and consists of some statistical columns:
- Line – Index line. Normally, we won’t see all of the strains. However we will see all strains 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. Typically 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”
- Length – 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 Method engine (The variety of rows in the whole 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 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 checklist daring, which accommodates a CallbackDataID.
We will see that, on this case, an IF() perform is pushed to the Method 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 instances.
As we will see from the timing, this takes numerous 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 just one.9 instances, which may very well be significantly better.
What it ought to seem like
The DAX question accommodates solely the Question created by Energy BI Desktop.
However generally, we’d like the Code of the Measure.
DAX Studio gives a characteristic known as “Outline Measures” to get the DAX Code of the Measure:
- Add one among two clean strains 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, the whole 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 superb signal.
The optimization labored very properly after these numbers.
Conclusion
We’d like some data when now we have 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’d like 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 goal to do it.
However now that I’ve laid down the muse to get and perceive the efficiency metrics obtainable in DAX Studio, I can write additional articles to indicate how one can optimize DAX code, what you need to keep away from, and why.
I’m trying ahead to the journey with you.
Obtain DAX Studio without cost right here: https://www.sqlbi.com/tools/dax-studio/
Free SQLBI Instruments Coaching: DAX Tools Video Course – SQLBI
SQLBI gives DAX-Optimization coaching as properly.
I take advantage of the Contoso pattern dataset, like in my earlier articles. You possibly can obtain the ContosoRetailDW Dataset without cost from Microsoft here.
The Contoso Knowledge will be freely used underneath the MIT License, as described here.
