Hitachi Vantara Pentaho Community Forums
Results 1 to 5 of 5

Thread: Bridge Table (many-to-many relation)

  1. #1

    Default Bridge Table (many-to-many relation)


    I have a many-to-many relation problem in my application and i would like to know which approache you normally use when facing this situation.

    I read about it and found some solutions, like using a bridge table or changing my fact table grain.

    First i would like to know: Does mondrian support a bridge table?

    What i'm sure now is that i won't use the positional flag solution simply because it can't work for my application.

    Thanks and best regards,
    Israel Guerra

  2. #2
    Join Date
    Oct 2007

    Default asdf

    Not inherently, no, but you can implement a multivalued dimension nonetheless.

    There's a few things you must be aware of. I'll cover those in a moment, but first I'm going to point out how you'll end up structuring things.

    Table F: Fact table
    Table B: Bridge table
    Table S: Bridge set table
    Table D: Dimension table

    Depending on how you have things structured, B may not be necessary. I used B strictly to use mysql's 'auto increment' feature to generate keys for each bridge set, then used an update query to add the keys back into the fact table so I didn't need to join to B. Implement this part however you like, though the point of this step was to create a key for this abstract concept of an item set. The item sets are actually defined in S, the first half of the key to S is just generated in B.

    S will use a combination of the key from B and a key from D as its primary key, and will have a weight factor as well (1/n, n is number of items in the set). In your cube, regardless of how this is implemented (<View> in your schema, a new view in the database, etc), the resulting fact table will be a crossjoin between F & S where the two keys from F (key to D and key to B) match the primary [compound] key to S. Keying for D in your mondrian schema is just a matter of choosing the key from the F&S crossjoin that matches the key in D.

    Next up, some things you'll wanna know:

    1) If you're using mysql or postgres, don't bother trying to use <View> or similar constructs within the cube to represent the fact table. You are going to have to create a view within your database. Mondrian attempts to do the necessary crossjoin as a subquery, and for some reason it doesn't work with those two. You may be able to find a better way to do this, if so let me know.
    2) If you aren't using mysql/postgres, you can *not* use a compound primary key for <View> and other similar constructs. You can do a custom sql query (again, can't use mysql or postgres for this) that does the join for you in your schema, but the short of it is: compound primary keys are not directly supported.
    3) When you do the crossjoin, your measures need to be either the aggregate of measure*weight, or weight, etc, to get your counts right.

    If you have further questions let me know and I may be able to help. I'm having trouble with my implementation of this with measures. For some reason, I get top level counts for measures (All only at highest level of aggregation), but all other levels regardless of hierarchy give null. I've had to move onto something else in the interim, but I'll be working on that again sometime soon. Hopefully you don't hit the same wall.


  3. #3


    Many thanks for the reply.

    I have been dealing with other things for some time and could not answer this.

    I wont test this for some time because i found out that a bridge table is not the best solution for my problem. Eventually i will test this and you can be sure i will post my experience here.

    Best regards,
    Israel Guerra

  4. #4
    Join Date
    Mar 2006


    Guys, I'm having the same problem.
    Find out this week using the method Bridge Table, but the comments in the forum may not be effective.

    For Oracle database, you have some kind of test or have any hint of what can be done?

    My situation:

    My sales are made by Brick and Pack, with that I have the PK of sales through these dimensions.
    I need to relate to my organizational structure to suit my table, this relationship must be made by both PK (Brick and Pack).
    The structure is related to Brick by PK, Pack is related to size through the table DM_DIVISION, DM_DIVISION relates of PK through the table.

    I am attaching a model for better understanding, perhaps you can help me

    Last edited by leandroconcon; 12-03-2008 at 09:36 AM.

  5. #5
    Join Date
    Nov 2012


    Hello Phantal, I really wish you be there because you look the only one in the whole Pentaho Community that have reached that far dealing with multi valued dimensions.

    When you said that the resulting fact table will be a crossjoin between F & S , I got confused.
    I thought that F should only have a key to B and S a key to B and D.
    In fact, I did not understand what you mean by "where the two keys from F (key to D and key to B) match the primary [compound] key to S."
    That would change the fact table grain, didn't?

    Hope you still be there...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.