Aggregation to speed up the performance of a Power BI report even if all IMPORTED

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube

One of the questions I normally get after presenting about aggregations in Power BI is that, does the aggregation works only for Composite mode and in scenarios to speed up the DirectQuery? or does it work in import mode as well? This article is about this question and shows how you can speed up your model with aggregation even if all imported.

Want to learn about aggregation?

If you are new to aggregation, I recommend reading my blog series about it;

If you want to know about it in a nutshell, here is the overall explanation:

Aggregation is a way of speeding up the performance of a Power BI dataset. The approach works on the basis of creating aggregated tables from the main detailed fact table. The calculations and visuals then will be using the aggregated table which is smaller and faster in the response time.

I have explained how aggregation can make a DirectQuery model fast in the above article. now let’s see how it can help in an all-imported mode.

The aggregated table

Creation of the aggregated table can be done anywhere, in Power Query, in the back-end data source, or using DAX calculated tables.

If the fact table is including all the columns below;

Then the aggregated table is the GROUPED-BY version of that table with fewer columns (which are grouped by key columns, and aggregations);

If you want to understand how to create the aggregated table like above in Power Query, read this article: Power BI Aggregation: Step 1 Create the Aggregated Table.

No manage aggregations in Imported mode

One of the differences, when you create aggregation on an imported table vs DirectQuery table, is the manage aggregation. This feature, which is also called as aggregation awareness, only works if the main table is DirectQuery.

This doesn’t mean that you cannot use aggregations in Imported model. It just means that this feature (which is Power BI ability to find the aggregated table, when the main table is queried) won’t work, and you have to do it in another way.

DAX Measures instead of Manage Aggregation

We don’t have the aggregation awareness of Power BI in all imported model. However, we can use DAX measures to do almost anything. We can use a simple IF statement with another function that checks what tables is filtered, to switch between the aggregated and the main table.

To understand how it works, first let’s look at the model;

In the above model, the Sales Agg is an aggregated table, which includes the grouped data by dimDate, dimCustomer, and dimProductSubCategory. This means that If I use anything from these tables, and also DimGeography and DimProductCategory, I can get what I want from the Sales Agg table.

However, if I slice and dice my data by DimPromotion or DimProduct, then I would need FactInternetSales;

This can be done with an IF statement and a filter checking function in DAX;

Sales = IF(
ISCROSSFILTERED(DimPromotion[PromotionKey]) || ISCROSSFILTERED(DimProduct[ProductKey]),
SUM(FactInternetSales[SalesAmount]), // main table
SUM('Sales Agg'[SalesAmount]) // aggregated table
)

The expression above is using the main (non-aggregated) table if any of the fields from DimPromotion or DimProduct is used in a visualization. (ISCROSSFILTERED will check if any combination of fields from that table filters the output), and will use the aggregated table otherwise.

After the steps above, the two main table and aggregated tables can be hidden from the report view;

Testing the aggregation

To check if you are fetching data from an aggregated table or non-aggregated table, you can create another measure like below:

Sales from which table = IF(
ISCROSSFILTERED(DimPromotion[PromotionKey]) || ISCROSSFILTERED(DimProduct[ProductKey]),
"FactInternetSales",
"Sales Agg"
)

This measure now can be used in a tooltip, and you can see how it works in below;

If you slice and dice data by anything other than DimProduct and DimPromotion, the calculation result would come from the Sales Agg;

And if you filter data by any of DimProduct or DimPromotion fields, you will get the data from non-aggregated table.

Multiple layers of aggregations is also possible

Using the DAX measure approach, the multiple layers of aggregation is also possible, all you need is to add more conditions to your IF expression, you can also use SWITCH and get the result from the relevant aggregated table.

Summary: Aggregation to speed up the performance

In addition to good data modeling, proper DAX calculations, and having calculations in their rightful place, aggregation is another way to speed up the performance of your model. If you are dealing with large tables, aggregations would help to reduce the number of rows to process for the calculation. In this article, you learned how you can apply aggregation on an import data Power BI model.

If you are new to aggregations, I would recommend reading other articles in this series:

Video

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube
Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
Reza Rad
Trainer, Consultant, Mentor
Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for nine continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.

1 thought on “Aggregation to speed up the performance of a Power BI report even if all IMPORTED

Leave a Reply

%d bloggers like this: