Configuring Incremental Loading In Oracle BI Apps Part 2

In the previous post, I briefly talked about how Incremental loading works and how to go about it at the high level. So, it's time to get to the details.

So let's start with an easy example that there is 1 source and 1 target, which is PA_EVENT table. This table has 3 unique columns "Task_ID", "PROJECT_ID" and "Event_Num".

Before we start with the mapping, let's make these 2 columns the primary key in target designer:

In Dac, make sure we have 2 global parameters, or source system parameters:

$$LAST_EXTRACT_DATE: This is a variable that the value is determined at run time. DAC_SOURCE_REFRESH_TIMESTAMP is the default run time variable that stores the history of timestamps of all the updates made in the source table in the database.

$$LAST_UPDATE_FILTER: This is a parameter that I have created for informatica to use in it's source filter. This parameter's value is static with the logic: LAST_UPDATE_DATE > TO_DATE('@DAC_$$LAST_EXTRACT_DATE','MM/DD/YYYY HH24:MI:SS')

In other words, when I enter this parameter in the source qualifier, at run time the above column will be added as a where clause in the sql statement, filtering out all of the records that the last_update_date is less than last_extract_date from the source level.

So now let's start implementing the incremental logic in Informatica.

As you can see, the beginning of the mapping is very simple and straight-forward. We are going from source to applying the filter in SQ transformation, now here we are.

So now, everything that comes before lookup transformation should be the records that it's corresponding last update date is greater than the previous recorded timestamp, not all of the records stored in the source table.

Therefore, from these filtered records, we need to see whether we are going to insert them in the target or update them if it already exist in the target.

How do we know what to do? Well, we will look at the three unique columns "TASK_ID", "PROJECT_ID"and "EVENT_NUM" and see if they match from both source and target. This is where lookup transformation comes into the picture:

From there as we can see, there are 6 columns in the lookup, 3 are from the source, the primary key columns. The other 3 are created to be the lookup columns, there I am calling them LOOKUP_INPUT10, 11 and 12. These columns in reality, will be referring to the target columns of the same. So I am really just going from source.Task_ID to target.Task_ID here. Although it may look fancy.

Now, we need to set the lookup conditions as how we want to compare these fields. We have the option of setting source.Task_ID equal target.Task_ID or great or less or whatever. This time, I am setting the condition to be equal:

Now at run time, informatica doesn't know what lookup_input10 is TASK_ID. The value is going to come from DAC which we will get to later. Therefore, we need to specifically tell Lookup transformation about these things, therefore, we define lookup override in the property:

What I entered there is:

SELECT

$$LOOKUP_INPUT10 AS LOOKUP_INPUT10,

$$LOOKUP_INPUT11 AS LOOKUP_INPUT11,

$$LOOKUP_INPUT12 AS LOOKUP_INPUT12

FROM

$$TARGET_TABLE --

Each of the "$$" represents a parameter name that we will later define in DAC. So this SQL will be executed at run time and the result of this statement will be used to compared to the 3 columns from the source based on the lookup conditions.

In our particular case, think of it like this:

Source: The filtered 3 unique columns from the source has a list of records that are recently updated after the last loading.

Target: Select 3 columns from the target table

Lookup condition: (if ) Source = Target

Then generates 1 lookup output that holds the result of each comparison and bring it into expression transformation for assigning meanings to the lookup output:

So we are good so far. We will continue on finishing the mapping in my next entry:

Until next time: