ODI – Incremental Update and Surrogate Key using Database Sequence

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:

CREATE SEQUENCE <SCHEMA>.<SEQUENCE_NAME> CACHE 20 MAXVALUE <MAXVALUE> MINVALUE 1 INCREMENT BY 1 START WITH 1

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

CREATE SEQUENCE ABDW.CUST_GROUP_SEQ CACHE 20 MAXVALUE 99999999 MINVALUE 1 INCREMENT BY 1 START WITH 1

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):
      <%=snpRef.getObjectName("L","GROUP_KEY_SEQ","D")%>.nextval
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.