What is the easiest way to find whether your aggregations work in your Power BI report?

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.

https://docs.microsoft.com/en-us/power-bi/desktop-aggregations

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.

image

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.

image

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.

image

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.

image

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.

image

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.

image

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.

image

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.

image

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).

image

Image09: Performance analyzer with Direct Query statistics.

image

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!!

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s