Aggregations is one of the key feature in Power BI and it helps to analyze large datasets without any performance impact.
New to aggregations? read this to get a better idea.
However, once we designed aggregations in Power BI, next question is how do we know whether those aggregations are being used properly. Obviously, if you have a large data volume in the detail table and if the aggregated tables are not being used in queries you will notice high loading time for those visuals. However, that method always will not work. What is the easiest way to find whether your query uses an aggerated table as you wanted it to be? That is what I’m going to cover in this post.
In Power BI aggregations, detail tables must always be queried using Direct Query method and aggregated table can either be in Import mode or Direct query mode.
By looking at the queries of each visual, you can easily identify whether your aggregations are used by Power BI or not. How to track queries executed in your Power BI report? Well, that is where we can use the Performance analyser feature.
Image01: Enable Performance Analyzer
Click on “Performance analyzer” button to open the Performance analyzer panel. In the panel, click on “Start recording” button and then “Refresh visuals” Button. it will refresh all the visuals in your Power BI page and will show you query execution time for each visual. Apart from that, it will allow you to copy the query. That is how we know which query got executed when loading each visual.
Image02: Query execution statistics shown in Performance analyzer
Click on “Copy query” button and paste in a notepad. If your visual uses direct query, it will show you both DAX query as well as SQL query. Lets check below example.
Image03: Senario1- Both tables are in direct query mode
In this case, both the aggregated table as well as the detail table are in Direct query mode. Let’s check how will the query looks like when an aggregation is used by the respective visual.
Image04: The query when an aggregation is found
As you can see in Image:04, query copied from performance analyzer shows two queries. a DAX query as well as a SQL query. As shown in Image:04, it shows that the SQL query uses dbo.SummerizeTable, not FactOnline sales table. That way you know that you have design aggregations properly,
In same scenario , how will it looks if the aggregation table is not used as you expected.
Image05: The Query when an aggregation is not found
As shown in Image:05, this time the SQL query shows that Power BI can not find an aggregated table and hence query the detail table , dbo.FactOnlineSales.
Let’s check other scenario. In this case the aggerated table is using import mode and detail table is direct query mode.
Image06: Senario2- The aggregated table uses import mode
Let’s see how can we identify whether Power BI finds an aggregation when loading data to a visual.
You will notice two difference in that case. First, in Performance analyzer panel ,you will not see any Direct Query statistics. it will only show DAX query execution time.
Image07: No direct query statistics
Second, if you copy and paste the query, you will see that it does not have a SQL query. it only has a DAX query.
Image08: No SQL query
However, if there is no aggregation found, it again shows you direct query statistics in Performance analyzer panel and the query will show SQL part as well (Image:10).
Image09: Performance analyzer with Direct Query statistics.
Image 10: The query with SQL Query.
For me, this is the easiest way to identify whether I have designed my aggregations properly and whether those aggregations are being used by my reports. Thanks for reading and hope this helps you. Cheers!!