4.3.1 Overview of SCD Process

SCDs are used to maintain the history of dimension-member changes over time. SCD is a required process and is tied into the BI application. Without this process, the updated information will not be reflected in ALMBI. For example, if the Active Time Bucket Definition was changed for an ALM Process Execution, the SCD process is required to reflect the new Active Time Bucket details into the Result Area. It is mandatory to run the SCD process if the hierarchies have changed.

For more information on SCDs, see:

• Oracle Data Integrator Best Practices for a Data Warehouse at:

  http://www.oracle.com/technetwork/middleware/data-integrator/overview/odi-best practices-datawarehouse-whi-129686.pdf

• Oracle Warehouse Builder Data Modeling, ETL, and Data Quality Guide, 11g Release 2 (11.2), Part #E10935-03 at:

  http://docs.oracle.com/cd/E18283_01/owb.112/e10935/dim_objects.htm

The SCD component is delivered through an executable. For the ALMBI solution, the types of SCD supported are Type 1 and Type 2.

1. Type 1 SCD Methodology

   The Type 1 Methodology overwrites old data with new data, and therefore does not track changes to the data across time.

   Example:

   Consider a Dimension table, DIM_PRODUCT:

   In this example:

   Table 4-1 Dimension table

   N_PRODUCT_SKEY	V_PRODUCT_NAME	D_START_DATE	D_END_DATE	F_LATEST_RECORD_INDICATOR
   1	Personal Loan	5/31/2010	12/31/9999	Y

   • N_PRODUCT_SKEY is the surrogate key column which is a unique key for each record in the dimension table.
   • V_PRODUCT_NAME is the product name.
   • D_START_DATE indicates the date from which this product record is valid.
   • D_END_DATE indicates the date to which this product record is valid.
   • F_LATEST_RECORD_INDICATOR: A value Y indicates this is the latest record in the dimension table for this product and N indicates it is not. If the V_PRODUCT_NAME column is set as a Type 1 and if there is a change in the product name to Personal Loan from PL in the earlier example in the next processing period, then the record changes as shown in the following table:

   Table 4-2 Record Changes

   N_PRODUCT_SKEY	V_PRODUCT_NAME	D_START_DATE	D_END_DATE	F_LATEST_RECORD_INDICATOR
   1	Personal Loan	6/30/2010	12/31/9999	Y

2. Type 2 SCD Methodology

   The Type 2 method tracks historical data by creating multiple records for a given natural key in the dimensional tables with separate surrogate keys. With Type 2, the historical changes in dimensional data are preserved. In the earlier example, for the change in product name from PL to Personal Loan if history will be preserved then the V_PRODUCT_NAME column must be set as Type 2 in which case when SCD is processed for the processing period in which the change happens it will insert a new record as shown in the following example:

Table 4-3 Change from PL to Personal Loan

N_PRODUCT_SKEY	V_PRODUCT_NAME	D_START_DATE
1	Personal Loan	5/31/2010
1	Personal Loan	6/30/2010

A new record is inserted to the Product Dimension table with the new product name and the latest record indicator for this is set as 'Y' indicating this is the latest record for the personal loan product and the same flag for the earlier record is set to 'N'.