This tutorial will be about the DAX query structure and the two engines inside DAX Studio. You’ll see the process behind how your DAX formulas produce results. You’ll also learn how each engine works in your calculation.
There are two engines that are responsible for producing data: the formula engine and the VertiPaq storage engine.
The DAX Query Structure
This tutorial will show an in-depth DAX query structure on how DAX operates behind your report.
Every measure you create is a query. It works on a set of data and returns an answer. The query then talks to the SQL Server Analysis Services. It’s a customized version for Power BI that runs in the background.
After you write your query, it is sent to the formula engine. It answers all the DAX but not as efficient as the storage engine.
Once the formula engine receives the query, it asks the VertiPaq storage engine for data to work on. Then, the storage engine gets data from the compressed data cache.
VertiPaq is efficient because it works on compressed columns. It can compute a lot of rows within milliseconds.
At the end of the diagram, you can see the data source. This transaction only takes place when you do a data refresh.
Another thing you need to learn is that simple queries can be answered by the storage engine. Otherwise, it sends the data cache back to the formula engine to compute an answer.
When it sends that data back to the formula engine, it’s no longer compressed. It uses the full columns in memory.
So after the formula engine gets the data back, it computes and populates an answer to the DAX query.
The Engines Of The DAX Query Structure
It’s important to know the difference between the formula engine and the storage engine. Each of them is designed for different purposes so you can apply your DAX knowledge and obtain fast and accurate queries. These are the differences between the two engines:
The icon of each engine represents how fast their performance are. The storage engine is faster than the formula engine because it handles simple requests. If your DAX query is simple and requires a SUM, MIN, or MAX, the storage engine can do it all by itself.
On the other hand, the formula engine can handle complex DAX functions. Keep in mind that the more complex your query is, the more formula engines you’ll have in your expressions.
The data used by the two engines are also different. The formula engine uses uncompressed data cache from the storage engine which takes up a lot of memory and time, while the storage engine uses compressed data.
The storage engine is fast because it uses more than one core at a time. It means it’s a multi-threaded engine. If your tables have segments, it scans them depending on how many cores you have. The formula engine, however, can only do one core at a time.
Another great feature of the storage engine is when your measures produce an answer, it stores that answer in memory unlike the formula engine. If the same question is asked, it gives you an answer without running a full calculation.
The storage engine is optimized for speed, while the formula engine is optimized for complexity.
Behind The DAX Calculation
The only method to know the process behind your DAX’s performance is through DAX Studio. This is a sample file in the DAX Studio.
If you press F5 and run it, you’ll get results.
To know what’s happening with the two engines behind the calculation, turn on the Server Timings and the Query Plan. Then run the DAX again.
Once done, go to the Server Timings tab.
You can see the total execution time of the query, formula engine, and storage engine. You’ll also see how many storage engines were run. The formula engine is run by 83.3% of the whole query, while the storage engine only takes 16.7%.
If you go to the Query Plan tab, you’ll see the Physical Query and Logical Query Plans. These are both formula queries about what the formula engine is doing.
***** Related Links *****
DAX Calculations In Power BI: Best Practices
Optimize DAX Functions With This New Course
Measure In Power BI: Optimization Tips And Techniques
Conclusion
Both the formula and storage engines play a great part in generating results from your DAX code. They are the most important parts in the DAX query structure.
I hope you learned how these engines work behind the performance of your DAX, and how they can affect your report.
Nick
