I have 4 Facts and 3 dimension table. I have made a link table to link these fact table. Now the problem is, I have made link key from 3 fields in first 3 facts. But in the fourth Fact table I have only one field out of these 3 fields, So how should I link my fourth Fact table to the Link Table. Or which approach should I follow in linking my fourth Fact table to the model.
I have linked all my Dimension table to my link table I am explained in the diagram below:
Please suggest me the best approach for linking my last Fact Table to the model.
Create a LinkTable at the center of your current model. The LinkTable will match all Full Link Keys to Link keys that only have Dimension 1 values. This way you're matching keys with a high granularity (for example, Order Date values) to corresponding keys with low granularity (for example Budget MonthYear values)
Make sure that the LinkTable contains all key combinations from all connected facts tables. To avoid creating one facts table that filters all the others, move Dimension 1, Dimension 2 and Dimension 3 to the LinkTable as well.
No the 4th fact table cannot be joined to central big fact table as it has dimension key of only one dimension table. This is the problem. Now how should I link 4th fact table which has only Dim 3 key.
In this case, then you must first join the first 3 fact tables with a link key to get a big fact table, and then link the 4th fact table directly to the big fact table. See the model link as in the attached file.