Many to Many Dimensions

One of the many cool things that's already in Analysis Services in SQL Server 2005 (SSAS) is the concept of many to many dimensions.

Suppose I have 3 bank accounts and one of those I share with my wife, and on another I am co-signatory for a local charity. This means there is a many to many relationship between me and those accounts.  In the relational world this is typically dealt with using a bridging table - which in this case would have columns with AccountID (to reference the account table), AccountHolderID (to reference the signatories) and a few other fields such as SignatureImage, etc.

In analysis services many to many dimensions work in a similar fashion,  but where in the relational world they would be linked directly to each other, in the multi-dimensional world, the two dimensions in our scenario would be linked by the use of an intermediate fact table, joined to the main fact table.  There's an example (as always!) in the Adventure Works sample SSAS solution, where dimSalesReason links to the main fact table (FactInternetSales) via an intermediate fact table:

Many to many dimensions can be useful to improve the performance of distinct counts, and to carry out basic basket analysis (customers who bought X also bought Y). Mark Russo has an excellent white paper on this whole area running to 84 pages of step by step instructions to get you started.

However this technique comes at a price and can cause more performance problems than it solves.  A new whitepaper came out late last year and discusses the pros and cons of this approach and how to get the best out of it, careful use of keys and aggregation design.

Technorati Tags: Analaysis Services,SSAS,Many to Many Dimensions