In the September 2018 release of Power BI Desktop, Microsoft introduced a new game-changing feature in the world of data analytics: “Aggregations”. I feel that Aggregations will provide much flexibility when it comes to handling large data sets in Power BI. With this new feature, users can define detail-level and aggregate-level information separately and Power BI will automatically pick which information (table) to be accessed when the end user perofrms a query. This adds a lot of flexibility to data modeling and enable the end user to analyse big data using Power BI, which was a challenging task previously.
To see how Aggregations work, you need to update Power BI Desktop to the latest version. The following article explains this feature in detail, and is a great source to familiarize yourself with Aggregations before you continue with this post:
https://docs.microsoft.com/en-us/power-bi/desktop-aggregations
Please note that this feature is still in preview (at the time of writing this post) so you cannot publish a Power BI report which uses the Aggregation .
This article will focus on the steps to detect whether a query is hitting or missing an Aggregation? When you run Power BI desktop on your machine, multiple behind-the-scene processes start to run. One of these are Microsoft Analysis Service Tabular Model. That’s right, the Power BI back-end is now handled by the SSAS Tabular Model engine. To determine whether a query is being hit or missed by an aggregation, we need to run a profiler for that particular tabular instance. To find out the right background SSAS Tabular instance, you need to open Task Manager and look for “Microsoft SQL Server Analysis Services” in the running task list. A sample illustration is given below:
On my PC, I have two SSAS instances running, one on the Tabular model (MSSQLSERVER) and one on Multidimensional (MULTIDIM). In Image:01, there is a third instance, which does not have a name. That is the instance used by Power BI Desktop. However, I do not have a name to run a SQL Server Profiler trace against.
So, how do I find the name or IP/Port of this instance?
- Right click on the process name as shown inImage:02 and select ‘Go to details.’ The respective PID (Process ID) for the relevant SSAS service will be displayed. In this example, the PID is 9736 (Image:03)
2.Open Command Prompt on your PC and run the following command:
Netstat -anop TCP | findstr [PID]
Make sure you replace [PID] with the SSAS Process ID. This command will give us the TCP IP and the port of the SSAS Service. Refer to Image: 04
The above image shows the TCP IP, and port of my machine: 127.0.0.1:24731.
- Now that we know the IP address and port, we can connect to the SQL Server Profiler.SSMS, and on the Tools menu, click SQL Server Profiler.
- Go to Files and click Create New Trace.
- In the Connect to Server dialog box, select Analysis Services as the server type and enter the IP address and port number as the server name, and keep authentication as Windows Authentication. A sample image is given below:
4. Click Connect, and it will open the Trace Properties Select the Event Selection tab and click the All Event Check box.
5.Select the following list of events from the Query Processing section for tracing:
- Aggregate Table Rewrite Query
- Vertipaq SE Query Begin
- Direct Query Begin
Note: if your SSMS version is older than v 17.9, you will not see the Aggregate Table Rewrite Query event in the event selection area.
6.Let’s run the trace (Image:07 below) and create a visual in Power BI which uses an Aggregation.
In the above example, you can see the following events:
- Aggregate Table Rewrite Query
- Vertipaq SE Query Begin
Let’s check the Text data of the “Aggregate Table Rewrite Query” event. In the following example, the TextData is shown in JSON document format.
In here, you would see that there is a match (matchResult:”matchFound”) and which aggregation table was hit ( mapping: {“table”:”FactOnlineSalesAgg(Date,Store,Product)}”}. Additionally, the JSON document provides more details on the aggregation functions and the columns that were used.
There is also a “Vertipaq SE Query Begin” event since my aggregation table (FactOnlineSalesAgg(Date, Store, Product)) is in import mode and data is extracted from it. If your aggregated table is in Direct Query mode,however, you will see a “Direct Query Begin” event instead
Let’s see what happens if Power BI cannot find an Aggregation for a query.
As you can see in Image:09, we have one “Aggregate Table Rewrite Query” event and one “Direct Query Begin” event. Let’s check the TextData on “Aggregate Table Rewrite Query.” This time the JSON document says ‘match not found’ when Power BI searches for Aggregations (matchingResult:attempedFailed).
here is a “DirectQuery Begin” event since my detail table is in direct query mode, and since no aggregations were found Power BI sends a direct query to the detail .
Using this method, 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!
Author: Asanka Padmakumara Consultant – Business Intelligence Asanka has more than eight years’ experience in the software industry with much of his latter years focused on business intelligence. His skill repertoire includes across-the-board experience working with Microsoft BI technologies, developing dashboards and delivering solutions in the banking, telco, apparel, FMCG, and healthcare domains.
