OWB 11gR2 – Incremental Update
By David Allan-Oracle on Jul 11, 2010
In OWB the merge or insert/update mode can be constructed by either indicating that keys are used (or a key) for matching or specific columns are used for matching. This is true for database resident mappings or code template based mappings. To understand how the code template support works, let's look at how interfaces and Knowledge Modules are used and defined in ODI.
Update Matching Criteria
To define how rows are compared to see if they are the same, in ODI there is an 'Update key' selector, so if you want to match on a specific key, use this. In the figure below the target data-store has an update key property that lets you define the key to be used (which can be retrieved using the UK property value when using column list APIs from within a KM).
Figure 1: Target data-store property panel in ODI.
So can you control the specific columns to match on? Yes, this can be achieved by setting the 'Update key' to '<Undefined>'.
Figure 2: Target data-store with no 'Update key' defined in ODI.
Then setting the 'Key' property on the specific columns of the target data-store.
Figure 3: Target data-store column property panel in ODI, define 'Key' column.
So how is this done in OWB?
In a very similar manner of course. If you want to use a constraint, then the target data operator should have the 'Match by constraint' property set.
Figure 4: Target table operator properties in OWB.
You can also manually set how the matching is to be performed, firstly by switching the 'Match by constraint' property to 'NO_CONSTRAINTS'.
Figure 5: Target table operator with match by constraints disabled in OWB.
Then just as in ODI where you set specific columns to be used for matching, you click on the column and define the matching columns. Some of the properties below are only applicable to database resident mappings such as the 'Update Operation', this kind of property would have to be achieved in the code template via some best practice using the user defined tags.
Figure 6: Target table operator attribute properties in OWB.
How to determine insert and update columns......?
In ODI there are insert/update metadata flags on each target column (also the user defined tags, see below). This metadata is also in OWB - that is the update metadata flag, user defined tags and so on.
In OWB for example, each attribute in the table operator has a bunch of tag properties, for example see the figure below under Knowledge Module Metadata Tags. Note the SCD, user defined tags and so on.
Incremental Update - is that all and it works?
Incremental update depends on the code template, not all code templates support incremental update, some are appending data for example. It depends on the code template and its use of the ODI Reference API. Generally the code templates have the details (in the description, so read it) of what the user must do in order to use the code template. For example in the Oracle Incremental Update code template, the description indicates the comparison of data uses the Update Key - it must be set. Without this, the user will get an error stating that no key has been set.
Figure 9: Read the descriptions.
Having said that if you are using the database resident OWB mappings or the Oracle Target code template then the insert or merge or delete logical operation indicator is defined on the target table operator or operators. So there is a property 'Loading Type' which has many of the values you'd expect INSERT, INSERT/UPDATE, DELETE and so on.
With the open code templates you still have to watch out since code templates such as the integration SQL Control Append code template does not have such a description - the fact that it may utilize a Control Code Template (CCT) means you should check the CCT description, in the CCT description it states 'Data cleansing can be performed only if an update key is defined on the controlled table.' - all CCTs need an update key, so if you see a code template with Control in the name a key is required to be set.
A quick run through cross referencing some areas of ODI and OWB hopefully filling in a few pieces of the jigsaw.