Conditional loading

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

You can set Target Filter for Update, Target Filter for Delete, and Match By Constraint.

1 - Target Filter for Update

If the condition evaluates to true, the row is included in the update loading operation.

2 - Target Filter for Delete

If evaluated to true, the row is included in the delete loading operation.

3 - Match By Constraint

When loading target operators with the UPDATE or the DELETE conditions, you can specify matching criteria. You can set matching and loading criteria manually or choose from several built-in options.

Use Match By Constraint to indicate whether unique or primary key information on a target overrides the manual matching and loading criteria set on its attributes. When you click the property Match By Constraint, Warehouse Builder displays a drop down list containing the constraints defined on that operator and the built-in loading options.

Advertising

3.1 - All Constraints

If you select All Constraints, all manual attribute load settings are overruled and the data is loaded as if the load and match properties of the target attributes were set as below.

All Constraints Target Load Settings

Load Setting Key Attribute All Other Attributes
Load Column when Updating Row NO YES
Match Column when Updating Row YES NO
Match Column when Deleting Row YES NO

3.2 - No Constraints

If you select No Constraints, all manual load settings are honored and the data is loaded accordingly.

If you select a constraint previously defined for the operator, all manual attribute load settings are overruled and the data is loaded as if the load and match properties of the target were set as displayed in Table 25-4.

Target Load Settings for a Selected Constraint

Load Setting Selected Key Attributes All Other Attributes
Load Column when Updating Row NO YES
Match Column when Updating Row YES NO
Match Column when Deleting Row YES NO
Advertising

3.3 - Advanced - Reverting Constraints to Default Values

If you made changes at the attribute level and you want to default all settings, click Advanced. Warehouse Builder displays a drop down box list containing the loading options as shown in Figure 25-2. Warehouse Builder defaults the settings based on the constraint type you select.

Advanced Settings for Match By Constraint“

For example, if you want to reset the match properties for all key attributes, click Advanced, select No Constraints, and click OK. Warehouse Builder overwrites the manual load settings and loads the data based on the settings displayed in Table 25-4.

Table 25-4 Default Load Settings for Advanced No Constraints

Load Setting All Key Attributes All Other Attributes
Load Column when Updating Row YES YES
Match Column when Updating Row NO NO
Match Column when Deleting Row NO NO

Alternatively, if you click Advanced and select All Constraints, Warehouse Builder overwrites the manual load settings and loads the data based on the settings below.

Default Load Settings for Advanced All Constraints

Load Setting All Key Attributes All Other Attributes
Load Column when Updating Row NO YES
Match Column when Updating Row YES NO
Match Column when Deleting Row YES NO
Advertising