OWB Insert and update a same table

628810

Hi,

I want to load the delta (difference records) in target table . The target table will have ID,address,startdate,end_date and active_y_N . If the record(ID) is already there in target and address is changed then i have to make its as N ,assign end_date as sysdate (update) to that record and add oner more record for same id with startdate,Y and no end date.

Else if it is a new record , i have to insert it in target table with Y,startdate and no end date. Please let me know how to implement in owb.

Oleg

This type of tables is named Slowly Changed Dimension Type2 (SCD2).
You can use SCD feature from OWB10g R2 and later (if you buy OWB Enterpise ETL option),
but in this case you need define your target table as dimension, for more details look at OWB User guide:
[http://download.oracle.com/docs/cd/B31080_01/doc/owb.102/b28223/concept_data_modeling.htm#sthref248]

Or you can use Oracle recommendation for creating SCD mappings for older OWB releases (in this case you can use table as target),
look at this thread
714801

user Carsten give link for Oracle SCD whitepaper.

Regards,
Oleg

MccM

This is SCD2 as Oleg2 said, but if you just want to do what you're describing you can simply set your loading type to UPDATE/INSERT and create a merge statement without creating any dimensions.

558993

Hi,

It can be done in many ways and it depends on how u want to design it...I will give u a simple way. Take the record from src and do a look up on the target for the latest rec with the same id...then u can use merge at the target and and do an insert/update...

else u can use a splitter to split the same record into 2...use the 1st 1 as insert and in case there is already an existing record in the atrget u can join with it to get the old data and do an update to the existing record...

using SCD is also an option ...So u make the choice.

Regards
Bharath