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.

Note:

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

Prerequisites

Prerequisites for using this IKM are:
  • 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.

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

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