Configuring Incremental Loading In Oracle BI Apps Part 3

In the previous post, we have covered up until configuring the lookup transformation. So now we know that we have 1 output from lookup transformation and it is brought into the expression transformation.

So from there, as we know that the lookup output are either going to have a list of records that satisfies the lookup condition or it is NULL when it doesn't satisfy the condition. In expression, will define meanings for the NULLs and No-Nulls by creating an 'update Flag' port:

The input port here is LOOKUP_INPUT10 which comes from lookup transformation. The output port "update Flag" gets its value from a condition that is based on the input port:

IIF(ISNULL(LOOKUP_INPUT10),'I','U')

So here I am assigning "I" and "U" for either LOOKUP_INPUT is Null or No_Null. You can choose other symbols as you like. It doesn't have to "I" or "U".

In other words, if Lookup_Input is Null, that means the lookup condition: Source.Task_ID = Target.Task_ID, Source.Project_ID = Target.Project_ID, Source.EVENT_NUM = Target.EVENT_NUM are not satisfied. Meaning that this particular record exist in the source that are recently populated (based on last update date filter ), but not in the target. Therefore, this record is flagged with "I". If the lookup_Input is not Null, then flag it with "U".

Depending on the data and the environment that we are working, sometimes just looking at whether 1 column is Null or not may not be enough. If those cases occurs where you need to make sure all of the lookup inputs satisfy the conditions or not, you can concatenate them in the expression. Converting non-text datatype before concatenation is also possible. An example will be:

In the above situation, I bring all of the needed lookup input columns into expression, then in the update_flag expression, I have:

IIF(ISNULL(to_char(LOOKUP_INPUT10)||'~'||to_char(LOOKUP_INPUT11)||'~'||

LOOKUP_INPUT12||'~'||LOOKUP_INPUT13||'~'||TO_CHAR(LOOKUP_INPUT14)||'~'

||TO_CHAR(LOOKUP_INPUT15)),'I','U')

You can explore your own environment and find out what works for you.

Now bring update Flag along with all of the columns from the source into Update Strategy Transformation where we will actually tell the transformation what to do when loading to the target:

In the update strategy expression, enter:

IIF(UPDATE_FLAG = 'I',DD_INSERT,IIF(UPDATE_FLAG = 'U',DD_UPDATE,DD_REJECT))

Again here, we are associating "I" with DD_Insert, "U" with DD_Update, and everything else will be DD_Reject. These are the actual commands that Update strategy uses to interact with the target table.

Now that the mapping is built. Yet, because we are using parameters that the values are passing from DAC, therefore, in order for Informatica to receive these values, it needs to know what they are. For that reason, we have to add the mapping parameters in this specific mapping:

Here we are adding 6 parameters to the list, because they are what's used in this mapping:

$$LAST_EXTRACT_DATE --- Referred by the expression in $$LAST_UPDATE_FILTER

$$LAST_UPDATE_FILTER --- Used in Source Qualifier Transformation

$$LOOKUP_INPUT10 --- Lookup Override

$$LOOKUP_INPUT11 --- Lookup Override

$$LOOKUP_INPUT12 --- Lookup Override

$$TARGET_TABLE --- Lookup Override

So from this point, we are done with the mapping.

In the next entry, we will continue on with the configuring the tasks and workflow in Workflow Manager.

Until Next Time!