locked
Many to many dimension RRS feed

  • Question

  • What is the best way to handle a many-tomany relationship in a dimension.  The fact table is sales order and has a customer key.  The customer dimension can then have multiple sales regions.  There are up to nine sales region types.  So there is a many-to-many relation between customer and sales region.  Is there a typical way to handle this scenario?  Is it best to create an intermediate bridge table?


    John Schroeder
    Tuesday, September 13, 2011 7:16 PM

Answers

  • Hi John,

    The many-to-many dimension relationship defines an association between a dimension and a measure group by specifying an intermediate fact table that is joined to the dimension table. An intermediate dimension table is in turn joined to both the intermediate fact table and to the fact table on which the measure group specified by the relationship is based.

    The many-to-many relationships between the intermediate fact table and both the dimension table in the relationship and the intermediate dimension create the many-to-many relationships between members of the primary dimension and the measures in the measure group specified by the relationship.

    You can refer to this document about an example of using the Many-to-Many dimension in SQL Server 2005 Analysis Services to analyze sales data http://technet.microsoft.com/en-us/library/ms345139(v=sql.90).aspx 

    Thanks,
    Challen Fu


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Proposed as answer by Kieran Patrick Wood Friday, September 16, 2011 9:53 AM
    • Marked as answer by Challen Fu Wednesday, September 21, 2011 10:05 AM
    Friday, September 16, 2011 9:44 AM

All replies

  • SSAS can handle many-to-many dimensions and has built in functions to do so.

    Check out the article and whitepaper:

    http://blogs.technet.com/b/andrew/archive/2008/01/22/many-to-many-dimensions.aspx

    http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=137

     

     

    Tuesday, September 13, 2011 7:47 PM
  • Hi John,

    The many-to-many dimension relationship defines an association between a dimension and a measure group by specifying an intermediate fact table that is joined to the dimension table. An intermediate dimension table is in turn joined to both the intermediate fact table and to the fact table on which the measure group specified by the relationship is based.

    The many-to-many relationships between the intermediate fact table and both the dimension table in the relationship and the intermediate dimension create the many-to-many relationships between members of the primary dimension and the measures in the measure group specified by the relationship.

    You can refer to this document about an example of using the Many-to-Many dimension in SQL Server 2005 Analysis Services to analyze sales data http://technet.microsoft.com/en-us/library/ms345139(v=sql.90).aspx 

    Thanks,
    Challen Fu


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Proposed as answer by Kieran Patrick Wood Friday, September 16, 2011 9:53 AM
    • Marked as answer by Challen Fu Wednesday, September 21, 2011 10:05 AM
    Friday, September 16, 2011 9:44 AM