locked
Many to Many Relationships RRS feed

  • Question

  • I have question regarding the Many to many relationship :

    Why Many to many relationship required at least one common Dim between the regular measure group and the intermediate measure group (as showing Fact Sales Dim in following figure)?

    Friday, January 11, 2013 6:55 AM

Answers

  • Hi Zaim,

    In simplistic terms, think of many-to-many as doing a join along a chain of tables. Those joins are done based on the shared dimensions. If you have no shared dimensions, the join cannot be done.

    Regardsm


    Elvis Long
    TechNet Community Support

    • Marked as answer by Zaim Raza Thursday, January 17, 2013 8:21 AM
    Thursday, January 17, 2013 8:04 AM

All replies

  • your design is a kind of weird to me, I've never saw such a design before! ... connecting Fact Sales as a measure group with Fact Sales as dimension. if you consider Fact Sales as a degenerated dimension then why you need an intermediate fact between the original fact and its degenerated dimension?! ... love to learn.

    anyways ... the following link contains a link to a nice white paper about the subject. it explains a lot of similar and even more complex many to many cases:

    http://sqlserverdiary.blogspot.com/2013/01/many-to-many-relationships-in-ssas.html

    Saturday, January 12, 2013 7:33 AM
  • In this case, AS is simply forcing you to break out the direct M2M relationship between the 2 fact tables into 2 separate 1-to-Many relationships.  So instead of Fact Sales directly related to your other fact (Salesrep Bridge), you need OrderID in the middle.  That way, you have a one-to-many between OrderID and Fact Sales, and another one-to-many between OrderID and Salesrep Bridge.  Believe this is to keep the implementation of the referential integrity simple and is common in many relational products. 

    For more details, check out this blog post from Chris Adamson, one of the dimensional modeling gurus out there.  He even uses the same Sales rep bridge example and, after breaking down the many-to-many into it's 1:M parts, ends up with this.

    Notice though that, instead of bloating the dimension between the 2 facts with millions of rows from the degenerate dimension (OrderId), he implements a grouping key for the Sales reps, which makes the dimension much more compact.  SQLCAT did a white paper on this technique here if you're interested.

    Hope that helps.


    Brent Greenwood, MS, MCITP, CBIP
    // Please mark correct answers and helpful posts //
    http://brentgreenwood.blogspot.com





    Saturday, January 12, 2013 3:53 PM
  • Hi Bret,

    Thanks for your reply.I have a scenario where I need to implement the Many to Many relationship. i know the performance issues pertaning to many to many relationship.

    i found very good example for the many to many relationships and i have implemented using this example which working fine.but i can't able to understand that why Many to many relationship required at least one common Dim between the regular measure group and the intermediate measure group ?

    http://www.bidn.com/articles/ssas-development/87/setting-up-many-to-many-in-ssas-2008-cube

    Thanks,

    Zaim Raza.

    Sunday, January 13, 2013 12:40 AM
  • Hi Butmah,

    Without the at least one common Dim between the regular measure group and the intermediate measure group you can't able to creat the many to many relationship and tha't the reason i am curios to konw this thingy.

    thanks,

    Zaim Raza

    Sunday, January 13, 2013 12:43 AM
  • Hi Zaim,

    In simplistic terms, think of many-to-many as doing a join along a chain of tables. Those joins are done based on the shared dimensions. If you have no shared dimensions, the join cannot be done.

    Regardsm


    Elvis Long
    TechNet Community Support

    • Marked as answer by Zaim Raza Thursday, January 17, 2013 8:21 AM
    Thursday, January 17, 2013 8:04 AM