Friday, March 11, 2016

ODI 11g - Incremental Update load using Surrogate Key [Solved]

In one of our projects we had a scenario where we had to load data from an Oracle source to an Oracle target. We faced a lot of issues during the process. Then we came across Himu's blog post on the same, which saved us a lot of hours and frustration.

Below are some key pointers to note when using Incremental Update.
  • Do not make the Surrogate Key as Primary key in your Target Table.
  • Make the Natural Key as the Primary Key. 
  • Check Insert only for Surrogate Key and Natural Key
  • Disable 'Check not null' key on Mapping editor for Surrogate Key.  
ODI Mapping Quick edit screenshot - Refer for first four points
In the above diagram ROW_WID is the surrogate key and ROW_ID the natural key.
  • Make the Natural Key as the Update Key in flow tab.

This worked for us. Let us know if this helps. 

Do check out the Mhimu's blog post at https://mhimu.wordpress.com/2009/05/04/odi-incremental-update-and-surrogate-key-using-database-sequence/