Many-to-Many Dimensions

In SSAS, data structures do not always conform to the snowflake or star schema model where one fact is associated with a single dimension member.  For example, consider the example of financial transactions in accounts that can have one or more customers. This can be modeled as:

The relationship between transaction and customer is a many-to-many relationship.  A single transaction can be associated with many customers and each customer can be associated with many transactions.  For example, I can have an individual account as well as a joint account with my wife.  So a single transaction in our joint account means that transaction is associated with many customers (my wife and I), and each customer (me) can be associated with many transactions (all my transactions for my individual account and my joint account).

In the example picture above, there are two dimensions and two fact tables.  The Customer dimension is the Many-to-Many (MM) dimension to the Transaction fact table.  The CustomerAccount measure group is the Intermediate Fact Table (called a bridge table in the relational world) and Account is the intermediate dimension.  The intermediate fact table is the fact table that relates the Many-to-Many dimension to the regular dimension.  In this case, it relates the Customer dimension to Account dimension.  The Transaction fact table is related to Customer in the conventional manner, but is also related to the Customer dimension via a many-to-many relationship.

More info:

Many-to-Many Dimensions in Analysis Services 2005

Dimension Relationships

The Many-to-Many Revolution 2.0

Analysis Services Many-to-Many Dimensions: Query Performance Optimization Techniques

About James Serra

James is a big data and data warehousing solution architect at Microsoft. Previously he was an independent consultant working as a Data Warehouse/Business Intelligence architect and developer. He is a prior SQL Server MVP with over 25 years of IT experience.
This entry was posted in Dimensions, SQLServerPedia Syndication, SSAS. Bookmark the permalink.

One Response to Many-to-Many Dimensions

  1. Pingback: Preparation for the 70-467 SQL BI exam | x86x64