Search code examples
oracle-databaseoracle-data-integrator

How to configure ODI to auto-increment rows for IKM Incremental Update

I have question about Autoincrement ID for my 'dimension' and IKM: Incremental Update.

I have source table with only one column: SUPPLIER_NAME. It has 23 rows with suppliers name.

I have target table with two columns: SUPPLIER_ID, SUPPLIER_NAME

Next I want on SUPPLIER_ID create autoincrement ID for every new row and use IKM: increment update - where someone add new supplier I only want update table (add new rowe) and add for this supplier new ID (next value for autoincrement).

How can I do this?

I create sequence on DB like:

Create sequence autoinc start with 1  
increment by 1  
minvalue 1  
maxvalue 1000000;

In ODI I create sequence: AutoIncrementDIm --> Increment: 1, Native seuqence - native sequence name: autoinc

Next I create ODI mapping:

Source table (with one colum) map to target table (with ID and NAME).

Map supplier_name to supplier_name

For ID I use: #NFS_HD.AutoIncrementDim_NEXTVAL

In logical part I set integration type: Incremental Update In physical part I set for IKM: IKM Oracle Merge

For my first running everthing it's ok. I have autoincrement from 1 to 23 for every supplier.

But when I have new rows with new supplier name in source table and run my mapping I get something like:

Results

New row (with new supplier) has 47 ID ... I think that s because sequence was running for every rows.

What I must change to correct or what it is the better solution to do this?

Solution

  • On the logical mapping, click on your SUPPLIER_ID target attribute. In the property pane in the Target tab, unselect the Update checkbox. It means that this attribute will not be used in the update query.

    enter image description here

    Also make sure that the SUPPLIER_NAME attribute is set as a key so the IKM use it to know when it should do an insert or an update.