Post reply

Problem with tables with many to many relations

  • April 15, 2008 at 8:29 am

    #379809

    I have this kind of data base structure

    1. Fact table (f)

    2. Table that contains Countries and that is referenced to fact table

    3. Table that contains Country groups

    4. Relation table between Countries and Country groups and that have one to many relations with Countries and Country groups tables

    How can I create cube with that database structure. I want to have Country groups as dimension and to have hierarchy with Countries.

    Any suggestions?

  • April 15, 2008 at 8:58 am

    #802626

    You can create a many-to-many relationship in a cube, but it is not really a hierarchy.

    In your case, "Countries" will contain the relationship between your facts and your "Country Groups".

    So, you would have to add "Countries" as a fact table.

    You could then associate the "Country Groups" to the facts through the "Countries" fact table.

    There are some automatic features in AS, but basically you end up specifying a multiplier in the many-to-many fact table - so if your fact is related to 2 fact groups, each of the "Countries" fact records for the relationship would have a multiplier of .5 allowing AS to properly calculate aggregations.

    Many-to-Many relationships is getting into some of the more complicated features of SSAS. If you are unsure of yourself, I would suggest you get a book on the subject and read up before you design something that is not going to work.

  • April 15, 2008 at 9:40 am

    #802660

    Hi Michael,

    Thak you for your post, but I'm confused. I have one more table in my database model, that is 'Relation table between Countries and Country groups'. Should I put this table as a fact or Countries table?

    Thank you!

    Tanya.

  • April 15, 2008 at 10:16 am

    #802677

    The relationship table - I thought it was all in the one table from your previous email.

  • April 21, 2008 at 2:55 am

    #804797

    Dear Michael,

    Thank you very much for your advices.

    The problem is solved and everything is working ok.

    Best regards

    Tanya

  • June 17, 2011 at 1:32 am

    #1340217

    How did you do that? I am also facing same issue. Can you plese explain me?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply