Directing items: I will not discuss how I can do the DAX code today.
More articles will follow, focus on normal mistakes and how to avoid them.
However, before we understand the metrics that work well, we need to understand the construction of the table model in Power BI.
The same construction applies to tabrar models in SQL server's analysis services.
Any tablar model has two engines:
- Store engine
- Formula Engines
These two have different objects and to achieve different tabrs in the tabular model.
Let's get away.
Store engine
Engine of storage display between DAX question and data stored in the Tabror model.
The engine takes any given Dax question and sends questions to the vertipaq storage engine, storing information in the data model.
The setting engine uses the tongue called XMSQL to ask the data model.
This language is based on the standard SQL language but is a few skills and only supporting the simple arithmetic operators (+ ,,, = <>,.
Integrating data, XMSQL support SUM, MIN, MAX, COUNTbeside DCOUNT (Different calculation).
Then it supports GROUP BY, WHEREbesides JOINs.
It will help if you have the basic understanding of the SQL questions when trying to understand XMSQL. If you do not know the SQL, it will help read the foundations when logging deep in analyzing of miserable DAX code.
The most important fact is that the rudder engine is the most variable.
Therefore, when the storage engine issues a question, it will use multiple CPUs to speed up the question.
Finally, the engine storage storage can read questions and results.
As a result, repeated performance in the same question will speed up being killed because the result can be found from the cache.
Formula engine
Formula engine is the DAX engine.
All activities, which is a war engine that will not do it, is made by the engine formula.
Usually, the setting engine returns information from the data model and transfers the effect of the engine engine.
This work is called material transactions, as the data is stored in the memory to be processed by formula engine.
As you can imagine, it is important to avoid great construction materials.
The storage engine can call the formula engine when the XMSQL question contains the storage engine duties cannot do.
This is a working id called CallbackDataID and should be avoided, if possible.
Obviously, the formula engine is one pimse and does not have a cache.
This means:
- No similarities using many CPOs
- No reuse is made repeated to the same question
This means we want to load as many jobs as you can in the setting engine.
Unfortunately, it is impossible to explain exactly which part of our Dax-code is made by the engine. We should avoid certain patterns of ensuring that the right engine completes the job at a small amount of time.
And this is another story that can fill all the books.
But how long can we see us to be used by each engine?
To get workout data
We need to have a DAX Studio on our machine to find the metric works.
We can find the DAX Studio Download link in the lines below.
If you can't install the software, you can find a portable Dax version from the same site. Download ZIP file and release a local folder. Then you can start the Daxstudio.exe, and you get all aspects without limits.
But first, we need to receive a DAX question from Power BI.
First, we need to start an analysis of Power Bu Desktop performance:
As soon as we see Perform Performance Analyzer Pane, we can start recording work data and DAX question in all material:
First, we must click on the first recording
Then click “Refresh Import” Restarting all visualization of the actual page.
We can click on one of the lines from the list and we see that the corresponding view is active.
When we grow in one of the lines in the report, we see a few lines and the link to copy the DAX's question to the clipboard.
As we can see, the power of the human needs 80'606 millisecies to complete the visible matrix offer.
Only Dax question used 80'194 milliseconds.
This is a very efficient measure used in this visual.
Now, we can start the DAX Studio.
If we have Dax Studio installed on our machine, we will get it from external tool:
The Dax Studio will automatically be linked to the Power bi desktop file.
Should we start the Dax Studio manually, can connect manually to the Bi file and:
After the connection was established, a question is opened on the DAX Studio.
In the lower part of the DAX Studio window, you will see a log class where you can see what is happening.
But, before paste the Dax question from the Power Bi Desktop, we should start the server time in Dax Studio (right corner of Windows Studio):
After pasting a blank editor question, we must give the “clear run” button and make a question.
“Wipe Run” confirming the CORIDATE Cace is specified before issuing a question.
Cache to cache before measuring the metric performance is the best to verify the original starting point of measurement.
After issuing this question, we will find a server memorial page at the bottom of the DAX Studio window:
We now see many details, which will be evaluated next.
Interpreting the information
The left of Servers Timings, we will see the killings times:
Here we see the following numbers:
- Total – Perfect Time is Done in Miliseconds (MS)
- SE CPU – The sum of the CPU time spent with the final engine (se) to make a question.
Usually, this number is greater than all-time because of the same killings using multiple CPUs - FE – Time spent by formula engine (FE) and the percentage of total fate
- Se – time spent by the final engine (FE) and the percentage of total fate
- SE QUESTIONS – The number of last engine questions required for the DAX question
- Se cache – the use of a storage cache, if any
As a rule of thumb: a larger percentage of the final engine time, compared with the formula for formula formula, better.
The middle class shows a list of the final engine question list:
This list shows how many SE questions are done with the DAX question and includes some mathematical columns:
- Line – Index line. Usually, we won't see all lines. But we can see all lines by clicking on cache and internal buttons in the upper right corner of server time panels. But we will not find them very useful, as they are an inner picture of the visible questions. Sometimes it can be helpful to see the cache questions and see which part of the question is burned by the Sel Cache.
- Subclass – Usually “Scan”
- Time – Time Delivered for each of the SE
- CPU – Time of CPU spent for each of the SE
- Par. – Each second question's match
- Lines and KB – Mature size of SE
- Waterfall – Time Sequence made of SE's
- Question – Starting for each of the SE
In this case, the first See question returned 12'4272 '422 lines in the formula engine (number of lines in all the true table) using 1 GB of memory. This is not good, as large writing materials like these are working killers.
This means clearly we made a big mistake with your DAX code.
Lastly, we can read the actual XMSQL code:
Here we can see the XMSQL code and try to understand the question of the DAX question.
In this case, we see that there is a highlighted callbackid. The Dax Studio highlights all CallbackDaid in the text text and do all questions from the Bold questionnaire, containing CallbackDaaid.
We see that, in this case, work if () is pressed on an engine formula (FE), as the Se cannot process this function. But then you know that Fe is doing. Therefore, it costs the FE to get each line from the ark. In this case, more than 12 million times.
As we can see from the time, this takes a lot of time.
Now we know we write a bad DAX code and SE is worth FE FEFTING DAX work. And we know we use 1 GB of RAM to do this question.
In addition, we know that the matching similarities are only 1.9 times, which can be very better.
What to look
DAX's question consists of only the question caused by Power Bi Desktop.
But in many cases, we need a measure code.
Dax Studio provides a feature called “Definence Steps” to find a Dax Date code:
- Add one of the two empty lines to the question
- Place the cursor in the first row (blank)
- Get an estimate in the data model
- Right click on average and click on the specification
5. If our rating costs another quote, we can click on the interpretation of dependents dependents. In this case, DAX Studio issues the code for all methods used in the selected scale
Result DEFINE a statement followed by one or more MEASURE Statements containing our guilty DAX code.
After doing the code well, I pulled out a new question and took a server time to compare the original data:
Now, the whole question lasted 55 ms, and then you created material things in only 19 lines.
Similar similarities in 2.6 times, better than 1.9 times. It looks like the SE doesn't require a lot of energy to process the parallels.
This is the best sign.
Efficiency work well after checking these numbers.
Store
We need information when we have a little view in your Power Bi report.
The first step is to use the Performance Analyzer in Power bi desktop to see where time is spent when exposing views.
When we see that it takes a lot of time to make a Dax question, we need DAX Studio to find the problem and try to fix it.
I didn't cover any ways to expand the Dax in this article, because it was not my purpose to do it.
But now that I have set down the foundation for finding and understanding the operating Metrics available on the Dax Studio, I can write some articles to show how you can make a Dax Code, to avoid it, and why.
I look forward to the trip with you.
Download DAX Studio FREE here:
FREE SQLBI tool training: Dax Tools Course – SQLBI
SQLBI offers Dax-Actiholitation training again.
I am using the Coco sample data, as in my previous headers. You can download Contosoretailw Dataset free from Microsoft here.
Details of Cocoos can be used freely under the MIT License, as described here.
