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
The Many-to-Many Revolution 2.0
Analysis Services Many-to-Many Dimensions: Query Performance Optimization Techniques
Pingback:Preparation for the 70-467 SQL BI exam | x86x64