Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
TimmK
Helper IV
Helper IV

Is 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.

DM.PNG

1 ACCEPTED SOLUTION

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

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


View solution in original post

3 REPLIES 3

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

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


Thank 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.

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.

 

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.