Normally when it’s come to data warehousing, a fact table can be linked to one or many dimensions and when it’s come to snow flex schema, a dimension can be linked with one or many dimensions. But there are scenarios where linkage between dimensions are many to many. See bellow case which I came across.
In here, a KPI can be linked to many organizations and similarly an organization can be linked to many kips. Which means many to many relationship between these two. How do we address this kind of scenarios in DW designing? For handle this kind of scenarios, we need to have an intermediate fact table, which shows the relationship.See the fact table bellow.
This is fine,But now how do we get in this to SSAS? In SSAS we have to set dimension usage with measure groups. First create two dimensions in SSAS as shown bellow.
Create measure group FactKPIValues and add FactKPIOrganisation to that measure group. This is very important that both the measures should be in same measure group .And since we don’t want to see FactKPIOrganisation , set visibility of the measure to false.
Now we go to dimension usage panel page and set dimension usage for these measure groups.
As shown bellow, FactKPIValues link with DimKPIDetail and DimDate with regular relationship, and FactKPIOrganisation has regular relationship with DimKPIDetail and DimOrganisation. But we want to see KPI values with organisation. For that I have to set Many to Many relationship with FactKPIValues and DimOrganisation.This is how the linkage works.
Now set the dimension usage between FactKPIValues and DimOrgnisation to many to many relationship by setting FactKPIOrganisation as intermediate measure group.As shown bellow.
This is it, how we have connected KPIValues measure group with organisation dimension.Hope you would find this as useful.