9       About Dimension Loading Process

This chapter provides information about Dimension Loading Process in the Oracle insurance data foundation application.

Topics:

·        About SCD Component 

·        Populating Data in Dimension Table

·        Tables Used by the SCD Component 

·        Loading Dimension Tables

·        Supplementary Information for Dimension Loading Process

About SCD Component

A Slowly Changing Dimension (SCD) is a dimension that stores and manages both current and historical data over time in a data warehouse. SCDs are dimensions that have data that changes slowly, rather than changing on a time-based, regular schedule. It is considered and implemented as one of the most critical ETL tasks in tracking the history of dimension records. There are three types of SCDs and you can use Warehouse Builder to define, deploy, and load all three types of SCDs.

Topics:

·        Type 1 SCDs - Overwriting

·        Type 2 SCDs - Creating another dimension record

·        Type 3 SCDs - Creating a current value field

Type 1 SCDs - Overwriting

The Type 1 SCD overwrites old data with new data and therefore does not track historical data. This is useful for making changes to dimension data. This is the default type of dimension that is created. You do not need to specify any additional information to create a Type 1 SCD.

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 that 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, when SCD is executed for the new processing period, the record in the above example changes to the following.

Record Change Example:

Type 2 SCDs - Creating another dimension record

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.

Therefore, a Type 2 SCD retains the full history of values. When the value of a chosen attribute changes, the current record is closed. A new record is created with the changed data values and this new record becomes the current record. Each record contains the effective time and expiration time to identify the time period between which the record was active.

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

Type 3 SCDs - Creating a current value field

A Type 3 SCD stores two versions of values for certain selected level attributes. Each record stores the previous value and the current value of the selected attribute.

When the value of any of the selected attributes changes, the current value is stored as the old value and the new value becomes the current value.

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://docs.oracle.com/cd/E11882_01/owb.112/e10935.pdf  

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

An excellent published resource that covers SCD in detail is "The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling" by Ralph Kimball and Margy Ross.

The SCDs used in Data Foundation solutions are listed in the OIDF SCD Technical Metadata file of this release.

Populating Data in Dimension Table

Topics:

·        Prerequisites

Data Foundation solutions use the SCD component to handle dimensional data changes. For more details about the SCD component and SCD process, see the Slowly Changing Dimension (SCD) Process and the Execute the SCD Batch sections respectively.

Prerequisites

·        The SCD executable should be present under <installation home>ficdb/bin. The file name is scd.

·        The user executing the SCD component should have execute rights on the file mentioned as prerequisite in point 2.

·        The setup tables accessed by SCD component are SYS_TBL_MASTER and SYS_STG_JOIN_MASTER.

SYS_TBL_MASTER stores the information like which is the source stage table and the target dimension tables. The source sometimes can be the database views which could be simple or a complex view.

SYS_STG_JOIN_MASTER stores the information like which source column is mapped to which column of a target dimension table. It makes use of data base sequence to populate into surrogate key columns of dimension tables.

Tables Used by the SCD Component

The database tables used by the SCD component are as follows:

·        SYS_TBL_MASTER

·        SYS_STG_JOIN_MASTER

·        DIM_<dimensionname>_V

About SYS_TBL_MASTER Table

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

Sample Data: This is the row put in by the solution installer for the Line of Business dimension.

NOTE:   

For any new dimension added, a row will have to be inserted to this table manually.

About SYS_STG_JOIN_MASTER Table

The solution installer will populate this table for the seeded dimensions. Table for Seeded Dimension:

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

·        PK – Primary Dimension Value (may be multiple for a 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

·        NN – Not Null

Sample Data: This is the row put in by the solution installer for the Line of Business dimension.

Sample Data:

NOTE:   

For any new dimension added, the column details will have to be inserted to this table manually.

About DIM_<dimensionname>_V Table

The database view which SCD uses as the source.

Example:

DIM_BANDS_V

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

NOTE:   

For any new dimension added, a view will have to be created similar to DIM_BANDS_V.

DIM_<dimensionname>: Output table to which SCD writes the dimension data. A sequence must be added for every user-defined dimension.

Example:

create sequence SEQ_DIM_<DIM> minvalue 1

maxvalue 999999999999999999999999999

increment by 1

Loading Dimension Tables

Load data into the Dimension tables using one of the following suitable processes.

Topics:

·        Seeded Data

·        Loading Policy Tables through Insurance SCD

·        Loading Dimension Tables through Data Foundation SCD

·        Loading Key Dimensions using AMHM and Hierarchy Flattening

·        Loading Data from STG_INTF Tables to DIM_INTF Tables

·        Executing the Loading Procedure using Batch Framework

·        Executing the Hierarchy Flattening Procedure using Batch Framework

·        Checking the SCD Batch Execution Status

Seeded Data

This is an automatic process wherein the Seeded data for Dimensions, which are packaged within the installer, are populated during the installation.

NOTE:   

To access the Seeded data for Dimensions, see the latest version of the Oracle Insurance Data Foundation Application Pack Run Chart.

Loading Policy Tables through Insurance SCD

To execute Policy Dimension SCD batch <INFODOM>_INSURANCE_SCD, see Execute the SCD Batch of the DIM_POLICY table.

Loading Dimension Tables through Data Foundation SCD

To execute an SCD batch <INFODOM>_DATA_FOUNDATION_SCD for any other Dimension table, see Execute the <INFODOM>_DATA_FOUNDATION_SCD batch for the required Dimension table.

NOTE:   

For more comprehensive coverage of configuration and execution of a batch, see Oracle Financial Services Advanced Analytical Applications Infrastructure User Guide Release 8.1.1.0.0.

Loading Key Dimensions using AMHM and Hierarchy Flattening

The Dimension Loader functionality in the Data Foundation application enables you to load Dimension tables such as DIM_ORG_UNIT, DIM_GL_ACCOUNT, DIM_COMMON_COA, DIM_PRODUCT, and DIM_ORG_STRUCTURE.

For more information about loading the Dimensions using AMHM, see the Dimension Management section in Oracle Financial Services Advanced Analytical Applications Infrastructure User Guide Release 8.1.1.0.0 and Dimension Load Procedure section in the Oracle Financial Services Analytical Applications Data Model Utilities User Guide.

Loading Data from STG_INTF Tables to DIM_INTF Tables

The following tables are a part of the Staging. The Dimension data is stored in the following set of tables.

For more information about viewing the structure of staging tables, see the Oracle Financial Services Analytical Applications (OFSAA) Data Model Document Generation Release 8.1.x.

Staging tables are present for all key dimensions that are configured within the OFSAAI framework. The Dimension IDs used for key dimensions are as follows.

Executing the Loading Procedure using Batch Framework

The batch for Key Dimension Loader can be executed by executing the task (Task for Loading Data from DRM) present in the seeded batch <infodom>_DATA_FOUNDATION_SCD.

Below are the input parameters:

·        pDIMENSIONID: This is the dimension ID.

·        pSynchFlag: This parameter is used to identify if a complete synchronization of data between staging and fusion table is required. The default value is 'Y'.

For more information, see the Dimension Loaders section in the Data Loaders section in the Oracle Financial Services Analytical Applications Data Model Utilities User Guide.

Executing The Hierarchy Flattening Procedure using Batch Framework

The batch for Dimension Hierarchy flattening can be executed by executing the task (DT for DRM Dimension Hierarchy Flattening) present in the seeded batch <infodom>_DATA_FOUNDATION_SCD.

Below are the input parameters:

·        pDIMENSIONID: This is the dimension ID.

·        pHierarchyId: Enter the Hierarchy ID. If all the hierarchies belonging to a dimension are to be processed, then provide NULL as the parameter value. Else, provide the System Identifier of the hierarchy that must be transformed.

For more details, see the Hierarchy Transformation section in the Data Loaders section the Oracle Financial Services Analytical Applications Data Model Utilities User Guide.

Checking the SCD Batch Execution Status

To check the SCD batch execution status, see Verify Log Files and Check Error Messages if any.

Supplementary Information for Dimension Loading Process

The following are the supplementary information required for the Dimension loading process.

Topics:

·        Improving SCD Performance

·        Handling Multiple GAAP Codes for the Same Account Number for the Same MIS Date in SCD

·        Handling Multiple GAAP Codes for the Same Account Number for the Same MIS Date in the Function

Improving SCD Performance

SCD performance can be improved by providing hints and session alter statements. This requires the presence of the following four columns in SYS_TBL_MASTER:

·        merge_hint

·        select_hint

·        session_enable_statement

·        session_disable_statement

These columns are present in the OFSAAI versions 7.3.2.4.0 and above. If these have to be used in OFSAAI versions 7.3.2.2.0 or 7.3.2.3.0, execute the following SQL queries:

ALTER TABLE SYS_TBL_MASTER ADD MERGE_HINT VARCHAR2(255)

/

ALTER TABLE SYS_TBL_MASTER ADD SELECT_HINT VARCHAR2(255)

/

ALTER TABLE SYS_TBL_MASTER ADD SESSION_ENABLE_STATEMENT VARCHAR2(255)

/

ALTER TABLE SYS_TBL_MASTER ADD SESSION_DISABLE_STATEMENT VARCHAR2(255)

/

During the upgrade to OFSAAI 7.3.2.4.0, ensure to backup SYS_TBL_MASTER table and to drop the preceding four columns, if these scripts are executed in any of the OFSAAI versions prior to 7.3.2.4.0. Otherwise, an upgrade to OFSAAI 7.3.2.4.0 may throw an error, since the columns exist.

For improving performance, hints for the MERGE query, which is generated internally by the SCD, can be provided under MERGE_HINT. The following session alters can be mentioned in the SESSION_ENABLE_STATEMENT and SESSION_DISABLE_STATEMENT columns.

1.     SESSION_ENABLE_STATEMENTs are executed before the MERGE in the SCD and SESSION_DISABLE_STATEMENTs are executed after the SCD MERGE.

2.     Since all the tasks under the SCD batch for DIM_ACCOUNT works on the same target, the SESSION_DISABLE_STATEMENTs in SYS_TBL_MASTER cannot be provided when tasks are executed. In this case, there can be a separate SQL file to contain all the SESSION_DISABLE_STATEMENTs to be executed once after all the tasks in the SCD are done. The SESSION_DISABLE_STATEMENT will hold a null in the SYS_TBL_MASTER table.

3.     SESSION_ENABLE_STATEMENTs are required to be mentioned only for the first task in the batch. Here the target is the same for all the tasks under a batch. In case any of the tasks are to be executed separately, then the SESSION_ENABLE_STATEMENTs should be mentioned for any one of the tasks which is included in the batch for the execution.

Example

MERGE_HINT and SESSION_ENABLE_STATEMENT in SYS_TBL_MASTER

4.     Execute all the tasks in parallel. This may cause N_RCV_LEG_ACCT_SKEY to have an incremental value as compared to N_ACCT_SKEY.

5.     Execute the SQL file with all the SESSION_DISABLE_STATEMENTs, after the successful completion of the SCD batch.

6.     After the DIM_ACCOUNT table is populated using this approach, you cannot use the initial approach (FN_POPDIMACCOUNT) as this will lead to SKey conflict.

7.     Ensure that you have set the value of the sequence SEQ_DIM_ACCOUNT_SCD as max (value of SKey in DIM_ACCOUNT) +1, before moving from old to a new approach.

8.     The F_LATEST_RECORD_INDICATOR for an existing DIM_ACCOUNT data already loaded by the function must be updated to 'Y' before running the SCD, failing which a new SKey may get generated for the same account number.

9.     SCD execution occurs based on the GAAP code, which is configured in the SETUP_MASTER table. These are introduced to tackle the scenario of multiple GAAP codes. Whether or not there exist multiple GAAP codes, SETUP_MASTER must be manually configured as follows:

Where V_COMPONENT_VALUE must be manually populated with the required GAAP code. For all other GAAP codes, ensure to update SETUP_MASTER manually before running DIM_ACCOUNT SCD.

Handling Multiple GAAP Codes for the Same Account Number for the Same MIS Date in SCD

NOTE:   

For illustration, Account Dimension is considered.

When multiple GAAP codes exist for the same account number for the same MIS date, configure the SETUP_MASTER table manually as mentioned in the preceding section:

·        V_COMPONENT_VALUE will hold the GAAP code for which the SCD must be executed.

·        If there are different GAAP codes for two distinct account numbers for the same MIS date, then the SCD has to be executed for each GAAP code by changing the V_COMPONENT_VALUE manually in SETUP_MASTER table. The SETUP_MASTER table should have only one record WHERE V_COMPONENT_DESC = 'DEFAULT_GAAP'.

Handling Multiple GAAP Codes for the Same Account Number for the Same MIS Date in the Function

NOTE:   

For illustration, Account Dimension is considered.

For the FN_POPDIMACCOUNT function, you have to create views and use these views instead of the tables in the FSI_DIM_ACCOUNT_SETUP_DETAILS table. For Product Processors having GAAP code as part of the Primary Key, create a view on the table with a filter on the GAAP code as:

·        where V_GAAP_CODE = (SELECT V_COMPONENT_VALUE FROM SETUP_MASTER WHERE V_COMPONENT_DESC = 'DEFAULT_GAAP')

·        Use this view under TABLE_NAME in the FSI_DIM_ACCOUNT_SETUP_DETAILS table. If there are different GAAP codes for two distinct account numbers for the same MIS date, then the function has to be executed for each GAAP code by changing the V_COMPONENT_VALUE manually in SETUP_MASTER table. The SETUP_MASTER table should have only one record WHERE V_COMPONENT_DESC ='DEFAULT_GAAP'.

NOTE:   

If STG_OPTION_CONTRACTS is loaded for two MIS dates, and FN_POPDIMACCOUNT is executed, then all records irrespective of the MIS date will get loaded. To resolve this, remove the expression under SQL_TEXT in FSI_DIM_ACCOUNT_SETUP_DETAILS for STG_OPTION_CONTRACTS and use the same expression to create a view and use this view as the TABLE_NAME in FSI_DIM_ACCOUNT_SETUP_DETAILS.