Power BI – How to find out whether a query hit an aggregation or not?

In the 2018-September release of Power BI Desktop, Microsoft has introduced a new game changing feature in data analytical world: “Aggregations”. Personally I feel that Aggregations will provide lot of flexibility when it comes to handling large data sets in Power BI. If you have not still updated your Power BI Desktop, make sure you update to latest Power BI version and try and see how Aggregations works. Following article will help you for that.

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

Please note that this feature is still in preview ( as of I’m writing this post) so you can’t publish a Power BI report with which uses Aggregation feature to cloud.

But how do we know whether my query hit an Aggregations or a not?  that is what I’m going to cover in this post. when you run Power BI desktop in you machine, behind-the-scenes multiple processes start to run. One of them is Microsoft Analysis Service Tabular Model. That’s right. Power BI backend is handle by SSAS Tabular Model engine. If we are to find whether a query being hit or miss by an aggregation, we need to run a profiler for that tabular instance. In order to find out the background SSAS Tabular instance, Open Task Manager and look for “Microsoft SQL Service Analysis Service” in running task list. Check the image bellow. This is how it shows in my machine.

image

Image:01

In my PC, I have installed two SSAS Instances, one for Tabular model (MSSQLSERVER) and one for Multidimensional Model(MULTIDIM). As you can see in Image:01, there is a 3rd instance, which does not have any name. That is the instance used by Power BI Desktop. But I don’t know the name of this instance to run a profiler. How do I find the name or IP/Port in this instance? For that right click on the process name as in  Image:02  and go to Detail and there you can get respective PID(Process ID) for this SSAS service. In my case PID is 9736 (Image:03)

image

Image :02

image

Image: 03

Now open Command Prompt and run following command.

Command : Netstat -anop TCP  | findstr [PID]

Make sure you replace [PID] with SSAS Process ID which we found using above steps.This command will give us the TCP IP and the port of SSAS Service. Check Image: 04

image

Image:04

As you can see in Image: 04 , in my PC , TCP IP and port is 127.0.0.1:24731.

Now run SSMS and go to tools and select SQL Server Profiler. Go to files and select Create New Trace. Then select “Analysis Service” as server type and enter above TCP IP address for the server name. Keep authentication as Windows Authentication. Check Image:05.

image

Image: 05

Click Connect and it will open Trace Properties window. In that, go to the Event Selection and select All Event Check box. Check Image:06.

image

Image :06

Select following list of events under “Query Processing” section for tracing.

  • Aggregate Table Rewrite Query
  • Vertipaq SE Query Begin
  • Direct Query Begin

Please note that if your SSMS version is older than v 17.9, you will not see “Aggregate Table Rewrite Query” event in event selection area.

Lets see run the trace and create a visual in Power BI which use an Aggregation.  This is how my trace looks like now.(Image:07)

image

Image:07

As you see, it shows one “Aggregate Table Rewrite Query” event and one “Vertipaq SE Query Begin” event. Lets check the Text data of “Aggregate Table Rewrite Query” event.The TextData shows as a  JSON document format. Check the Image :08

image

Image:08

In the JSON document, you can see that there was a match (matchResult:”matchFound”) and which aggregation table was hit( mapping: {“table”:”FactOnlineSalesAgg(Date,Store,Product)}”}. Additionally, the JSON document provides more detail on what were the aggregation functions used and which columns were used .  A “Vertipaq SE Query Begin” event is there since my aggregation table (FactOnlineSalesAgg(Date,Store,Product)) is in import mode and  data was extracted from it. If your aggregated table is in Direct Query mode, you will see a Direct Query Begin event here. Lets see what happens if Power BI can’t find an Aggregation for a query.

image

Image:09

Now as you see in Image:09, we have a one “Aggregate Table Rewrite Query” event and one “Direct Query Begin” event.Lets check TextData of “Aggregate Table Rewrite Query”. This time JSON document says not match found when Power BI search for Aggregations.( matchingResult:attempedFailed). Check Image:10

imageImage :10

A “DirectQuery Begin” event is there since my detail table is in direct query mode and because no aggregation found, Power BI send a direct query to the detail table.

This way you can find out how effective your aggregations are and which aggregation tables are used by which queries.

Thanks for reading and comment if you have any questions .

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