Forum Discussion
How to Classify Aggregated Fact Tables?
For simplification let us assume we have only three tables:
- DIM Item: Has columns item key and item description
- FACT Consumption: Has columns item key, date and quantity
- FACT Open Purchase Orders: Has columns purchase order key, date, item key and quantity
What if I now use GROUP BY to SUM the quantity for each item key in power query for FACT Open Purchase Orders? Is it then still classified and and treated as a FACT table in the data model? The result of the aggregation is that I have unique rows with the item key and the quantity sum. This would be a 1:1 relationship with the DIM Item table. Should I merge the aggregated FACT Open Purchase Orders table with the DIM Item table or use a relationship in the data model? What is best practice here?
Hi TimmK ,
You should create such a star model.
Understand star schema and the importance for Power BI - Power BI | Microsoft Docs
If you aggregate the quantity in the ‘Fact Open Purchase Orders’ table, then other fields in the table must also be aggregated. Unless the other fields in the table are useless, then you can do this.Best regards,
Lionel ChenIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
8 Replies
TimmK , You should Join Ite with Both fact. Create a date table and join it with both tables on date and order date
You should able to analyze data together for Consumption and Open purchase with Item and Date
refer: https://www.sqlbi.com/articles/the-importance-of-star-schemas-in-power-bi/
After the aggregation in the query editor FACT Open Purchase Orders will not have a date column anymore, because M was used to group by item key to sum quantity. The date has no relevancy for it because I only want to know the current open order quantity for each specific item key.
Hi TimmK ,
How about creating table relationships like this? If it's useless, please provide sample data of the three tables.
Best regards,
Lionel ChenIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Date does not exist anymore after the aggregation in power query for FACT Open Purchases. As I stated, I use a GROUP BY to SUM the quantity for each item key.
Hi TimmK ,
There is no ‘Item key’ field in the FACT Open Purchases table. How did you complete the aggregation?
Best regards,
Lionel ChenIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi TimmK ,
You should create such a star model.
Understand star schema and the importance for Power BI - Power BI | Microsoft Docs
If you aggregate the quantity in the ‘Fact Open Purchase Orders’ table, then other fields in the table must also be aggregated. Unless the other fields in the table are useless, then you can do this.Best regards,
Lionel ChenIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi TimmK ,
Has your problem been solved?
Best regards,
Lionel ChenIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
