Forum Discussion

TimmK's avatar
TimmK
Icon for Helper IV rankHelper IV
5 years ago
Solved

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...
Need Help
  • mwegener's avatar
    mwegener
    5 years ago

    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

mwegener's avatar
mwegener
Icon for Most Valuable Professional rankMost Valuable Professional
5 years ago

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

  • TimmK's avatar
    TimmK
    Icon for Helper IV rankHelper IV
    5 years ago

    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.

    • mwegener's avatar
      mwegener
      Icon for Most Valuable Professional rankMost Valuable Professional
      5 years ago

      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.