This discussion is archived
7 Replies Latest reply: Apr 25, 2012 5:26 AM by 922032 RSS

Update/Insert Problem with Oracle Warehouse Builder

922032 Newbie
Currently Being Moderated
Hello,

i have update/insert problem with owb.

Situation: I have a source-table called s_account and a target table called w_account_d. In the target table are already data which was filled trough the source table inserts. Now anyone make changes on data on the target table. This changes should now give further on the source table with an update operation. But exactly here is the problem i can´t map back the data to source because that will create a loop.

My idea was to set a trigger but i can´t find this component in owb or is anywhere hidden?

Also i have already seen properties as CDC or conditonal loading in the property inspector of the table, but i have no idea how it works.
Give it other possibilities to modeling this case? or can anyone me explain how i can implement this eventually with CDC?


I look forward for your replies :)
  • 1. Re: Update/Insert Problem with Oracle Warehouse Builder
    Pnreddy Explorer
    Currently Being Moderated
    Hi,

    I believe your w_ tables are warehouse tables(or target tables). If is so why are you upadting the warehouse tables directly?
    If the warehouse tables are to be updated it should be according to the source tables changes, in other words your warehouse target should be in sync with your source, it can not be other way round.
    If you are updating the source tables depending on the warehouse targets then you have to reconsider the design.

    If you plan to use the triggers to update the source tables every time when the target is updated, this will affect the preformance greatly due to high data volume processing on warehouse tables. Also as for as my knowledge there is no option to fire a trigger through OWB.

    Even if you use the CDC mechanism to capture the changes on warehouse data you will end up with huge volumes of logs rapidly filling up your database server.

    If your intention is to keep the source and target in sync always you can go for SCD type 1(i.e No history) implementation and even in this case your target should be in sync with the source and it should not be the other way round.

    Regards,
    Pnreddy
  • 2. Re: Update/Insert Problem with Oracle Warehouse Builder
    922032 Newbie
    Currently Being Moderated
    Hi,

    for first thanks for you answer :) And at the next sorry for my wrong explanation. I meant the staging area table and not the source table. And by the way, yes the w_table are the warehouse tables but the process remains the same.

    Now i will try for a better understanding the big picture explain again

    I have a source (e.g. CRM or ERP-System) which loads data into staging area. now i have already data in the warehouse tables. In the next loading process i must compare the data in the warehouse table with the data in the staging area table. Are the same data (Key) in the staging area and in the warehouse but changes at other attributes as e.g. name or street then make an update. On the other side if the the data which are the staging area are not in the warehouse table make an insert operation.

    So how i can implement this?

    I look forward for your replies :)
  • 3. Re: Update/Insert Problem with Oracle Warehouse Builder
    Pnreddy Explorer
    Currently Being Moderated
    Hi,

    For this in your mapping which loads from staging to target you have to set the target table properties.

    Set the appropriate loading type(I think in your case it is 'UPDATE/INSERT')

    Then set the Loading Properties for each column of the target table accordingly.

    For key columns you have to set the properties as

    Load Column When Inserting Row - Yes
    Load Column When Updating Row - No
    Match Column When Deleting Row - No
    Match Column When Updating Row - Yes
    Update: Operation (=) which means TARGET=SOURCE

    For all other columns set the properties as:

    Load Column When Inserting Row - Yes
    Load Column When Updating Row - Yes
    Match Column When Deleting Row - No
    Match Column When Updating Row - No
    Update: Operation (=) which means TARGET=SOURCE

    Aslo make sure that in the Condition Loading Properties you set the 'Match by Constraint' as 'NO CONSTRAINTS'

    Regrads,
    Pnreddy
  • 4. Re: Update/Insert Problem with Oracle Warehouse Builder
    922032 Newbie
    Currently Being Moderated
    Hi,

    first thanks for the your answer. Now i have it implemented how you suggested me. Now i have the follow problem with this error message by the deployment of the mapping.

    VLD 2782: Missing constraints to perform INSERT/UPDATE for target table.
    --> Target table W_ACCOUNTdoes not have unique constraints defined. For load type INSERT/UPDATE in row-based mode, it relies on unique constraint for performing updates. Each row will first be inserted, and if an duplicate value exist on the unique index, it will perform an update.

    I have already definied constraints (Primary Key(by me it is INTEGRATION_ID)) in the Staging Table (W_ACCOUNT_DS) and the target table (W_ACCOUNT_D). but it doesn´t work. Have possible anyone a idea what could be wrong.

    I look for your answer :)
  • 5. Re: Update/Insert Problem with Oracle Warehouse Builder
    Pnreddy Explorer
    Currently Being Moderated
    Hi,

    Though the constraints are defined on the physical table, i feel you might have missed to import the same into OWB.

    Check the table properties in OWB and verify whether the target table and the stage table have the constraint information(i.e Primary Key)

    Regards,
    Pnreddy
  • 6. Re: Update/Insert Problem with Oracle Warehouse Builder
    922032 Newbie
    Currently Being Moderated
    Hi

    thanks for your answer. I follow your suggestion and have set the constraints of both tables into the database directly.Nevertheless it doesn´t work to begin. In the next step i found by right click on a table the listpoint "configure" - I goes to "unique key" --> creation method and set here follow options: Constraint State = ENABLE, Constraint Validation = Validate. That error message that appears before by the deployment disappears yet. Now i start the job to test if the insert/update process works right. Finally it seems to work - but not really.

    My Testscenario

    1. Load the data from source table about the staging area to data warehouse table: Check - it works!
    2. Change one data record in source table
    3. Load the source table with changed data record once again to staging area: Check - it works!
    4. Load new staging area table with the changed data record to data warehouse table: Check it works! BUT, BUT i can not recognize if it is insert or update operation, then under the design window by jobs execution windows is reported "rows selected 98", Rows inserted" is empty and "rows updated" is empty. So i think works not correct, then my opinion if it works correct it should show be "rows updated" 1.

    What can yet now still be wrong or forgotten? Any ideas?

    *By the way think not 98 rows there is not important if you make an update or insert which performance. It is an example table the right tables have million of records.*

    I look forward for your answers :)
  • 7. Re: Update/Insert Problem with Oracle Warehouse Builder
    922032 Newbie
    Currently Being Moderated
    Hi,

    i want come back to your idea with SCD Type 1. How can i implement my case with this? It would be nice when it goes you can give to me good manual (the best would be step-by step) how i implement my case with this.

    I look for your answer :)

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points