IKM Oracle Slowly Changing Dimension

DESCRIPTION   

1.   Management of TYPE 2 Slowly Changing Dimensions on Oracle.

2.   Data can be controlled. Invalid data is isolated in the Error Table and can be recycled.

FEATURES

1.   If TYPE2_FLG variable is set then new records will be created if  there is a change to any slowly changing (type 2) attributes

2.   If UPDATE_ALL_HISTORY variable is set then any overwrite attributes that are not flagged as system columns will be updated to match  the value on the current record

3.   If OBI_SCD1_WID option is set then a reference table will be automatically maintained with the natural key and SCD1 Key (plus any additional attributes flagged with UD1 checkbox)

REQUIREMENTS
When defining the Target Datastore, it is necessary to set additional information in the SCD Behaviour list box and in the column flexfields. This controls the processing logic for the dimension when different options are used.

In the SCD Behavior list box (found on Description of each column concerned):

1.   Surrogate Key    Mapped on TARGET to next sequence value     Usually ROW_WID

2.   Natural Key        Column(s) representing the Natural Key  Usually INTEGRATION_ID, DATASOURCE_NUM_ID

3.   Current Record Flag    Column holding Y for last record and N for others  Mapped on TARGET to Y and set correctly in processing  Usually CURRENT_FLG

4.   Start Timestamp    Record effective start date. Usually EFFECTIVE_FROM_DT

5.   End Timestamp    Record effective end date. Usually EFFECTIVE_TO_DT

6.   Insert Row        Slowly Changing (or Type 2) Attributes   A new record will be inserted if there is a change to  any of these attributes on the current record

7.   Overwrite Column    All other columns should be overwrite

In the column flexfield, set the following additional information

1.    OBI SCD1 Wid    Set to Y for type 1 surrogate key (SCD1_WID)

            This will be at the same grain as the natural key

2.    OBI System Column    Set to Y for Overwrite Columns (see SCD Behaviour) that should not be updated with the value  from the current record (see Update All History)

3.    OBI Change Column    Set to Y for columns to compare when determining whether a change has occurred. Usually changed on dates.

In the interface there are a couple of additional factors worth mentioning:

1.    Key        Should be natural key plus source start timestamp (usually INTEGRATION_ID, DATASOURCE, SRC_EFF_FROM_DT)

2.    SCD1 Key        If using OBI_SCD1_WID option this column should be insert only

3.    Insert/Update    Even if the SCD Behaviour is overwrite the column   will only be overwritten if it is updateable

4.    UD1 checkbox    If using OBI_SCD1_WID option, additional attributes  can be maintained on the reference table

5.    Source Start    Map to start of time if not available

6.    Source End        Map to end of time if not available

7.    Start Timestamp    Map to source start

8.    End Timestamp    Map to end of time

9.    Current Flag    Map to Y

SPECIFIC OPTIONS (Refer to Option descriptions for more information on each option)

1.   OBI_FULL_HISTORY    Optimizes initial load for bringing in history   Requires unique index on staging table

2.    OBI_SCD1_WID Whether to maintain OBI SCD1 Wid column  Requires reference table

LIMITATIONS

1.   Make sure to map ALL target table columns flagged as: "Surrogate Key", "Natural Key", "Current Record Flag" and "End Timestamp". Notice that

      mappings set for the "Current Record Flag" and "End Timestamp" columns are not used.

2.   As this Knowledge Module is dedicated to Datawarehouse Projects (where data       should not be removed), when using it with a journalized Source Datastore,       it is not possible to synchronize deletions. Therefore, data should be manually  filtered to exclude any source delete events (add a filter on JRN_FLAG = 'I').

3.   The FLOW_CONTROL and STATIC_CONTROL options call the Check Knowledge Module to isolate invalid data (if no CKM is set, an error occurs). Both options must be set to NO in the case when an Integration Interface populates a TEMPORARY target datastore. I

If the Table Primary Key is designated by the same column as the Surrogate Key, then:

1.     On the "Diagram" tab of  the  Interface, uncheck the "Check Not Null" checkbox for the column representing the Primary Key.

2.    On the "Control" tab of the Interface and in the "Constraint" panel, set  the option value to NO for the Primary Key control.