cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TimmK
Helper II
Helper II

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
mwegener
Super User II
Super User II

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 work at KUMAVISION AG , one of the world's largest
implementation partners for Microsoft Dynamics. #
"Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website


View solution in original post

3 REPLIES 3
mwegener
Super User II
Super User II

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 work at KUMAVISION AG , one of the world's largest
implementation partners for Microsoft Dynamics. #
"Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website


View solution in original post

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 work at KUMAVISION AG , one of the world's largest
implementation partners for Microsoft Dynamics. #
"Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website


Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors