apps-infra

    Forum Stats

  • 3,873,670 Users
  • 2,266,623 Discussions
  • 7,911,597 Comments

Discussions

OWB Insert and update a same table

628810
628810 Member Posts: 2
edited Oct 3, 2008 11:36AM in Warehouse Builder
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.

Answers

  • Oleg
    Oleg Member Posts: 1,463
    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
    MccM Member Posts: 442 Bronze Badge
    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
    558993 Member Posts: 625
    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
This discussion has been closed.
apps-infra