This option integrates data into an Oracle target table modeled as a Type 2 slowly changing dimension. New records are inserted, and changes to existing records can either trigger an insert or an update depending on the whether there is a change to any of the Type 2 columns.
Data can be controlled by isolating invalid data in the error table and recycling when fixed. This option uses the variables #TYPE2_FLG and #UPDATE_ALL_HISTORY to control behavior.
Note:
This IKM now supports ETL diagnostics and automatic corrections. The flow control option is no longer used with this IKM.Prerequisites
Update key must be defined in the interface and the key columns should be indexed (usually INTEGRATION_ID, DATASOURCE_NUM_ID, SRC_EFF_FROM_DT).Slowly changing dimension behavior must be set for all target table columns (set in model) and must include the following:
- Surrogate key (usually ROW_WID)
- Natural key (usually INTEGRATION_ID, DATASOURCE_NUM_ID)
- Start and end timestamps (usually EFFECTIVE_FROM_DT and EFFECTIVE_TO_DT)
- Current flag (usually CURRENT_FLG)
End timestamp should be mapped to the maximum value (usually #HI_DATE).
Current flag should be mapped to Y.
Source from and to dates should be Not Null (default to #LOW_DATE or #HI_DATE).
ETL_PROC_WID should be indexed.
Column Classification
The table describes how dimension columns should be categorized and the different behaviors for each classification. The categorization is done on individual table columns in the model.
Column | Description | SCD Behavior | Other Flexfields |
---|---|---|---|
Surrogate key |
Warehouse generated primary key for dimension |
Surrogate key |
|
Natural key |
Business or source key, unique in combination with time |
Natural key |
|
Start timestamp |
Time record is effective from |
Start timestamp |
|
End timestamp |
Time record is effective to |
End timestamp |
|
Current flag |
Whether the record is the latest effective |
Current flag |
|
Type 2 columns |
Creates new version of record (insert) if there is a change to any of the Type 2 columns |
Insert on change |
|
Update history columns |
Always set to the value from the current record |
Overwrite on change |
Not a system column |
Other system columns |
Maintained as insert/update |
Overwrite on change |
System column |
Change columns |
Record is rejected if there is no change to any of these columns |
Overwrite on change |
Change column and system column |
SCD1 key |
Warehouse generated key corresponding to the natural key |
Overwrite on change |
SCD1 WID (column flexfield) |
Slowly Changing Dimension Features
Type 2 Changes — If the variable #TYPE2_FLG is turned off (set to 'N') then the dimension behaves as a Type 1 dimension. The natural key must be unique (no history allowed) because no maintenance of the start/end dates is performed. With #TYPE2_FLG on (set to 'Y') new records will be inserted; changes that update at least one Type 2 column will also trigger an insert, subject to some restrictions, and any other change will update the existing record.
Type 2 changes are triggered as follows:
- The incoming record must have at least one type 2 column different when compared to the current dimension record.
- The new Type 2 record start timestamp is calculated as follows: If there is a change column with a non-null date value, then use that (the Oracle BI Applications standard is to use CHANGED_ON_DT as the change column); otherwise, use the current timestamp (sysdate)
Update All History — With #TYPE2_FLG and #UPDATE_ALL_HISTORY both on (set to 'Y'), then any update history columns will be updated with the value from the current version of the record (latest record with the same natural key).
Options for Functionality
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.
SCD1 Key — Set this to TRUE to automatically maintain a surrogate natural key or Type 1 key. This is managed using a Type 1 table named according to a standard pattern. If the dimension table is W_DIMENSION_D, then the Type 1 table will be W_DIMENSION_T1_D. The sequence that generates the Type 1 key is also named according to a standard pattern. Continuing the example, it would be named W_DIMENSION_D_S1W. Additional columns that are at the same grain (also Type 1) can be automatically maintained on the Type 1 table if they are marked with the UD1 flag.
Prerequisites for W_DIMENSION_D are as follows:
Type 1 key column must be identified by the SCD1 WID flexfield in the model.
Type 1 key column should be mapped on source or staging to a constant value, for example, 0.
Type 1 key column should be insert only.
Type 1 table (W_DIMENSION_T1_D) must have at least the following columns:
- Type 1 key column
- Natural key columns
- System columns W_INSERT_DT, W_UPDATE_DT and ETL_PROC_WID
- Any columns marked as UD1
Type 1 table (W_DIMENSION_T1_D) should have indexes on: Type 1 key columns and natural key columns.
Type 1 Key sequence should be created (W_DIMENSION_D_S1W). Type 1 Key sequence should be created (W_DIMENSION_D_S1W) .
Fill Gaps — Set this to TRUE to automatically extend the first records to cover any earlier date.
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.
|
#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:
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 Configuration Manager by the load plan component.
Options for Performance Tuning
Detection Strategy — To avoid updating the table if no changes have occurred, the incoming data is compared with the existing record on a set of change columns. These are either defined in the model column flexfield (OBI_CHANGE_COLUMN), or otherwise all columns are compared. To always process the changes, this option can be disabled.
Hints and Full History — 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.
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.