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.
Prerequisites
Prerequisites for using this IKM are the following:
·        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.
o   Uses #LAST_ARCHIVE_DATE to filter target by CREATED_ON_DT system column (set variable to NULL to disable this).
o   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_FEATURE_ENABLED
#SOFT_DELETE_PREPROCESS
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.
#SOFT_DELETE_FEATURE_ENABLED
Truncate delete table
Removes records from the delete table once they have been processed
#SOFT_DELETE_FEATURE_ENABLED

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:
o   The target table must have the ETL_PROC_WID and W_UPDATE_DT system columns.
o   Tables <target>_PE and <target>_DEL must be created with the columns in the interface key.
o   #LAST_ARCHIVE_DATE must be NULL if target table does not have the CREATED_ON_DT column.
o   #SOFT_DELETE_PREPROCESS should be refreshed from Oracle BI Applications 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:
o   Set the slowly changing dimension behavior for the following table columns in the model:
- Natural key
- Starting/ending timestamp
- Current flag (optional)
o   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.
o   If using current flag, map it to 'Y' again executing on the target.
o   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:
o   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:
CHANGED_IN_TASK
PHASE_CODE
PHASE_MULTIPLIER
·        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. For more information, see the article titled "Oracle Business Intelligence Applications Version 11.1.1.7.1 Performance Recommendations (Doc ID 1539322.1)" on My Oracle Support.
·        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:
·        SRC set TRACEFILE_IDENTIFIER='ODI_TRACE'; SRC set events '10046 TRACE NAME CONTEXT FOREVER, LEVEL 8'; TGT set TRACEFILE_IDENTIFIER='ODI_TRACE_TGT';
·        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, flow control, recycle errors, and date track). The bulk mode variable can be set to:
Y - Enabled
F - Enabled for full load only
N - Disabled

No comments:

Post a Comment