6       SCD Configuration

Overview of SCD Process

SCDs are dimensions that have data that changes slowly, rather than changing on a time-based, regular schedule.

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-bestpractices-datawarehouse-whi-129686.pdf >

·        Oracle Warehouse Builder Data Modeling, ETL, and Data Quality Guide at

 <http://download.oracle.com/docs/cd/E16338_01/owb.112/e10935/dim_objects.htm>

Additional online sources include:

<http://en.wikipedia.org/wiki/Slowly_changing_dimension>

<http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/10g/r2/owb/owb10gr2_gs/owb/lesson3/slowlychangingdimensions.htm>

<http://www.oraclebidwh.com/2008/11/slowly-changing-dimension-scd/>

<http://www.informationweek.com/news/software/bi/showArticle.jhtml?articleID=204800027&pgno=1>

<http://www.informationweek.com/news/software/bi/showArticle.jhtml?articleID=59301280>

You can also refer to The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling by Ralph Kimball and Margy Ross.

The SCD component of the platform is delivered via a C++ executable. The types of SCD handled by the OFSAAI SCD component for OFSAA BI applications installers are Type 1 and Type 2.

Type 1

The Type 1 methodology overwrites old data with new data, and therefore does not track historical data. This is useful for making changes to dimension data.

In this example,

·        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 till which this product record is valid.

·        F_LATEST_RECORD_INDICATOR with value 'Y', which 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 SCD column and if there is a change in the product name to 'Personal Loan' from 'PL' in the above example, in the next processing period, then when SCD is executed for the new processing period the record in the above example changes to:

Type 2

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 above example for the change in product name from 'PL' to 'Personal Loan' if history has to be preserved, then the V_PRODUCT_NAME column has to be set as Type 2 when SCD is processed for the processing period and the change inserts a new record as shown in the following example:

A new record is inserted to the product dimension table with the new product name. The latest record indicator for this is set as 'Y', indicating this is the latest record for the personal loan product. The same flag for the earlier record was set to 'N'.

Prerequisites

The Hierarchy Flattening Transformation should have been executed successfully.

1.     The SCD executable should be present under <installation home>ficdb/bin. The file name is scd and the user executing the SCD component should have execute rights on this file.

2.     The setup tables accessed by SCD component (SETUP_MASTER, SYS_TBL_MASTER, and SYS_STG_JOIN_MASTER) should have the required entries. The SETUP_MASTER table does not come seeded with the installation; the required entries must be added manually. The required columns are mentioned in the Tables Used by the SCD Component. The tables SYS_TBL_MASTER and SYS_STG_JOIN_MASTER are seeded for the Org unit, GL Account, Product, and Common COA (Chart of Accounts) dimensions along with solution installation and you must only add entries in these tables, if you add new dimensions.

3.     Database Views with nameDIM_<Dimension Name>_V come seeded, for the seeded dimensions which come as part of installation. These views source data from the Profitability dimension tables as well as the flattened hierarchy data.

DIM_PRODUCT_V is the view available for the product dimension.

New views will have to be added for any new dimension, added in addition to the seeded dimensions.

Tables Used by the SCD Component

The following are the database tables and columns used by the SCD component:

·        SETUP_MASTER

§       V_COMPONENT_CODE: This column is not used by the OFSEFPA solution. This column acts as a primary key for ALMBI.

§       V_COMPONENT_DESC: This column value is hard coded in the database view definitions for DIM_PRODUCT_V, DIM_GL_ACCOUNT_V, DIM_COMMON_COA_V, and DIM_ORG_UNIT_V to obtain the Hierarchy ID from the REV_HIER_FLATTENED table. For this reason, the value for this column should be unique.

§       V_COMPONENT_VALUE: This is the hierarchy ID to be processed and this can be obtained by executing the following query:

select b.object_definition_id,short_desc,long_desc from fsi_m_object_definition_b b inner join fsi_m_object_definition_tl t on b.object_definition_id = t.object_definition_id and b.id_type = 5

Examples:

·        SYS_TBL_MASTER

The solution installer populates one row per dimension for the seeded dimensions in this table.

Example:

This is the row inserted by the solution installer for the product dimension.

Note: For any newly defined dimension, a row will have to be inserted to this table manually.

·        SYS_STG_JOIN_MASTER

The solution installer populates this table for the seeded dimensions.

The possible values for column type (the COL_TYPE column) in SYS_STG_JOIN_MASTER table are:

PK: Primary Dimension Value (can be the multiple of the given Mapping Reference Number)

SK: Surrogate Key

DA: Dimensional Attribute (may be multiple for a given Mapping Reference Number)

SD: Start Date

ED: End Date

LRI: Latest Record Indicator (Current Flag)

CSK: Current Surrogate Key

PSK: Previous Surrogate Key

SS: Source Key

LUD: Last Updated Date/Time

LUB: Last Updated By

Example:

This is the row inserted by the solution installer for the product dimension.

Note: For any newly defined dimension, the column details will have to be inserted to this table manually.

·        DIM_< dimension name >_V: The database view which SCD uses as the source.

DIM_PRODUCTS_V

These views come as part of install for the dimensions seeded with the application.

Note: For any newly defined dimension, a view will have to be created, which is similar to that of DIM_PRODUCTS_V.

A sequence should be created for every user-defined dimension, using the following query:

create sequence SEQ_< DIMENSION > minvalue 1

maxvalue 999999999999999999999999999

increment by 1

Executing the SCD Component

You can execute the SCD component from the Operations (formerly Information Command Center (ICC) framework) module of OFSAAI.

The SCD component for OFSAA BI applications installers has been seeded with the Batch ID <INFODOM>_SCD, which can be executed from Batch Execution section of OFSAAI.

You can also define a new Batch and an underlying Task definition from the Batch Maintenance window of OFSAAI. For more information on defining a new Batch, see the How to Define a Batch.

To define a new task for a Batch definition:

1.     Select the check box adjacent to the newly created Batch Name in the Batch Maintenance window.

2.     Click Add () from the Task Details window. The Task Definition window is displayed.

3.     Enter the Task ID and Description.

4.     Select Run Executable component from the drop down list.

5.     Select the following from the Dynamic Parameters list:

a.     Datastore Type: Select the appropriate datastore type from the list

b.     Datastore Name: Select the appropriate datastore name from the list

c.     Executable: Enter scd,<map ref num>

 For example, scd,2

Wait: Click Yes if you want to wait till the execution is complete or click No to proceed with the next task.

 Batch Parameter: Click Yes in Batch Parameter field if you want to pass the batch parameters to the executable and click NO otherwise.

6.     Click Save. The Task definition is saved for the selected Batch.

7.     Click Parameters. Select the following from the Dynamic Parameters List and then click Save:

The map ref number values available for the Executable parameter are:

-1, if you want to process all the dimensions. The Executable parameter mentioned earlier is:

 scd,-1.

If you want to process for a single dimension, query the database table SYS_TBL_MASTER and give the number in the MAP_REF_NUM column for the dimension you want to process. These are the ones which come seeded with the install. If you want to process for Product dimension, the Executable parameter mentioned earlier is:

 scd,6.

8.     You can execute a Batch definition from the Batch Execution section of OFSAAI Operations module.

Checking the Execution Status

The Batch execution status can be monitored through Batch Monitor section of OFSAAI Operations module.

The status messages in batch monitor are:

·        N: Not Started

·        O: On Going

·        F: Failure

·        S: Success

The execution log can also be accessed on the application server in the directory $FIC_DB_HOME/log/ficgen, where file name will have the Batch Execution ID.

The detailed SCD component log can be accessed on the application server in the directory $FIC_HOME by accessing the following path /ftpshare/<infodom name>/logs.

Check the .profile file in the installation home if you are unable to find this path.

The Event Log window in Batch Monitor section provides execution logs, in which the top row is the most recent. Any errors during the Batch execution are listed in the logs.