4       Dimension Population

This chapter describes the steps required to set up and execute the SCD process.

Topics:

·        Overview of Dimension Population 

·        Hierarchy Flattening 

·        Dimension Table Population 

·        DIM_RUN Population 

·        Time Dimension Population 

Overview of Dimension Population

In OFSAA, Hierarchies are defined and managed through the common infrastructure, Dimension Management User Interface. Before use in ALMBI, the related parent or child hierarchy data must first be converted to a flattened and level-based format. The dimension population process involves both the hierarchy flattening process and movement of the dimension data from processing dimension tables to the common reporting dimension tables, shared by all the OFSAA BI applications.

Description of Dimension Population follows

Hierarchy Flattening

Topics:

·        Overview of Hierarchy Flattening Process 

·        Prerequisites and Troubleshooting 

·        Tables Used by the Hierarchy Flattening Process 

·        Executing the Hierarchy Flattening Process 

·        Checking the Execution Status 

Overview of Hierarchy Flattening Process

The Hierarchies are maintained in the Dimension Management component of OFSAA Infrastructure. (In the Common Object Maintenance menu, navigate to Master Maintenance and select Dimension Management, and then select Hierarchies).

The Hierarchy Flattening process is used to move hierarchy data from the parent-child storage data structure to a level based storage data structure. In the Hierarchy Dimension Population Management model, hierarchy data for any hierarchies created on seeded or user-defined dimensions is stored within dimension specific hierarchy tables for the respective dimensions. The Hierarchy Flattening process copies this data to the REV_HIER_FLATTENED table in the BI data model after flattering is completed.

For example:

The hierarchy data of one or more Product Hierarchies created on the Product dimension (a seeded dimension) is stored in the DIM_PRODUCTS_HIER table.

Similarly, assuming there is a user-defined dimension (for example, Legal Entity) and a hierarchy has been defined on this dimension, then the hierarchy data is stored in the DIM_LE_HIER table (assuming this is the hierarchy table created for this hierarchy).

The hierarchy data in the preceding example is moved to REV_HIER_FLATTENED in the BI data model by the hierarchy flattening process.

Database components used by this transformation are:

·        REV_BATCHHIERFLATTEN – Oracle database function.

·        REV_HIER_TRANSFORMATON_BIAPPS – Oracle database Package called by the preceding function.

Some of the features of the Hierarchy Flattening process are:

·        You have the choice to process a single hierarchy or all hierarchies belonging to a particular dimension as part of a single execution.

·        Any change made to the hierarchy using the Hierarchy Management maintenance window changes the flag FLATTENED_ROWS_COMPLETION_CODE in REV_HIER_DEFINITIONS to Pending. This improves processing efficiency as the Flattening process will avoid hierarchies that have not been modified.

Prerequisites and Troubleshooting

The following are the pre-requisites and troubleshooting steps:

1.     All the post-install steps mentioned in the Oracle Financial Services Analytical Applications Infrastructure (OFSAAI) Installation and Configuration Guide and the Oracle Financial Services Asset Liability Management Installation and Configuration guide. ALM Analytics have to be completed successfully.

2.     The Hierarchies are maintained in the Dimension Management component of OFSAA Infrastructure. (In the Common Object Maintenance, navigate to Master Maintenance and select Dimension Management, and then select Hierarchies).

3.     The steps mentioned subsequently in this section are debugging steps and must be checked only if the hierarchy flattening process has failed. Seeded Hierarchies which are included with the installation and any hierarchies created using the Dimension Management user interface will have the proper data in the following section Tables Used by the Hierarchy Flattening Process.

§       Check in the database (atomic schema) to confirm the FLATTENED_ROWS_COMPLETION_CODE column in REV_HIER_DEFINITIONS table has the value Pending for the Hierarchy ID being processed. This column will have the value Pending for any new hierarchy created or modified using the OFSAAI Hierarchy management UI.

§       Check if the REV_DIMENSIONS_B table has a row for the dimension that is being processed. (Use a database SQL query to check. This is available in the section Executing the Hierarchy Flattening Transformation).

§       Check if the REV_HIERARCHIES table has a row for the hierarchy ID that is being processed. (Use a database SQL query to check. This is available in the section Executing the Hierarchy Flattening Transformation).

4.     Application users must be mapped to a role which has the seeded batch execution function (BATPRO)

§       By default, this SMS function is mapped to the SMS Role Data Centre Manager (SYSOPC).

§       The ALM Application seeds three user-profiles:

—    ALM Administration

—    ALM Analyst

—    ALM Auditor

After installation of ALM, the system administrator should additionally map the BATPRO function with the required ALM roles.

5.     Before executing a batch, check if the following services are running on the application server:

§       Iccserver

§       Router

§       AM

§       Messageserver

For more information on how to check if the services are up and how to start the services if you find them not running, see the Oracle Financial Services Analytical Applications Infrastructure User Guide.

6.     Users must create Batch Processes for executing flattening and movement procedures. This process is explained in the section Executing the Hierarchy Flattening Transformation.

§       The flattening procedure takes Dimension ID and Hierarchy sys ID as additional parameters; Dimension ID is mandatory whereas Hierarchy ID is optional.

§       These processes can also be run using the Simplified Batch window, which allows for the execution of stored procedures.

Tables Used by the Hierarchy Flattening Process

The following are the tables used by the hierarchy flattening process:

·        REV_HIERARCHIES: This is the master table for hierarchies with one row per hierarchy.

·        REV_DIMENSIONS_B: This is the master table for dimensions with one row per dimension.

·        REV_HIER_DEFINITIONS: The FLATTENED_ROWS_COMPLETION_CODE column is checked to determine if the hierarchy can be processed.

·        DIM_<DIMENSIONNAME>_HIER: This table stores the parent or child hierarchy data and is the source for the transformation. For example, DIM_PRODUCTS_HIER.

·        REV_HIER_FLATTENED: This is the output table for the transformation into which the flattened hierarchy data gets populated.

Executing the Hierarchy Flattening Process

You can execute the function from the Operations framework module of OFSAAI.

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

To define a new task for a Batch definition, follow these steps:

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

2.     Click Add + from the Task Details grid. The Task Definition window is displayed.

3.     Enter the Task ID and Description.

4.     Select Transform Data from the Transform Data drop-down list.

5.     Select the following from the Dynamic Parameters drop-down list:

§       Datastore Type:  Select the appropriate datastore type from the drop-down list:

§       Datastore Name: Select the appropriate datastore name from the drop-down list.

§       IP address: Select the IP address from the drop-down list.

§       Rule Name: Select BATCH_HIERTRANSFORMATION from the drop-down list of available transformations. (This is a seeded Data Transformation procedure installed as part of the ALMBI application. If you do not see this procedure in the list, contact Oracle support).

§       Parameter List: These are comma-separated values of Dimension ID and Hierarchy ID. Following are the available Dimension ID values and Hierarchy ID values:

Dimension ID Values

ORG_UNIT_ID = 1

GL_ACCOUNT_ID = 2

COMMON_COA_ID = 3

PRODUCT_ID = 4

6.     If you are using a user-defined Dimension, execute the following query in the database to find the value, and use the value in the Dimension ID column to process the dimension name and description:

SELECT B.DIMENSION_ID, T.DIMENSION_NAME, T.DESCRIPTION FROM

REV_DIMENSIONS_B B INNER JOIN REV_DIMENSIONS_TL T ON

B.DIMENSION_ID = T.DIMENSION_ID AND T.DIMENSION_NAME LIKE '<DIMENSION NAME>'

7.     Replace the tag <DIMENSION NAME> in this query with the Dimension Name you find in the UI (Navigate to OFS ALM Home page, select Common Object Maintenance, select Master Maintenance, and then select Dimension Management). This is the Dimension on which the Hierarchy you want to flatten is configured. You must create separate Batches for each Dimension.

If all the Hierarchies belonging to a Dimension are to be processed, then provide null (in lower case) as the parameter value. Otherwise, provide the System Identifier of the Hierarchy that needs to be transformed.

For example, you can find the Hierarchy ID through the Hierarchy user interface at the bottom of the window, as depicted in the following figure.

Description of Audit Trail follows

You can also execute the following query to find the unique system identifier for a specific Hierarchy:

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 =<ID_TYPE>

Use the value in the HIERARCHY_ID column as the parameter for the hierarchy to be processed.

<ID_TYPE> represents the dimension number to which a particular hierarchy belongs.

For example, if all the Hierarchies for the GL Account Dimension must be processed, the parameter list should be given as follows:

'2', null, where '2' is the Dimension ID for the seeded Dimension GL Account.

If a particular Hierarchy with code 1000018112 needs to be processed, the parameter list should be given as follows:

'2', '1000018112'

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

9.     Execute the Batch.

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

Hierarchy transformation can also be directly executed on the database through SQLPLUS.

·        Function Name: REV_BATCHHIERFLATTEN

·        Parameters: BATCH_RUN_ID, MIS_DATE, PDIMENSIONID, and PHIERARCHYID.

·        Sample Parameter Values:'Batch1','20091231','2', and '1000018112'.

The first paragraph should contain the command overview or a short description of the reference information.

Checking the Execution Status

The status of execution can be monitored using the Batch Monitor window of the OFSAAI Operations module.

The status messages in Batch Monitor are:

·        N: Not Started

·        O: On Going

·        F: Failure

·        S: Success

The Event Log window in Batch Monitor provides logs for execution with the top row being the most recent. If there is any error during execution, it will get listed here. Even if you see Successful as the status in Batch Monitor it is advisable to go through the Event Log and re-check if there are any errors.

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

The database-level operations log can be accessed by querying the FSI_MESSAGE_LOG table. The Batch Run ID column can be filtered for identifying the relevant log. (This is the same log you see in the Event Log window.)

Check the .profile file in the Installation Home if you are unable to navigate to these locations.

Dimension Table Population

The dimension table population process serves two purposes:

·        Move flattened hierarchy data from operational tables to the BI Tables.

·        Execute the SCD process against each processed dimension.

Dimension table population should be run after the initial creation of a hierarchy and after any changes are made to a hierarchy Dimensional data changes are handled in the ALMBI solution using the SCD component.

Topics:

·        Overview of SCD Process 

·        Prerequisites 

·        Tables Used by the SCD Component 

·        Executing the SCD Component 

·        Checking the Execution Status 

·        List of Dimension Tables 

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:

·        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:

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:

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

Prerequisites

The following are the prerequisites:

·        The hierarchy flattening process has been run.

·        The setup tables accessed by the SCD component, including SETUP_MASTER, SYS_TBL_MASTER, and SYS_STG_JOIN_MASTER have the required entries.

·        Having entries in the table SETUP_MASTER is optional. By default, SCD maintains only a history of changes to all the members within a dimension, without the context of any hierarchy. If instead, you wish to maintain the history of changes for a specific hierarchy, the SETUP_MASTER table can be used for this purpose.

·        This is achieved by specifying the sys-id of the required hierarchies, in table SETUP_MASTER. This table is referenced during SCD execution and if a hierarchy ID is found, it would be included during the SCD process.

·        The column V_COMPONENT_DESC is used to identify the dimension-type and V_COMPONENT_VALUE for the hierarchy sys-ID.

·        The permissible values for the V_COMPONENT_DESC are listed in the following table:

·        Separate rows in this table are seeded for different hierarchy sys-ID's, one-row corresponding to each of the four dimensions, that is PRODUCT, ORG UNIT, COMMON COA, and GL ACCOUNT. Add entries in this table only if you add a user-defined dimension.

·        The tables SYS_TBL_MASTER and SYS_STG_JOIN_MASTER are seeded for the Org unit, GL Account, Product, and Common COA dimensions. Add entries in these tables only if you add user-defined dimensions.

·        Database Views with the name DIM_<Dimension Name>_V are seeded along with the seeded dimensions during the ALMBI installation. These views present data from the dimension tables as well as the flattened hierarchy data. For example, DIM_PRODUCT_V in usable format. New views should be included for any new dimensions defined.

Tables Used by the SCD Component

These tables are described in the following sections:

SETUP_MASTER

Rows for each of the four key dimensions PRODUCT, ORG UNIT, COMMON COA, and GL ACCOUNT will be seeded into this table during the ALMBI Installation.

The table structure is as follows:

·        V_COMPONENT_CODE: This column acts as a primary key.

·        V_COMPONENT_DESC: This column contains a standard value used within the database view for a flattened hierarchy.

·        V_COMPONENT_VALUE: This column contains the unique hierarchy identifier for the reporting hierarchies to be used in ALMBI.

Hierarchy unique identifiers 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_tlt on b.object_definition_id = t.object_definition_id and b.id_type = 5 and b.leaf_num_id = <dimension_id>; <dimension_id>

represents the dimension number to which a particular hierarchy belongs.

Alternatively, the unique system identifier for each hierarchy can be found at the bottom of the Hierarchy Management page while in EDIT mode.

The following rows are seeded into the SETUP_MASTER table, exactly as follows, with the exception of V_COMPONENT_VALUE. This value should reflect the unique system identifier of the Reporting Hierarchy for each dimension.

 SYS_TBL_MASTER

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

Example: The following data is inserted by the application installer for the product dimension.

No changes are required to this table if the standard key dimensions are being used within ALMBI. If any new dimensions have been added (for example, ALM_COA_ID) a row will have to be inserted to this table manually.

SYS_STG_JOIN_MASTER

The ALMBI application installer populates this table for the seeded dimensions.

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

·        PK: Primary Dimension Value (maybe multiple for a given "Mapping Reference Number")

·        SK: Surrogate Key

·        DA: Dimensional Attribute (maybe 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 or Time

·        LUB: Last Updated By

Example: The following data is inserted by the application installer for the Product Dimension.

No changes are required to this table if the standard key dimensions are being used within ALMBI. If any new dimensions have been added (for example, ALM_COA_ID), a row must be inserted to this table manually.

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

Example:

Dim_products_V

These views come as part of the application installation.

For any new dimension added, a View will have to be created similar to DIM_PRODUCTS_V.

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

Example:

create sequence SEQ_DIM_<DIM> minvalue 1 maxvalue  999999999999999999999999999 increment by 1

Executing the SCD Component

You can execute the function from the Operations framework module of OFSAAI.

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

To define a new task for a Batch definition, follow these steps:

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

2.     Click Add + from the Task Details grid. The Task Definition window is displayed. Enter the Task ID and Description.

3.     Select Run Executable from the Run Executable drop-down list.

4.     Select the following from the Dynamic Parameters drop-down list:

Executable: scd,<map_ref_num>

For example, scd, 125.

A third optional parameter (N or Y) passed during SCD execution (like SCD,<map_ref_no>,<N/Y>) determines if a soft delete should be executed on for old records. The default parameter value is 'N'. For example, if the records are not part of the STG tables and SCD is executed with a parameter 'Y', then the older records in the DIM table will get soft deleted by setting the LRI indicator to 'N'.

Use the following table to identify the map_ref_num to be used, while executing the SCD process.

§       Wait: When the file is being executed, you can either wait until the execution is complete or proceed with the next task. Select the checkbox for Yes or No.

Click Yes to wait for the execution to be complete. Click No to proceed with the next task.

§       Batch Parameter: Select Y. (upper case required).

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

6.     Execute the Batch.

You can execute a Batch definition from the Batch Execution window of an OFSAAI Operations module.

You cannot execute the SCD process from the simplified batch window.

Checking the Execution Status

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

The status messages in the batch monitor are:

·        N: Not Started

·        O: On Going

·        F: Failure

·        S: Success

The ICC execution log can be accessed on the application server in the directory $FIC_DB_HOME/log/ficgen.

Sample Path: /dbfiles/home/oracle/OFSAAI/ficdb/log/ficgen

The file name will have the Batch Execution ID.

The detailed SCD component log can be accessed on the application server under <ftp-share>/<infodom name>/logs.

The file name will have the Batch Execution ID.

Sample Path: /dbfiles/home/oracle/ftpshare/OFSAADEMO/logs

Check the .profile file in the installation home if you are not able to find the paths mentioned earlier.

SCD Process Scenarios

It is not necessary to run SCD for all dimensions. In certain cases, you should specify the specific dimension requiring updates.

The following common scenarios provide guidance on which dimensions must be rerun:

·        Rerunning an existing ALM Process for the same as-of-date. It is not necessary to rerun any of the SCD dimensions.

·        Running an existing ALM Process for a new as-of-date.

·        Run SCD for Time Dimension (3), to refresh DIM_RESULT_BUCKET. Once per as-of-date and applies to all ALM processes run for that as-of-date.

·        Running a new ALM Process:

a.     Run SCD for Forecast Rate Scenarios or Stochastic Process (1)

b.     If new as-of-date, also run for Time Dimension (3)

If Hierarchy changes are made, rerun SCD for the appropriate dimension(s). For example, 4-7 as needed.

List of Dimension Tables

List of ALMBI Dimensions impacted by the SCD procedure are:

·        DIM_FCST_RATES_SCENARIO: This table stores all Deterministic Processes with relevant details.

Description of DIM_FCST_RATES_SCENARIO follows

·        DIM_RESULT_BUCKET: This table stores Income Simulation, Liquidity Risk Gap, and Interest Rate Gap Bucket information for each process

Description of DIM_RESULT_BUCKET follows

·        DIM_ORG_UNIT: This table stores Organization Unit dimension information.

Description of DIM_ORG_UNIT follows

·        DIM_GL_ACCOUNT: This table stores General Ledger Account information.

Description of DIM_GL_ACCOUNT follows

·        DIM_COMMON_COA: This table stores Dimensional Attributes of the COMMON_COA dimension.

Description of DIM_COMMON_COA follows

·        DIM_PRODUCT: This table stores Dimensional Attributes of the PRODUCT dimension.

Description of DIM_PRODUCT follows

·        DIM_PRODUCT_TYPE: This table stores Dimensional Attributes of the PRODUCT_TYPE dimension.

Description of DIM_PRODUCT_TYPE follows

·        DIM_CUSTOMER: This table stores Dimensional Attributes of the CUSTOMER dimension.

Description of DIM_CUSTOMER follows

·        DIM_ORG_STRUCTURE: This table stores Dimensional Attributes of the Legal Entity Dimension.

DIM_RUN Population

The DIM_RUN table is the dimension object that stores dimensional data for both Stochastic and Deterministic ALM Processes. A Data Transformation process is provided to populate the DIM_RUN table.

Topics:

·        Overview of DIM_RUN Process 

·        Prerequisites 

·        Table Used to Populate RUN details 

·        Executing the DIM_RUN Process 

·        Checking the Execution Status 

Overview of DIM_RUN Process

The database components used by this transformation are:

·        Database function: FN_DIM_RUN_ALM

·        Database procedure: POP_DIM_RUN, that is invoked by the function FN_DIM_RUN_ALM.

Prerequisites

Following are the pre-requisites for the DIM_RUN Process:

1.     All the post-install steps mentioned in the Oracle Financial Services Analytical Applications Infrastructure (OFSAAI) Installation and Configuration Guide and the Oracle Financial Services Asset Liability Management Analytics User Guide have to be completed successfully.

2.     Application users must be mapped to a role that has the seeded batch execution function (BATPRO).

3.     Before executing a batch, check if the following services are running on the application server.

§       Iccserver

§       Router

§       AM

§       Messageserver

For more information on how to check if the services are up and on, and how to start the services if you find them not running, see the Oracle Financial Services Analytical Applications Infrastructure User Guide.

4.     Batches must be created for executing the function. This is explained in section Executing the DIM_RUN Process.

Table Used to Populate RUN details

The physical table used in the ALM BI data model is DIM_RUN.

FSI_PROCESS_RUN_HISTORY, FSI_M_ALM_PROCESS, and FSI_M_OBJECT_DEFINITION_TL are the source tables which are used to populate DIM_RUN.

This table stores the Run details to be used for building the ALMBI reports. See the Oracle Financial Services Analytical Applications Data Dictionary Guide or the ALM BI Erwin Data Model for viewing the structure of this table.

Executing the DIM_RUN Process

You can execute the function from the Operations framework module of OFSAAI.

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

To define a new task for a Batch definition, follow these steps

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

2.     Click Add + from the Task Details grid. The Task Definition window is displayed. Enter the Task ID and Description.

3.     Select Transform Data from the Transform Data drop-down list.

4.     Select the following from the Dynamic Parameters list:

§       Rule Name: Select Populate_Dim_Run_ALM from the list of all available transformations. (This is a seeded Data Transformation process which is installed as part of the ALM BI application, if you don't see this process in the list, contact Oracle support).

§       Parameter List: Not Required.

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

6.     Execute the Batch. You can execute a Batch definition from the Batch Execution window of the OFSAAI Operations module.

You cannot execute this process from the Simplified Batch window.

Checking the Execution Status

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

The status messages in the batch monitor are:

·        N: Not Started

·        O: On Going

·        F: Failure

·        S: Success

The execution log and the detailed Dim Run population component log can be accessed from the location $FIC_DB_HOME/log/date.

The file name will have the Batch Execution ID.

Sample Path: /dbfiles/home/oracle/OFSAAI/ficdb/log/date

The database-level operations log can be accessed by querying the FSI_MESSAGE_LOG table. The Batch Run ID column can be filtered for identifying the relevant log.

Check the .profile file in the installation home if you are not able to find the paths mentioned earlier.

Time Dimension Population

ALM Business data is commonly represented as of a point in time or across a range of time periods. For this reason, the creation of a Time Dimension in OBIEE is an important capability. The following section describes how the time dimension is created and managed in ALM BI.

Topics:

·        Overview of Time Dimension Population 

·        Prerequisites 

·        How does ALM BI Use DIM Dates 

·        Table Used to Populate the Time Dimension 

·        Executing the Time Dimension Population Process 

·        Checking the Execution Status 

Overview of Time Dimension Population

The Time Dimension is treated as a Calendar dimension in OBIEE, which contains all dates for a specified period. The data is used on a day to day basis to populate the Account Summary Tables and many of the ALM BI result tables. The Time Dimension population process is used to populate the DIM_DATES table with values (between two dates) specified by the user.

The database components used by this process are:

·        Database function: FN_DIM_DATES

·        Database procedure: PROC_DIM_DATES_POPULATION, which is invoked by the function FN_DIM_DATES.

Prerequisites

Following are the prerequisites for Time Dimension Population:

1.     All the post-install steps mentioned in the Oracle Financial Services Analytical Applications Infrastructure (OFSAAI) Installation and Configuration Guide and the Oracle Financial Services Asset Liability Management Analytics User Guide must be completed successfully.

2.     Application users must be mapped to a role that has the seeded batch execution function (BATPRO).

3.     Before executing a Batch, check if the following services are running on the application server.

§       Iccserver

§       Router

§       AM

§       Messageserver

For more information on how to check if the services are up and on, and how to start the services if you find them not running, see the Oracle Financial Services Analytical Applications Infrastructure User Guide.

4.     Batches must be created for executing the function. This is explained in the section Executing the Time Dimension Population Process

How does ALM BI Use DIM Dates

During ALM BI transformation, each result table joins to the DIM_DATES table.

For example:

An ALM Deterministic Process inserts data into the following tables:

·        Result Detail (RES_DTL_xxxx, CONS_DTL_xxxx)

·        Result Master (FSI_O_RESULT_MASTER, FSI_O_CONSOLIDATED_MASTER)

·        Process Cash Flows (FSI_O_PROCESS_CASH_FLOWS)

·        Interest Rate Audit (FSI_O_INTEREST_RATES_AUDIT)

·        Exchange Rate Audit (FSI_O_EXCHANGE_RATES_AUDIT)

·        Economic Indicator Audit (FSI_O_ECONOMIC_IND_AUDIT)

·        Process Errors (FSI_PROCESS_ERRORS)

Result data is written to Process Cash Flows:

·        (FSI_O_PROCESS_CASH_FLOWS), Interest Rate Audit

·        (FSI_O_INTEREST_RATES_AUDIT), Exchange Rate Audit

·        (FSI_O_EXCHANGE_RATES_AUDIT), and Economic Indicator Audit

·        (FSI_O_ECONOMIC_IND_AUDIT) tables, only when the corresponding process option is selected

An ALM Stochastic Process can insert data into the Value at Risk result tables and the Earnings at Risk result tables based on the process options selected in the ALM Stochastic Process rule. The following tables are populated for each type of process:

·        Value at Risk

§       VaR by Product and Rate Path (FSI_O_STOCH_VAR)

§       Total VaR by Rate Path (FSI_O_STOCH_TOT_VAR)

§       Market Value by Product and Rate Path (FSI_O_STOCH_MKT_VAL)

§       Total Market Value by Rate Path (FSI_O_STOCH_TOT_MKT_VAL)

§       Interest Rate Audit (FSI_O_INTEREST_RATES_AUDIT)

§       Detail Cash Flows (FSI_O_PROCESS_CASH_FLOWS)

·        Process Errors (FSI_PROCESS_ERRORS)

·        Earnings at Risk

§       Average EaR by Product (FSI_O_EAR_LEAF_AVG)

§       EaR by Product and Rate Path (FSI_O_EAR_LEAF_DTL)

§       Average Net EaR (FSI_O_EAR_TOTAL_AVG)

§       Net EAR by Product (FSI_O_EAR_TOTAL_DTL)

§       Interest Rate Audit (FSI_O_INTEREST_RATES_AUDIT)

§       Detail Cash Flows (FSI_O_PROCESS_CASH_FLOWS)

§       Process Errors (FSI_PROCESS_ERRORS)

To successfully transform data from each of the above tables into the reporting model, the dates contained in these tables must also exist in the DIM_DATES table.

This includes the as_of_date, all dynamic start dates, and the dates corresponding to all active time bucket start and end dates. Result data is written to Interest Rate Audit (FSI_O_INTEREST_RATES_AUDIT) and Detail Cash Flows (FSI_O_PROCESS_CASH_FLOWS) tables only when the corresponding process option is selected.

Table Used to Populate the Time Dimension

The physical table used to store the time dimension in the ALM BI data model is:

DIM_DATES: This table holds the date details to be used for building the ALMBI reports.

Executing the Time Dimension Population Process

You can execute the function from the Operations framework module of OFSAAI.

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 grid. The Task Definition window is displayed.

3.     Enter the Task ID and Description.

4.     Select Transform Data from the Transform Data drop-down list.

5.     Select the following from the Dynamic Parameters drop-down list:

§       Rule Name: Select Dim_Dates_Population from the list of all available transformations. (This is a seeded Data Transformation which is installed as part of the ALM BI application, if you don't see this in the drop-down list, contact Oracle support).

§       Parameter List: Enter the Start Date and End Date in 'yyyymmdd' format. This is a mandatory parameter.

§       For example, 19000101','20120101'

§       Start Date: This is the date from which the Transformation will populate the DIM_DATES table.

§       End Date: This is the date up to which the Transformation will populate the DIM_DATES table.

6.     Click Save. Execute the Batch.

7.     You can execute a Batch definition from the Batch Execution window of the OFSAAI Operations module.

8.     You can execute this process from the Simplified Batch window. For more details, see Simplified Batch Execution.

9.     The function can also be executed directly on the database through SQLPLUS by providing the following details:

§       Function Name: FN_DIM_DATES

§       Parameters: P_BATCH_RUN_ID, P_AS_OF_DATE, P_ST_DT, and P_ED_DT

Sample Parameter Values: 'Batch1', '20091231', '19000101', and '19050101'

Checking the Execution Status

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

The status messages in the batch monitor are:

·        N: Not Started

·        O: On Going

·        F: Failure

·        S: Success

The Batch Process execution log and the detailed Time Dimension component log can be accessed from the location $FIC_DB_HOME/log/date.

The file name will have the Batch Execution ID.

Sample Path: /dbfiles/home/oracle/OFSAAI/ficdb/log/date

The database-level operations log can be accessed by querying the FSI_MESSAGE_LOG table. The Batch Run ID column can be filtered for identifying the relevant log.

Check the .profile file in the installation home if you are not able to find the paths mentioned above.