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;
- Power BI Fast and Furious with Aggregations
- Power BI Aggregation: Step 1 Create the Aggregated Table
- Dual Storage Mode; The Most Important Configuration for Aggregations! Step 2 Power BI Aggregations
- Power BI Aggregations Step 3: Configure Aggregation Functions and Test Aggregations in Action
- Multiple Layers of Aggregations in Power BI; Model Responds Even Faster
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:
- Power BI Fast and Furious with Aggregations
- Power BI Aggregation: Step 1 Create the Aggregated Table
- Dual Storage Mode; The Most Important Configuration for Aggregations! Step 2 Power BI Aggregations
- Power BI Aggregations Step 3: Configure Aggregation Functions and Test Aggregations in Action
- Multiple Layers of Aggregations in Power BI; Model Responds Even Faster
Video
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
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.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
very good trick. thanks for the tipp!
This is simple and useful, thanks.
Thanks. glad it was helpful.