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
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.
·
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.
·
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