Hello All,
I encountered a specific use case with Aggregations that I cannot manage to solve. I am hoping someone from the community could provide an advice or idea. Thank you in advance for your help
PROBLEM
A conflict that does not alow to have an ideal solution for aggregations.
The ideal solution is to have all aggreagations in import mode in order to have the best performance possible.
CONTEXT:
-
We are connected in Direct Query to a big Fact Table of around 150M records & 2 dimensions
A) Product Table with the following hierarchies: Family of Products / Sub Family of Products / Products Code
B) Geography Table with the following hierarchies: Continent/ Region/ Country/ CityIn order to have the best performance possible, and based on the business requirements we decide to make 2 aggregations (created in SQL)
○ Aggregation1: A combination of Family of products , Country & Sales - Size 7 Million rows
○ Aggregation2: A combination of Product Code & Region & Sales - Size 12 Million rows
I set up the aggregations in Power Bi:
For Aggregaton1:
§ Aggregation: GROUP BY Family of Products from Products table ==> Products Table is set up to DIRECT QUERY
§ Aggregation: GROUP BY Country from Geography table ==> Geography Table is set up to: DIRECT QUERY
§ Sum of Sales from Fact Table
For Aggregation2:
§ Aggregation: GROUP BY Product Code from FACT table ==> Products Table is linked to the Aggregation on PRODUCT CODE & the Product Table is set up to DIRECT QUERY for now (But must be set up later to DUAL when I set the aggregation to import)
§ Aggregation: Group by Region from Geography table ==> Geography Table is set up to: DIRECT QUERY
§ Sum of Sales from Fact Table
DUAL mode is important to create the connection between the Aggregated Table set on import and the dimension set on Direct Query
Ex from SQLBI presentation on Aggregations:
QUESTION:
I want to set up my aggregation from Direct Query to Import.
When I do so for aggregation 2, I need to switch the PRODUCT table dimension from Direct Query to DUAL
( in order to create the connection between a table in import and a table in Direct Query)
But when I switch my aggregation1 to import I have a conflict error. This error is because the table Product must be set to Direct Query. But if it is set to direct query It cannot communicate with the Aggregation2.
We cannot set a dimension table to be Direct Query for one aggregation and as DUAL for another. The logic approach would be to let the dimension in DUAL mode, as DUAL is considered both Import & Direct Query. But it is not the case.
Have you encountered this problem before ? Do you have an idea of how to approach this problem? Or a possible workaround?
The use case is very general. Use the same dimensions for two different aggregations, one needing the dimension to be DUAL and another agg table needing the dimension to be DIRECT QUERY.
Basically, one small dimension with high level of aggregation (ex: Aggregation1) and one aggregation with more details ( ex: Aggregation 2)
For now I keep only one aggregation in import, the other aggregation is still Direct Query. It is still better than hitting the Fact Table, but is way slower than the table in import.
Do not hesitate to ask me any question. Unfortunately I do not have a PBIX connected to Direct Query I can share to showcase better the problem encountered.
P.S I looked to many sources talking about aggregations but did not find any blog/video/presentation showcasing a similar problem.
Thank you in advance for your help,
Best regards,
Alex