Quick Tips for Aggregations & Composite Models in Power BI
I have been working with a customer on the implementation of Aggregations with Composite Models in Power BI and I have found that there are a few useful tips to assist you with getting it up and running.
Tip 1 – Always ensure that queries start off as DirectQuery
When starting to bring in the composite models the first thing to do is to ensure that all your queries start off as DirectQuery.
The biggest reason is that you can convert a table from DirectQuery to Import, but unfortunately you cannot do it the other way around (from Import to DirectQuery)
Another thing to make a note of, is if you go back into the DirectQuery table and click on Source it will show you that it set to Import, make sure you select DirectQuery again.
Change it back to DirectQuery as shown below
Finally, you can confirm that your table storage mode is set to DirectQuery by right clicking on the table and then going into the Field Properties.
And then viewing the drop down from the storage mode and ensure that it is set to DirectQuery
Tip 2 – Adding a Count of Rows in an Aggregated Table
I watched this awesome video How to use the Power BI Aggregations Feature by Adam Saxton (@GuyInaCube) and Christian Wade (@_christianWade), where they explain how to create Aggregations in Power BI, which I highly recommend you watch, it gives some great insights on how to get it working.
One of the nuggets of pure GOLD, was to add in a count table rows in your aggregated table.
The reason is that it can then be used in other measures that might not be defined as an aggregation type in your aggregation table. The example that they use where you are looking for an average, which in the internal engine, is a SUM divided by the COUNT.
Tip 3 – Distinct Counts on an Aggregated Table
Another great tip from the video above is if you want to be able to do distinct counts, you will need to specify a GroupBy for the column where your DISTINCTCOUNT measure will be applied.
If I recall correctly Christian did mention that a distinct count is only efficient up to 3 – 5 million values. As well as it does depend on the internal engine on where it will source the distinct count from, be that the aggregated table or the DirectQuery table.
Tip 4 – Where possible use SQL Server Views or Similar in your DirectQuery Source
I have found that when connecting using DirectQuery directly to the table there is no option to remove or alter the columns that are returned for the entire table.
The reason being is that there might be some columns that are not needed in the DirectQuery result set. Not only that but the developer of the view can abstract a lot of the details into the underlying view, which makes the DirectQuery return the data faster and only with the required information.
In some instances, depending on what steps are being applied where it could change the query from DirectQuery to Import Mode.
Along with this by using TSQL Queries there is also the possibility to use Indexed Views which could assist the DirectQuery performance.
Tip 5 – How to check if my Aggregated Table is being used
Currently DAX Studio does not support this, but once again it was shown in a preview build (in the video link above) that it will be coming to DAX Studio in the near future. Until then this is how you would check to see if your aggregated table is being used.
Open DAX Studio and connect to your current PBIX file. Once it is open on the bottom left it will have the Port Number as shown below.
Next open up SQL Server Management Studio, if you do not have it installed you can download the latest version here
Then select Tools and SQL Server Profiler
Then select the Server Type of Analysis Services.
Where it says Server Name, put in the details you got above from DAX Studio
Then click Connect
This will then bring up the Trace Properties window. Click on the Events Selection at the top
Then near the bottom right hand side click on Show all events
Then scroll down to the section where it says Query Processing and select the following “Aggregate Table Rewrite Query”
Then click Run.
Now as per the Microsoft documentation you can test and run some queries, and what you are looking for is where the “Matching Result” returns a “matchFound” against your Aggregated table. As in the example below it has found a match on the mapping table called “Sales Agg”
I have tested this out watching all the queries as they run, and I do not know how they do it, but it makes its decision almost instantly which table to use.
I hope that you have found these Aggregation and Composite Model tips helpful.
If anyone has got any other tips, please share them in the comments below, and I will happily update my blog post.