Implement many to many relationship with Dimensions

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.

DimKPIDetail

KPIKey KPIName
1 KPI1
2 KPI2

DimOrganisation

OrganisationKey OrganisationName
1 Org1
2 Org2

FactKPIValues

DateKey KPIKey Actual Target
20150101 1 90 100
20150201 1 70 60
20150101 2 89 108
20150201 2 45 40

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.

FactKPIOrganisation

KPIKey OrganisationKey EventCount
1 2 1
1 4 1
2 1 1
2 3 1

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.

Untitled

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.

Untitled2

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.

Untitled3

Now set the dimension usage between FactKPIValues and DimOrgnisation to many to many relationship by setting FactKPIOrganisation as intermediate measure group.As shown bellow.

Untitled5

This is it, how we have connected KPIValues measure group with organisation dimension.Hope you would find this as useful.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s