I have a data model, which has two facts Fact1 and Fact2 which are concatenated, common Dimension tables of Prod and Geo and a relate table which stores the affiliations between the two fact tables. Attached is the dummy implementation of the data model.
Now, there is a small issue that the Geo hierarchy is not connected to both the Fact tables directly. It has G1Key which is present in Fact table F1. But the fact table Fact2 has another key as G2Key, and there is a Mapping table which has G1Key and G2Key mapping.
Now i tried joining the Fact2 table which this mapping table on G2Key to get the G1Key which can act as a common link between the concatenated fact table and the Geo hierarchy, but then there is a many to many relationship between G1Key and G2Key, and joining that table with the fact table Fact2 will inflate the rows in the fact.
So, i thought of joining the Geo Dim table with the Mapping table ,but that also doesn't give desired results since the join now happens on a synthetic Key of G1Key,G2Key.