locked
Struggling with many to many RRS feed

  • Question

  • Hi there

    I'm really confused with a many to many scenario I'm trying to model. It seems very vanilla, but just can't seem to get my head around it. Any advice would be very gratefully received.

    It's a real estate cube

    We have a Branch dimension. A branch operates in many postcodes (Zip Codes). Many branches will operate within any postcode

    So we have a bridge table, where one branch key has many postcode keys, and one postcode Key will have many branch keys. Conceptually, it looks like this

    BranchKey PostcodeKey

    1 141

    1 982

    2 141

    2 86

    We have a Fact table, with one row for each property. One of the keys is Postcode key.

    I want to be able to select a branch member, and return all the properties that are within the postcodes that that branch operates in. SO in the example above, I want to select Branch1, and return all the properties that are in postcodes 141 and 982.

    Should I be having the bridge table as a measure group, with a regular relationship to the Branch dimension?

    Then how do I link the bridge table measure group to the fact table? Do I need an intermediate dimension between them? And what should it be formed from? A degenerate dimension on the fact table?

    Thanks in
    advance for any responses.



    • Edited by Sam Loud Wednesday, January 16, 2013 1:46 PM messed up formatting
    Wednesday, January 16, 2013 1:44 PM

Answers

  • Sam -

    The relationship between your Bridge measure group and your Bridge dimension (bottom right in your matrix above) should be on postalCodeKey instead of branchkey.  This is because postalCodeKey should behave as the "grouping" key in this case.  From your description above, PostalCode allows the Bridge (intersection of postalCode and Branch) to relate multiple branches to a single property (fact row). 

    Let me know if that helps.  Or if you need more information.


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




    • Edited by Brent Greenwood Monday, January 21, 2013 3:57 PM
    • Marked as answer by Sam Loud Tuesday, January 22, 2013 10:31 AM
    Monday, January 21, 2013 3:55 PM

All replies

    1. create Branch dimension
    2. create postcode dimension
    3. create branchpostcode fact by dropping one of the keys in measure area to have a branchcount measure or postcodecount measure
    4. create regular relationship between Branch dimension and branchpostcode fact
    5. create regular relationship between postcode dimension and branchpostcode fact
    6. create a many to many relationship between Branch dimension and the fact having property data through branchpostcode fact

    once processed and when property data is sliced by branch dimension it sums up all the related postcode rows and gives an aggregate value

    Hope that resolved your issue...

    Wednesday, January 16, 2013 2:15 PM
  • Hi there,

    You would add  your primary fact table measure group, a bridge fact table measure group, and use a many to many link on the primary measure group with intermediate link to bridge table, and a regular link on the bridge fact table group to a dimension that contains the relationship between the two.  The bridge table would also have a many to many link on the intermediate dimension.

    Links

    http://www.sqlbi.com/articles/many2many/

    http://sqlblog.com/blogs/marco_russo/archive/2004/10/04/M2M.aspx

    http://www.softcodedlogic.com/CompressManyToManyRelationships.htm

    cheers,

    Andrew


    Andrew Sears, T4G Limited, http://www.performancepointing.com

    Wednesday, January 16, 2013 2:23 PM
  • Thanks Andrew and Rakesh. Give me 10 minutes, and hopefully I'll back high-fiving you both

    Sam

    Wednesday, January 16, 2013 2:27 PM
  • OK, I think I've worked that through.

    Ive got the PropertyListings measure group, the BRIDGE measure group, the Branch dimension, and the BRIDGEdimension. I don't need a postcodes dimension at this point.

    the Dimension Usage matrix looks like this

                    PropertyListings       BRIDGE         

    Branch      M2M*                        BranchKey

    BRIDGE     PostcodeKey            BranchKey

    *M2M with BRIDGE as the intermediate measure group

    When I go to process, I get this error:

    Error 5 Errors in the metadata manager. The 'Branch' many-to-many dimension in the 'PropertyListings' measure group requires that the granularity of the 'BRIDGE' dimension is lower than that of the 'BRIDGE' measure group.  0 0 

    I'm sure I'm very close, but I haven't come across this error before - any ideas? Thanks for your help guys

    Wednesday, January 16, 2013 3:44 PM
  • Hi Sam,

    Please take a look at the following article to troubleshoot this issue:
    Error with many-to-many dimension and level of granularity: http://bifuture.blogspot.com/2012/01/ssas-error-with-many-to-many-dimension.html

    Regards,


    Elvis Long
    TechNet Community Support

    Monday, January 21, 2013 9:36 AM
  • OK Elvis, I'll give that a go. Thanks for your help.
    Monday, January 21, 2013 3:34 PM
  • Sam -

    The relationship between your Bridge measure group and your Bridge dimension (bottom right in your matrix above) should be on postalCodeKey instead of branchkey.  This is because postalCodeKey should behave as the "grouping" key in this case.  From your description above, PostalCode allows the Bridge (intersection of postalCode and Branch) to relate multiple branches to a single property (fact row). 

    Let me know if that helps.  Or if you need more information.


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




    • Edited by Brent Greenwood Monday, January 21, 2013 3:57 PM
    • Marked as answer by Sam Loud Tuesday, January 22, 2013 10:31 AM
    Monday, January 21, 2013 3:55 PM
  • And that did the trick Brent. Nice and clear explanation, so now it's not just fixed, I understand it too. Thanks very mcuh.
    Tuesday, January 22, 2013 10:31 AM