OWB - Target Operator Name Properties

> Data Integration Tool (ETL/ELT) > Oracle Warehouse Builder

1 - About

Under the operator name properties of a target mapping operator where you can load data such as:

you can set:

  • Primary Source,
  • Target Load Order,
  • and the Loading Type.

Depending upon the type of target, you can set different values for the Loading Type as described in Loading Types for Oracle Target Operators and Loading Types for Flat Files.

Advertising

3 - Properties

3.1 - Target Load Order

This property enables you to specify the order in which multiple targets within the same mapping are loaded. Warehouse Builder determines a default load order based on the foreign key relationships. You can overrule the default order.

3.2 - Loading Types for Oracle Target Operators

CHECK/INSERT Warehouse Builder checks the target for existing rows. If there are no existing rows, Warehouse Builder inserts the incoming rows into the target
DELETE Warehouse Builder uses the incoming row sets to determine which of the rows on the target to delete.
DELETE/INSERT Warehouse Builder deletes all rows in the target and then inserts the new rows.
INSERT Warehouse Builder inserts the incoming row sets into the target. Insert fails if a row already exists with the same primary or unique key
INSERT/UPDATE For each incoming row, Warehouse Builder performs the insert operation first. If the insert fails, an update operation occurs. If there are no matching records for update, the insert is performed. If you select INSERT/UPDATE and the Default Operating Mode is row based, you must set unique constraints on the target. If the operating mode is set based, Warehouse Builder generates a MERGE statement
NONE Warehouse Builder performs no operation on the target. This setting is useful for testing. Extraction and transformations run but have no effect on the target
TRUNCATE/INSERT Warehouse Builder truncates the target and then inserts the incoming row set. If you choose this option, Warehouse Builder cannot roll back the operation even if the execution of the mapping fails. Truncate permanently removes the data from the target
UPDATE Warehouse Builder uses the incoming row sets to update existing rows in the target. If no rows exist for the specified match conditions, no changes are made
If you set the configuration property PL/SQL Generation Mode of the target module to Oracle 10g or Oracle 10gR2, Warehouse Builder updates the target in set based mode. The generated code includes a MERGE statement without an insert clause. For modules configured to generate 9i and earlier versions of PL/SQL code, Warehouse Builder updates the target in row based mode.
UPDATE/INSERT For each incoming row, Warehouse Builder performs the update first. If you select UPDATE/INSERT and the Default Operating Mode for the target is set-based, Warehouse Builder generates a MERGE statement
INSERT/LOAD For cubes, OWB will try to insert all input data into the target
LOAD For dimensions and cubes, OWB will try to merge all input data into the target.
REMOVE For dimensions and cubes, OWB will try to match between the input data and target data to compute existing data; it will then remove existing data from target.
Advertising

3.3 - Primary Source

For Oracle Application Embedded Data Warehouse (EDW) users, refer to EDW documentation. For all other users, disregard this parameter.