Configuring Incremental Loading In Oracle BI Apps Part 1

Hello

As we all know, BI Apps is a package of tools with out of the box configuration catered to specific business area. It consists of pre-build Informatica ETL mappings, Dac Tasks and OBIEE data models & dashboards.

More often than none, we have to customized these pre-build objects in order to have it fit in to our specific project environments. These customization includes adding new fields across the ETL, changing the data models, adding incremental logic to non-incremental mapping and so on. Today, let's talk about how to configure Incremental Loading logic in BI Apps environment.

There are many different ways to implement incremental loading in BI Apps environment, the way I am going to do is by passing parameters from DAC into Informatica for incremental loading. The logic of the mapping is more or less similar to the standard Informatica practice.

For beginners, let's break down the process as follow"

1. Determine how many columns of the target table are unique columns that should be primary keys.

2. Filtering the source based on certain columns to determine what data are new data and what are old data. Usually, for each type II or type III Slowly Changed Dimension tables, "last_update_date" is almost the best column to filter on in source qualifier transformation.

3. Use lookup transformation to compare the unique columns from source to target and provide 1 output.

4. The value of the lookup output is either null or not-null as the result of comparing the source and target field. If everything in the source already exist in the target, then the lookup outcome is (depending on the lookup condition) null or not.

5. In the expression transformation, create conditions for null or non-null scenarios by assigning values such as "I" or "U".

6. In the update strategy transformation, using DD_Insert, DD_update commends in update strategy expression for each of those values from expression transformation.

The next article, we will look up the detail steps from Informatica to DAC on how to actually implement the incremental load

Until next time!