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 SQL Server MVP and a independent consultant with the title of Business Intelligence/Data Warehouse/Master Data Management Architect and Developer, specializing in the Microsoft SQL Server BI stack.
This entry was posted in Dimensions, SQLServerPedia Syndication, SSAS. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>