ODI 11g - Updating existing records - Inserting new ones (based upon a key)

Today's post will be a simple but very useful business case in Data Integration and Data warehousing purposes.

- Populate a table will new records
- If the record is fully duplicate, reject it.
- If the record is a duplicate on a key, but there is a particular field changing, update that record

In Oracle Data Integrator 11g this is easily achieved through an Integration Knowledge Module known as IKM Oracle Incremental Update (MERGE) which comes built in with ODI11g.

To use it, there must be a key declared either in database side, or within ODI.

The example which I will cover will include declaring a UK within ODI.

-  Firstly make sure that source datastore and target datastores are created in ODI.







I reverse engineered this table inside ODI:








Create a new Interface and define source and target:






As mentioned above, a key has to be declared, in my case, an Employee is uniquely defined with EMPID, so EMPID is the column which will be used to determine whether a record exists or not!


Navigate to Quick-Edit tab and define EMPID as UK:




Now we are almost set, but we also need to define the knowledge module in the Flow tab:

Click on top of SrcSet (In my case it is a file so an LKM is needed to load it into an Oracle RDBMS table) - Select LKM File to SQL


Click on top of Target - Select IKM Oracle Incremental Update (Merge) specifically.


Make sure that you disable Flow Control (False) because CKM is not being used and it will throw an error otherwise:





The target table is currently empty, in the first run every record in the source set will be inserted, run it and insert every record if there is any:










Expanding Merge rows step, it can be seen that all of the 7 records have been inserted: