Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

James Serra's Blog

James is currently a Senior Business Intelligence Architect/Developer and has over 20 years of IT experience. James started his career as a software developer, then became a DBA 12 years ago, and for the last five years he has been working extensively with Business Intelligence using the SQL Server BI stack (SSAS, SSRS, and SSIS). James has been at times a permanent employee, consultant, contractor, and owner of his own business. All these experiences along with continuous learning has helped James to develop many successful data warehouse and BI projects. James has earned the MCITP Business Developer 2008, MCITP Database Administrator 2008, and MCITP Database Developer 2008, and has a Bachelor of Science degree in Computer Engineering. His blog is at .

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 Customer Account 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

Comments

Posted by Anonymous on 19 November 2011

Pingback from  Many-to-Many Dimensions | SQL Server | Syngu

Leave a Comment

Please register or log in to leave a comment.