IKM BIAPPS Oracle Slowly Changing Dimension

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.

Prerequisites

Prerequisites for using this IKM are the following:

·        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 following 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:

o   Type 1 key column must be identified by the SCD1 WID flexfield in the model.

o   Type 1 key column should be mapped on source or staging to a constant value, for example, 0.

o   Type 1 key column should be insert only.

o   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

o   Type 1 table (W_DIMENSION_T1_D) should have indexes on: Type 1 key columns and natural key columns.

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

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