Forum Discussion
Aggregate Tables Off Other Product Dimensions
I have a weekly data set at the item level by geography (yes, I could likely import this but testing some different options). Will aggregate tables work when my main table is a direct query related at the item level if I create aggregate tables by certain levels of my data selection? For example, one table that is aggregated by department, one by category, and one by subcategory. My goal would be to divert direct querying until a client selected a brand or product as GBQ would have less filtering and aggregating to do at that point.
Am I understanding aggregate tables correctly? When I test this in DaxQuery Editor it seems like at a department or category selection it is still running a SQL code (albiet it is only a ~1700 ms run time at the moment). I thought that Power BI would determine it needed to use the imported aggregation table before moving to the DQ table.
I don't want to break up my dimProduct table as I want to be able to create nested slicers at various product levels. Any help would be great!
2 Replies
Hi rmcconnell340 - Yes, you're on the right track! Aggregation tables in Power BI can help optimize query performance by diverting queries to pre-aggregated data instead of hitting the DirectQuery fact table every time.
Go to Manage Aggregations and ensure the mappings are correctly set between your imported tables and the DirectQuery table.
Eg: as per your model build Sales_Dept_Agg[TotalSales] should be mapped to Sales_Fact[TotalSales] using a SUM() aggregation.
Make Sure Aggregation Tables Are in Import Mode:If your aggregation tables are also DirectQuery, Power BI won’t use them to optimize queries.
DAX Query Debugging in Performance Analyzer:Even if SQL runs at 1700 ms, check in Performance Analyzer whether Power BI is actually hitting the aggregation table.
If Power BI isn't using the aggregated table, it may be because a filter is forcing a fallback to DirectQuery.
Avoid Row-Level Security (RLS) on DirectQuery Fact Table:If RLS is applied, Power BI will always use DirectQuery and ignore aggregations.
I hope these details helps in performing the same.
Thanks rajendraongole1,
The DeptCube table is aggregated and connected to the weeklyFact table via a table aggregation. My aggregation table is imported and connected to a dimension table that is Dual. For some reason when I filter to different departments in my dimProduct table it is still hitting the DirectQuery when all should pass through the imported table at the department level.
