In this tutorial, you’ll learn about xmSQL aggregations. You’ll also understand how a storage engine in DAX Studio can greatly improve your report’s performance. There will be sample aggregations shown and used so that you can see how they perform in VertiPaq.
There are two types of engines: the formula engine and the storage engine. The latter is the faster between the two but it can’t do as much as the formula engine. So, if you create and run your queries, the best practice is to use the storage engine as much as possible.
xmSQL Aggregations
The storage engine can complete simple aggregations using these functions:
Each of these functions follows a simple type of behavior, so always strive to get these functions in your DAX formula.
These are some simple sample queries inside DAX Studio.
First, run the query with the COUNT function. A CALCULATE wrapped around the aggregation will invoke context transition. The query counts the rows of the year 2015 in the fact sales table.
If you go to the Server Timings tab, you’ll see the SELECT function for the DimCalendar Year and the COUNT function from the fact sales table.
Next, run the SUM, MIN, and MAX functions in the query. You’ll then get these results.
The queries provide the min, max, and sum of the Sales quantity for 2015. If you go to the xmSQL in the Server Timings tab, you can see the MIN, MAX, and the SUM functions working to generate results.
The last function in the query is DISTINCTCOUNT. Run the query and you’ll get these results.
If you go to the Server Timings tab, you’ll see the DCount in the xmSQL.
Now, if you try running the DISTINCTCOUNT, MIN, and COUNT together, you’ll see that DISTINCTCOUNT has its own scan. It doesn’t combine with the other aggregations and works on its own.
Since it has its own scan, it can sometimes slow down your DAX. You don’t have to worry about it, but you also have to be aware of what it’s doing in the background of your codes.
***** Related Links *****
VertiPaq Storage Engine On Multi-Threaded Environment
VertiPaq Compression And Storage In Power BI
What Is DAX Studio In Power BI Desktop
Conclusion
The VertiPaq storage engine is the fastest way to work on your DAX queries. It may not be able to do complex queries, but it does simple aggregations quickly. This helps you obtain data and results faster than the formula engine.
If you want to learn more about xmSQL aggregations in the storage engine, go to DAX Studio and practice the simple aggregations. You’ll see how fast it is, and how easy it is to develop.
Nick
