How to handle Many to Many relationships in data warehouse dimensional modeling

In a relational database, the many-to-many relationship between two tables is resolved through third intermediate table. This intermediate table sometimes called as bridge table.

For example, relationship between FactInternetSales and DimSalesReason though the FactInternetSalesReason intermediate table.

For a data warehouse in a relational database management system, this is the correct model and you can write your own queries as you want, however, SSAS is not supported many-to-many in this similar model.

To solve this problem by creating intermediate dimension between both fact tables. You create it from the primary key of the FactinternetSales table. Let’s call this dimension DimFactinternetSales. The relationship between the FactInternetSales and the new DimFactInternetSales dimension is one-one.

The following images show how to implement in actual scenario using SSAS multidimensional model

Cheers! Uma