Many to Many Dimensions

Overview

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, in a typical cube analyzing sales data, a single sales transaction is associated with a single customer, a single product, and a single point in time.

Data structures can be more complex. 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. Say we have 3 customers, John and Jane Doe, who share a joint account, and Sally Single, who has an individual account. If John and Jane contribute $100.00 to their account and Sally contributes $250.00, the result for all customers looks like this:

 

The sum for all amounts is not the sum of the individual amounts for each customer—that would double-count the data that John and Jane share. Instead, the total amount is the sum of all transactions. When computing a balance for a customer, the amount is the aggregate of all transactions to the accounts that customer is associated with, and not the simple sum of the transactions for each customer.

Setting up many to many dimensions in JDM

1. Define/select the table which will act as an intermediate between the 2 dimensions where there is a many-to-many relationship to the fact table. In the above example we have a table named “Customer Accounts” which holds the information of all the valid combinations of customers and accounts.
2. Add the intermediate table to the cube
   
   
   
3. Since all fact tables in a cube must have at least one standard measure defined we will add a hidden dummy measure as a count of one of the fields in the intermediate fact table
   
   
   
4. Add the dimensions to the cube and relate them to the fact tables
   
   
   
   
   Notice that account id can be related to both fact tables and customer id only to the intermediate fact table. The next step is to relate the customer id to the transactions fact table through the customer accounts table, i.e. the intermediate fact table.
5. Relate the M2M dimension the fact table
   
   
   
   
   This will relate the customers dimension to the transactions fact table through the customer accounts intermediate table.
   
   

From a performance point of view, M2M dimensions will have a negative impact.  Microsoft does not recommend setting up M2M relations on fact or dimensions tables larger than 1 million records.