Sunday, January 20, 2013

[DAC] - Full Load Vs Incremental Load

One of the key concepts in OBIA is the mechanism thru which incremental and full loads have been of implemented. The details are as follows:
Ø  In Informatica almost for every mapping there will be 2 types of workflows i.e. there will one workflow for the full load and another for incremental load.
Ø   In most of the cases these workflows will be calling the same set of routines (ETL mappings) except that the Incremental workflows will have Incremental sessions that have a SQL override with a condition such as EFFECTIVE_DATE >=$$LAST_REFRESH_DATE. Due to this condition these workflows will only extract the changed data set (Delta).
Ø   Another key thing to note here is the there is a separate set of mappings for identifying records that have been physically deleted from the source system, these mappings are The primary extract (*_Primary) and delete mappings (*_IdentifyDelete and *_Softdelete) mappings. These mappings use $$LAST_ARCHIVE_DATE mechanism to update the warehouse with source related deletions.
Ø   When you run an execution plan, the type of task or workflow which will be executed will be decided based on the value of the refresh date for the primary source or primary target table, If the refresh date is null DAC will automatically choose the task with full load workflow and if there is a value available for the refresh date DAC will execute the respective incremental workflow.
Note: The tasks with _full denote that it is used while running the full load and the other task with no _full will indicate that it is used for incremental load.
If you want to view this In DAC, you can see these both workflows in Design > Tasks and selectany one task and in the lower pane go to Edit tab. In Edit tab, there will be a Command for Incremental load and Command for full load and you will find the both the workflow names within these fields. The tasks with _full will be in Command for full load and the other will be in Command for incremental load.