Himu\’s Attempt at Blogging

Tidbits from my thoughts

ODI – Incremental Update and Surrogate Key using Database Sequence

with 17 comments

I am writing this because it had me lose my sanity for some time. If,

  • You’re using Oracle Data Integrator
  • You’ve defined a dimension table with a surrogate key even if it is not a SCD type 2 (recommended by Kimball)
  • The surrogate key is maintained through a database sequence (Oracle sequence in my case)
  • Your IKM in Incremental Update
  • You’re frustrated about how to control updates to a dimension having a sequenced surrogate key

Then read on for the solution that worked for me. The solution is pretty standard – you only need to know what you’re doing and setting the right options.

Step 1 – Create the database sequence object

For Oracle the command is similar to the following:


For example, to create the sequence named CUST_GROUP_SEQ in schema ABDW with a maximum value of 99999999, execute


Step 2 – Reverse-engineer your models

Well that’s obvious, isn’t it?

Step 3 – Configure the target datastore

In the Definition tab of your datastore, select Dimension as the OLAP Type

Step 4 – Design your interface

  1. Create a new interface
  2. Open the Diagram tab
  3. Drag the target datastore in place
  4. Drag the source datastore(s) in place
  5. Except for the surrogate key, do all the required column mappings
  6. Click on the name of the target datastore; in the properties panel, select <Undefined> as the Update Key
  7. Select the surrogate key column of the target datastore
    1. Select Execute On as Target
    2. Make sure the Key checkbox is not selected
    3. Clear the Check Not Null checkbox (as this is an auto-generated sequence)
    4. Clear the Update checkbox (as this is an auto-generated sequence)
    5. In the implementation box, write the following code (this is important):
  8. [MOST IMPORTANT] Select the natural key or unique key column of the target datastore
    1. Check the Key checkbox; this is the key that will be used by ODI to make comparisons during inserts/updates of the IKM
    2. Also, clear the Update checkbox
    3. If more than one column identify a row as unique then mark each one of them as Key columns in a similar fashion
  9. Go through the other columns and mark or clear the Insert/Update checkboxes as appropriate for your need
  10. Save the interface

That’s it! Execute the interface and everything should be fine. Let me know about your experiences.


Written by mhimu

May 4, 2009 at 4:35 pm

17 Responses

Subscribe to comments with RSS.

  1. Awesome! congrats! this is perfect and has cleared all my confusions about this!

    help me a lot.


    July 23, 2009 at 12:06 am

    • Thanks for the Info.

      Only problem I have seen is I am using IKM oracle merge and every run Sequence no is Un necessarily generating and loosing. Is therr any solution for this

      NIKIL KN

      January 25, 2016 at 12:30 pm

  2. Extremely helpful, especially for new users of ODI ! One recommendation, which doesn’t apply to setting up a surrogate sequence, but more as an FYI when trying to apply a new sequence to an ELT load, make sure your source tables have an index on the natural keys that you will be pulling from, especially for large recordsets. Again, wonderful posting, the only other thing that could help the user would be screenshots (I use snagit for these types of screenshots). Keep up the great blog!



    November 14, 2009 at 9:46 pm

    • Himu,

      What integration knowledge module are you using for your target datastore?

      I have implemented using target DB sequence also but using IKM ‘Oracle Incremental update (PL/SQL)’
      Then mapping my surrogate key as targetseq.nextval

      It works fine but I suspect it is slower because of the PL/SQL


      Does your method work with IKM ‘Oracle Incremental Update’ and does it greate a unique Surrogate key per row?



      February 24, 2010 at 5:06 pm

  3. i’m flattered 🙂


    November 15, 2009 at 9:38 am

  4. This is miracle.

    you are always like a ray of hope for us.



    July 28, 2010 at 3:57 pm

  5. Absolutely incredible!

    After 2 full days trying to solve this issue, you give me the solution!

    Congratulations! Himu for president!!

    Thanks a lot!

    Josue Borges

    August 26, 2010 at 3:30 am

  6. I followed the same thing, but its not working as a scd type2……


    June 19, 2012 at 3:18 pm

  7. WOW, Unbelievable, but it works. Thank you


    August 30, 2012 at 1:31 am

  8. Hi,
    The post is very helpful. However, I have a doubt. If the Surrogate key is the only Primary key in the target datastore, what properties of the Surrogate key column will be and how the sequence will be mapped to it ?

    Thanks in advance

    Harpreet Singh

    October 19, 2012 at 6:16 pm

  9. Nice post it helped a lot 🙂


    December 28, 2012 at 12:31 pm

  10. Hi,
    This post is really valuable..I was stuck with this for few days and this article is a life-saver.

    Thank you so much


    February 13, 2013 at 1:04 am

  11. Very well explained

    Dhananjaya H

    March 8, 2013 at 3:03 pm

  12. Nice explanation, Was searching for this, And I got now what I need to do. Thanks for your time for explaining it very clear.


    July 29, 2013 at 8:15 pm

  13. Hi Experts,

    In my project

    S-Turbo image (it’s a legacy system it will stored all file into single format i.e. turbo image technology)

    T -Sql server

    So it’s already developed project. like truncate and load process it will take so much time to execute and every time its load and delete, the data again load into target next day.

    they taught that we want only new records will inserting and old will be updated data no need every day truncate and load bcz its takes so much tiime.

    Already developed Process is: they are 2 levels of process and source is turbo image and target sql server having 2 schemas like HO, CDS

    Level 1 is, Load the data from turbo( its a already updated file ) to HO schema in sql sever (truncate and load every day).

    Level 2 is, load the HO schema tables data into views of CDS schema with same name of HO schema tables ( that views will created in DB side and reverser engineering into ODI level)

    And the next, they load the data from CDS views(source) into CDS target tables.(CDS schema have views of HO schema tables and Target tables)

    actually how they are moving the data into views in CDS is using procedures in 1 package like ‘Loadall’ in that procedures are create schema, Dest Schema, Get_Src schema,Genate views and Generates indexes..

    and they are using customized KM only ..They loaded data from turbo image to ‘x’ schema of Ms Sql server.. using LKM Sql to mssql , IKM sql to sql Append and CKM Sql.(truncate –>true)

    and 2nd level customized KM like..IKM MSSQL SCD CNI V1 No History
    IKM MSSQL SCD CNI V3(mostly using this KM)
    IKM Relator CNI Multiple
    IKM Relator CNI Unique
    IKM SQL to SQL Append CNI V2
    MSSQL Incremental Update CNI V2

    So we can you guide me where we have to start the process plzz.. Now where i can implement CDC and can you provide steps to do plz help me out…

    Cheers in advance…..:)


    January 31, 2014 at 3:07 pm

  14. nice post for 10g..


    January 5, 2016 at 5:36 pm

  15. Very usefull, yet in 2018. Thanks a lot buddy.

    Just one thing, the only way to “Make sure the Key checkbox is not selected” is deleting the PK constraint created in the reverse engeneering process, don’t you? if not, by default that checkbox is checked and locked.


    April 11, 2018 at 7:33 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: