1 Reply Latest reply: Apr 3, 2013 10:26 AM by JeromeFr RSS

    ODI loading a table like Dimension table


      I have a table with two columns.
      Table Name - PROGRAMS.
      Columns - Pgm_ID, Pgm_Type.

      What I am trying to accomplish is when I add a new row, I want to keep updating this table. But if the same Pgm_Type already exists, to ignore it.

      In my model I assigned this table as SCD OLAP type. Column Pgm_ID as surrogate Key, and Pgm_Type as Add row on change. In my procedure insert statement I added 3 additional rows, but I am getting a primary key violation. Is it possible to do this without adding additional columns for Current record flag and start and end time stamp etc., Is there any alternate way to handle this.

      Thanks for your time and help.
        • 1. Re: ODI loading a table like Dimension table
          You can use an incremental update KM instead. For example IKM Oracle Incremental Update (MERGE).

          - Set your UK (unique key) on Pgm_ID.
          - Select the Insert box for both Pgm_ID and Pgm_Type.
          - Select the Update box for Pgm_Type.

          So when you load data from source :
          - If the Pgm_ID is not in the target, the row is copied.
          - If the Pgm_ID is present in the target, the Pgm_Type is updated.

          Hope it helps.