Forum Discussion
Helper IVIs an Aggregated Fact Table Still a Fact Table?
To simplify things let us assume there are only two tables:
- DIM Item: Has columns item key, description
- FACT Open Orders: Has columns order key, date, item key, quantity
Now I use M in the query editor for FACT Open orders to apply GROUP BY to SUM the quantity for each specific item key.
The new, transformed Open Orders table has now only the columns item key and summed quantity - each row now is unique based on the item key. Order key and date do not exist anymore.
How do I classify the new, transformed Open Orders table? Is it still a FACT table? Should I have a 1:1 relationship to the DIM Item table (as seen on the picture below)? Or should I merge it with the DIM Item table? What is best practice according to the star schema?
Picture below shows the data model after the transformation of the open orders table.
Hi TimmK ,
since the table still contains the fact/key figure, it is still the fact table. There should be a 1:* relationship between dimension table and fact table in most cases and the filter direction should point from the dimension table to the fact table.
The question is, why do you need this aggregated view on the Open Orders table?
Best regards
Marcus
3 Replies
- mwegener
Most Valuable Professional
Hi TimmK ,
since the table still contains the fact/key figure, it is still the fact table. There should be a 1:* relationship between dimension table and fact table in most cases and the filter direction should point from the dimension table to the fact table.
The question is, why do you need this aggregated view on the Open Orders table?
Best regards
Marcus
TimmKThank you, mwegener
In some cases I am absolutely sure that I will not need a different aggregation, so I can use Power Query to filter and aggregate to improve performance significantly.
I am still confused why such an aggregated table would be classified as a fact table as it has unique rows and only one key. Essentially, after the aggregation it is just a copy of DIM Items, but with less rows and an additional column. Also, according to Leitfaden zu 1:1-Beziehungen - Power BI | Microsoft Docs it is stated that one should merge when there could be a 1:1 relationship. As there are no duplicate rows a 1:1 relationship would be possible in my case.
Besides, in my practical case the Open Orders table somehow contains additional item keys that do not exist in the DIM Item table. This leads to weird behaviour when having a relationship between the two tables. Merging would solve this.
- mwegener
Hi TimmK ,
as I said, the fact table is the table with the key figures, the uniqueness of the rows is not important here, but the existence of the key figure.With the Open Orders I could imagine that they are not item numbers at all, but resource numbers, G/L account numbers, .... At least that's how it would be for us in Dynamics NAV/Business Central.