Hitachi Vantara Pentaho Community Forums
Results 1 to 7 of 7

Thread: How to lookup a primary key in a Dimension lookup/update step

  1. #1
    Join Date
    Jun 2010
    Posts
    114

    Default How to lookup a primary key in a Dimension lookup/update step

    When I try perform a lookup on the primary key in a Dimension lookup/update step,
    I have to use PK in the Key fields TAB to lookup as well as in the Technical key field DROPDOWN.

    But, I get the following error
    DB2 SQL Error: SQLCODE=-121, SQLSTATE=42701, SQLERRMC=WH_MBR_DMSN_ID, DRIVER=3.50.152

    which means
    THE COLUMN name IS IDENTIFIED MORE THAN ONCE IN THE INSERT OR UPDATE OPERATION OR SET TRANSITION VARIABLE STATEMENT

    If we are not supposed to mention the column name more than once, is there a way to perform the lookup?
    Thanks,
    Om
    ----------------------
    Spoon version -4.4.0

  2. #2
    Join Date
    Apr 2008
    Posts
    4,690

    Default

    Correct me if I'm wrong, but isn't your Technical Key supposed to be different from your Business Key?

    You use your Business Key (eg. Current Value Primary Key) in the key fields tab, and your Technical Key (Primary Key of the SCD Table) in the drop-down
    **THIS IS A SIGNATURE - IT GETS POSTED ON (ALMOST) EVERY POST**
    I'm no expert.
    Take my comments at your own risk.

    PDI user since PDI 3.1
    PDI on Windows 7 & Linux

    Please keep in mind (and this may not apply to this thread):
    No forum member is going to do your work for you. We will help you sort out how to do a specific part of the work, as best we can, in the timelines that our work will allow us.
    Signature Updated: 2014-06-30

  3. #3
    Join Date
    Jun 2010
    Posts
    114

    Default

    May be my understanding is wrong. Let me explain the scenario...
    In SCD, I am selecting a bunch of rows in the fact table. Now, I need to pull up the rows in the dimension table for which the dimensions Ids are present in the current select statement.
    So, the values of FK's are coming in a stream and I want to do a look up on the Dimension table, what value are we supposed to give in the Key fields TAB?

    In other words, I am trying to match the PK of the dimension table with the FK of Fact table (in the stream) and get the other fields in dimension table.

    In this case,
    The Technical Key is always the PK
    But, I am not sure regarding what exactly am I supposed to give in the Key fields tab.
    Thanks,
    Om
    ----------------------
    Spoon version -4.4.0

  4. #4
    Join Date
    Apr 2008
    Posts
    4,690

    Default

    If I'm catching you correctly, you have:

    FACT
    -----
    ID | Dim_TK | Measure
    1 | 1 | 15
    2 | 1 | 30
    3 | 2 | 1
    4 | 3 | 2
    5 | 1 | 5

    SCD
    ----
    TK | Bus Key | Attr1 | Attr2
    1 | MyCo1 | Att | Oth
    2 | MyCo2 | Attribute | Another
    3 | MyCo3 | Something | Else

    And you want to get (for example) Attr2 for each fact row?
    ID | Dim_TK | Measure | Attr2
    1 | 1 | 15 | Oth
    2 | 1 | 30 | Oth
    3 | 2 | 1 | Another
    4 | 3 | 2 | Else
    5 | 1 | 5 | Oth

    This is a classic DB lookup step...
    **THIS IS A SIGNATURE - IT GETS POSTED ON (ALMOST) EVERY POST**
    I'm no expert.
    Take my comments at your own risk.

    PDI user since PDI 3.1
    PDI on Windows 7 & Linux

    Please keep in mind (and this may not apply to this thread):
    No forum member is going to do your work for you. We will help you sort out how to do a specific part of the work, as best we can, in the timelines that our work will allow us.
    Signature Updated: 2014-06-30

  5. #5
    Join Date
    Jun 2010
    Posts
    114

    Default

    Thanks a ton for your patience gutlez.
    I think, I am missing something when it comes to bussiness key or I need to get a better understanding on it.

    Let me give you my table structure

    FACT_TABLE
    ------------
    ID(PK)| dm_id(FK)
    1 | 111
    2 | 222
    3 | 333

    DMSN_TABLE
    -------------
    TK(PK)|Bus Key|Attr1
    111 | first | I
    222 | two | II
    333 | three | III

    Step 1) Table input: "Select * from FACT_TABLE "
    Step 2) Dimension lookup/update :
    Now, we need to get the rows from DMSN_TABLE for which the TK matches with the dm_id in FACT_TABLE
    Even though we have the Bussiness key in the DMSN_TABLE, I am not sure how to use it. As Bus Key does not exist in FACT_TABLE, I may not be able to use it for comparision.
    May be that is the reason I am trying to use the primary key column more than once.
    Once again, thanks for replying
    Thanks,
    Om
    ----------------------
    Spoon version -4.4.0

  6. #6
    Join Date
    Apr 2008
    Posts
    4,690

    Default

    I wrote a long message, but managed to goof up, and it disappeared

    So...
    Given the tables that either one of us have mocked up, what output would you want to see?

    In a perfect world, you would not have any rows in FACT_TABLE that do not have corresponding rows in DMSN_TABLE
    I find Dimension Lookup / Update is useful for populating your DW, not for pulling information out of it.

    The lookup part would be if you had that record in your DMSN_TABLE already, you don't need to update or insert a new row.
    The Update part is if you have new details (customer moved, changed names, etc) that need to be loaded into your DMSN_TABLE

    For getting information out of your DW, use table joins, or DB lookups...
    **THIS IS A SIGNATURE - IT GETS POSTED ON (ALMOST) EVERY POST**
    I'm no expert.
    Take my comments at your own risk.

    PDI user since PDI 3.1
    PDI on Windows 7 & Linux

    Please keep in mind (and this may not apply to this thread):
    No forum member is going to do your work for you. We will help you sort out how to do a specific part of the work, as best we can, in the timelines that our work will allow us.
    Signature Updated: 2014-06-30

  7. #7
    Join Date
    Jun 2010
    Posts
    114

    Default

    Now I get it, I shld use DB lookup instead of Dim Lookup/update for my scenario.

    I really appreciate your patience for writing it all again.... thank you
    Thanks,
    Om
    ----------------------
    Spoon version -4.4.0

Tags for this Thread

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.