Hitachi Vantara Pentaho Community Forums
Results 1 to 5 of 5

Thread: Bridge table with kettle

  1. #1

    Question Bridge table with kettle


    I have a model to bridge table in my database.
    Link about bridge table (search for figure 6-17): + table + Kimball & source = bl & = HZs7EYPlwA Ots & sig = FvJrb5XiyB_Ry4LVb-pURito3sM & hl = pt-BR & ei = xJQ2SovmOt2wtgeS67yhCQ & sa = X & book_result hi = & ct = result & resnum = 4 # PPA270, M1
    First, I thought to use a lookup transformation doing with dimensions and then add in the fact (the normal), but i dont know the number of lookups that I need to do. I can not do loops with Kettle.
    I not using Mondrian, so I think the closure generation step is not an option. I can not change the model too. I need to find a solution to this model.

    So ... if someone qualifies?

  2. #2
    Join Date
    Nov 1999


    So basically you need to generate combinations of 2 sets of keys.
    Surely you can do joins etc to generate those.

    Please note that usually you can't just invent this data, it has to come from somewhere, preferably a source system ;-)

  3. #3


    The data comes from a source !

    My scenario:

    The fact has an attribute that can have between 1 .. N connections with the dimension. I can not put N attributes in fact without knowing the exact number and that I do not have to know.
    What I could do: a table of relationship between fact and dimension, but that does not solve my problem of multiple keys without knowing how I would.
    What I really did: the fact i linked in a table "umbrella" (called U_table) that has only one attribute as PK in U_table table and the relationship of the size (called bridge_table). The bridge table i linked in a dimension table.

    The link in the message I sent earlier shows this type of modeling.

    What I want: if a relationship between fact and dimension table already exists in the bridge table, I do not want to insert a new record at the bridge table, I want to use the existing registry. For this, for each set of relationships, I need to check whether this set of relationships is the bridge table.

    I not understand how to do this only using joins . Can anyone explain, please? Thanks!

  4. #4
    Join Date
    Nov 1999


    The fact has an attribute that can have between 1 .. N connections with the dimension. I can not put N attributes in fact without knowing the exact number and that I do not have to know.
    OK, so you already know the relationship. If the dimension is slowly changing, you need to first look up the surrogate key for the dimension.
    Then you need to maintain the bridge table, probably for a certain time period. The date key for that you need to look up too.

    Then you can use for example the "Combination Lookup/Update" step to keep a unique list of (Kimball example):

    • Diagnosis Group Key (to the fact, generated and maintained by the step)
    • Diagnosis Key (to the dimension, possibly slowly changing)
    • The date key (same as in fact, only if you are using snapshot data)

    The step will insert the row if it doesn't exist and not if it does. If you can have duplicates (again, i don't know your use-case) you need to simply slam it in there, use an auto-increment field and grab the next value or something like that.

    In any case the result will be that you will have N diagnosis' per diagnosis group in the bridge table.

    Perhaps you can make it easier on yourself by thinking about this differently....

    Suppose there is no such thing as a bridge table, only facts and dimensions. OK, so you have this dimension called a "Weighing Group" and it has a "Weighing factor" in there. The fact table is called "Weighing group bridge" and expresses the relationship between "Weighing Group" and "Diagnosis" (and time!).

    The second fact table is the "Health Care Billing Line Item Fact" (as in the picture from Kimball).

    Now, to retrieve the Diagnosis per line item, you need to join with the other fact table (our bridge table). Low and behold, it's exactly the same thing now. (especially if you then make the "weighing group" degenerate in the "Weighing group fact").

    Since I've always like the idea that a fact table expresses the relationships, nice and clean, I've always only modeled fact tables in my entire career. So OK, they look suspiciously like bridge tables if you think about it, and you do need a few extra keys in the extra fact table like the time, but you gain a lot of flexibility and storage is cheap these days anyway.


  5. #5


    Tks Matt!

    I will do some attempts using your help!


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.