A quick post today. Sometimes you will be mystified why your aggregations are not getting hit. There is no clear-cut answer to why. The best way to find the underlying cause of this is to debug it. Our friends at DAX Studio have made this quite easy 🙂 so let’s dive in.
I have a model with Sales and a Sales aggregation table:
I have set up aggregations to be used.
Now to test if it is working, I am dragging year and totalcost into a visual. It doesn’t seem slow, so it seems to work. But how can I test for sure?
To debug it I am starting DAX Studio, connect to the model and start collecting all queries send to the model.
Now to rerun the query and catch the query I am using the performance analyzer in Power BI desktop. I start recording and click on refresh the visual.
There are a couple of way to collect queries. The two most common ones are to use DAX studio as I am showing here, another one is to use the performance analyzer and expanding the visual directly on then copy the query from there. I decided to use DAX studio because it allows me to just double click on the query I want, and it is more useful when working with multiple visuals. I double click on the captured query:
This copies the whole query to the query pane. To debug the aggregation, I need to look at the trace files from the engine. DAX studio has a UI for it so that makes it easy. I turn on server timing and execute the query.
In the result pane I now get the details of the query. How long it took, how much time was spent in the Formula engine and in the storage engine but most importantly for this use case if the aggregate was hit. Below you see that an aggregate match was found and how it is mapped.
If we now swap out TotalCost for DiscountAmount we immediately notice it is slower in Desktop. Expanding the performance analyzer show we now also have a “Direct query” in the mix, meaning the aggregation was not used.
When I now run the DAX query in “DAX Studio” we see the agg match is now missed. Most importantly you can also see why. When investigating the aggregation details you can see it is missing an aggregation for the DiscountAmount column. In my case I didn’t set the aggregation so that is easy to fix
When you get more interesting cases it will be telling you too, for example if you do average it needs to Count and Sum. For one calculation you need two aggregations defined. That would look like this:
This easy method will allow you to see if your aggregation is being hit or not and debug it.
very nice
Thanks a lot for this useful tip.