This tutorial will discuss about DAX query optimization practices. You’ll learn what VertiPaq can do with caches to improve DAX performance and generate accurate results.
VertiPaq Caches In DAX Calculations
The first topic is the VertiPaq Cache. Before you run your queries, you have to clear the cache first because other users may not use the cache that is embedded in the calculation. This will make your DAX work faster in providing results.
One of the best practices is to always clear the cache before doing any type of optimization. You can also run your query with a warm cache to see if changes can be made to utilize the existing cache. By doing so, you can get accurate representations of what your queries are doing and how you can optimize them.
The storage engine has the ability to cache results that will be used later. When the formula engine requests the same data, the storage engine will automatically use the same cache that was used in previous requests. This shows that the cache system improves user performance.
To clear the cache, go to DAX Studio. In the top left corner, click Run and choose Clear Cache then Run. You can set it to always clear the cache before running so that you don’t need to do it every time.
There is also a Clear Cache button near the Run button if you want to clear the cache before you make your queries.
VertiPaq has the ability to detect identical expressions used in the query in order to avoid redoing or rescanning the work.
This is a sample query. It’s a defined measure of the Total Sales that is iterating the Fact Sales table and taking the product of the Quantity and Current Price.
In the ADDCOLUMNS function below, the Total Sales references the MEASURE function above. The Total Sales2 is written the same way in the SUMX function above.
If you run the query, you can only see the expression once. The storage engine knows that there are two identical expressions. This prevents your query from reworking on the same expressions.
DAX Query Optimization
One of the most important things you can do to optimize DAX is to locate bottlenecks. This directs you to where you should start optimizing and allows you to make solutions for performance issues. It’s important to have a holistic view of what you’re trying to accomplish, which is to develop things in order and provide correct and fast results.
First, make sure your data model is set up correctly. DAX is set up to work in a specific manner so you have to go with its flow to have a smoother experience.
Next, find the measures that are causing the most problems. Run them in the Server Timings in DAX Studio and look for unnecessary rows and Call Back Data IDs which affect the total duration of the measure’s calculation.
You can also find them in the Records column in the Physical Query Plan. Make sure that the number of records is equal or close to the number of rows of the final output. This ensures that your query is materializing enough tables for the results.
***** Related Links *****
Storage Engine In Power BI – VertiPaq Operators & Queries
Measure In Power BI: Optimization Tips And Techniques
VertiPaq Analyzer In DAX Studio | Power BI Tutorial
Conclusion
DAX optimization provides faster performance and accurate answers. If your queries take time to generate results, you need to optimize your DAX. This improves the overall performance of your measures by only using the necessary processes for the calculation.
Nick
