Data model - connect 2 fact table

[BlueRocky40]

Hello, Can anyone help me with my data model. I'm not sure if my data model is correct or not. I can get correct data if I will use the Dim1, Dim2 Dim3 filter  but unable to get the correct data if my filter is through Dim4, Dim5 and Dim6. How can i relate the 2 Fact Table as these 2 tables having many duplicates. And to get the correct data when accessing either both sides (Dim4, Dim5, Dim6) of the tables using group by and to filter by weekly cutoff of Plan and Actual. Thanks for any help.

[Nathaniel_C · Accepted solution]

Hi @BlueRocky40 ,

You will have to make sure that you have interim tables for filtering because typically your fact tables will have differing granularities. Filters flow downhill from dim tables to fact tables is why your filter are not working as you expect. So Dim 4 and 5 are fine if you are filtering "down" to Fact1, but will not flow "up" from Fact1. Dim6 will work fine as a filter for Fact2 as it flows "downhill" to Fact2. So, you are on the right track as you have Dim 1, 2, and 3 that flow "down" to both.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

[BlueRocky40]

Hi @Nathaniel_C thanks for the responses, I got the correct model now. You are right to create interim table and to remove all the duplicates and link with one side of the table is cross filtering. Thanks.