IKM BIAPPS Oracle Incremental Update

This IKM integrates data into an Oracle target table in incremental update mode. New records are inserted and existing records are updated. Data can be controlled by isolating invalid data in the error table and recycling when fixed.


This IKM now supports ETL diagnostics and automatic corrections. The flow control option is no longer used with this IKM.


Prerequisites for using this IKM are:
  • The update key must be defined in the interface and the key columns should be indexed.

  • If the "Synchronize Deletions from Journal" process is executed, the deleted rows on the target are committed.

Options for Functionality

  • Soft Delete — There are several additional steps that you can perform if the soft delete option is enabled. The variables #SOFT_DELETE_FEATURE_ENABLED (global) and #SOFT_DELETE_PREPROCESS (can be set for each fact or dimension group) control exactly which steps are executed.

    If you are able to implement triggers to efficiently capture deletes on the source system, you can disable the expensive pre-process steps (which extract all source records and compare against all target records) and, instead, directly populate the delete table.

    Step Action Control

    Soft delete pre-process

    Runs the "Identify Delete" step which compares the data in the primary extract table against the target table, and records any obsolete target rows in the delete table.

    • Uses #LAST_ARCHIVE_DATE to filter target by CREATED_ON_DT system column (set variable to NULL to disable this).

    • Only data sources that have implemented the primary extract are included. If a data source has no records in the primary extract table then no records will be added to the delete table for that data source.



    Soft delete on target

    Runs the "Soft Delete" step which updates the DELETE_FLG column to 'Y' on the target table for records which have been identified for delete.


    Truncate delete table

    Removes records from the delete table once they have been processed


    Note that all these steps are committed together, along with any other inserts and updates to the target table. This keeps the data warehouse consistent.

    Prerequisites for using this option are the following:

    • The target table must have the ETL_PROC_WID and W_UPDATE_DT system columns.

    • Tables <target>_PE and <target>_DEL must be created with the columns in the interface key.

    • #LAST_ARCHIVE_DATE must be NULL if target table does not have the CREATED_ON_DT column.

    • #SOFT_DELETE_PREPROCESS should be refreshed from Oracle BI Applications Configuration Manager (Configuration Manager) by the load plan component.

  • Date Track. Automatically maintains effective from and to dates in the target table, similarly to a slowly changing dimension. It can handle dates or numbers (usually date keys, for example, YYYYMMDD). This can also set a current flag column, which will be 'Y' for the last record and 'N' for earlier records.

    Prerequisites for using this option are the following:

    • Set the slowly changing dimension behavior for the following table columns in the model:

      - Natural key

      - Starting/ending timestamp

      - Current flag (optional)

    • The natural key and starting timestamp columns should be indexed if they are not covered by the update key index. In the interface, map the effective to date column to a constant maximum value (usually #HI_DATE) set to execute on the target.

    • If using current flag, map it to 'Y' again executing on the target.

    • ETL_PROC_WID should be indexed.

  • Change Capture — Captures target table changes to an image table to streamline the process of reflecting the changes in aggregates.

    Step Action Control

    Truncate change image table

    Clears out image table ready to capture changes in the current process.

    Always runs

    Capture preload changes

    Captures target table records that are about to be updated.

    Always runs

    Capture soft delete changes

    Captures target table records that are about to be marked for soft delete.

    Runs if soft delete feature is enabled

    Capture post load changes

    Captures target table records that have been inserted or updated.

    Always runs

    All these steps are committed together along with any other inserts and updates to the target table. This keeps the data warehouse consistent.

    Prerequisites for using this option are the following:

    • For W_FACT_F, the image table W_FACT_CMG must be created with all the columns of the target table as well as the following columns:




  • Unspecified Record — If the target table is a dimension, set this to TRUE to automatically insert an "Unspecified" record. This is referenced by facts in case no other dimension record matches. The default column values are determined by model naming standards using the user-defined function GET_UNSPEC_VALUE.

Options for Performance Tuning

  • Hints — This IKM allows the passing of hints into the generated SQL. See My Oracle Support document (ID 1963225.1) titled Oracle Business Intelligence Applications Version 11g Performance Recommendations.

  • Alter Session List — Applies a list of alter session commands to the session used by the KM. Commands should be separated by a semi-colon and without the "ALTER SESSION" prefix. Each command should be prefixed SRC or TGT depending on whether it should be executed on the source connection (relevant if using an LKM) or the target connection. For example:

  • Analyze Target — Statistics will be collected on the target table before it is loaded if the KM Option ANALYZE_TARGET is set to True. By default it is set to False.

  • Analyze Flow Table — Statistics will be collected on the flow table (I$) after it is loaded if the KM Option ANALYZE_FLOW_TABLE is set to True. By default it is set to False. This option also affects how the effective dates are calculated in full load if the date track option is enabled. If the flow table is not analyzed then an UPDATE statement is used to set the effective to dates. Otherwise a MERGE statement is used.

  • Bulk Mode (variable #ETL_BULK_MODE) — If enabled, bulk mode will use the direct path write to target (append hint) and bypass the I$ table (if no other KM options requiring it, for example, recycle errors, and date track). The bulk mode variable can be set to:

    Y - Enabled

    F - Enabled for full load only

    N - Disabled