8       Loading Multiple Load Runs in OFSAA

This section provides information about Loading Multiple Load Runs in OFSAA in the Oracle Insurance Data Foundation application and step-by-step instructions to use this section.

Topics:

·        Objective

·        Design Details

·        Implementation

·        Loading OFSAA Staging Tables

·        Post Stage Load Process

·        Loading Data into OFSAA Results Tables from Staging Tables

Multiple load run enables data to be loaded multiple times during the day for staggered processing of data by analytical applications. The degree of complexity of data required by analytical apps vary from one to the other, the load run ensures that the customer can process the data as soon as it is ready for an app to uptake. This reduces the turnaround time to reporting, by avoiding the 'end of day' type of processing of information as part of the original design.

NOTE:   

As a work around, currently you can use the following methods to load the results table using different Load Run IDs.

Objective

The following are the objectives of loading multiple Load Runs in OFSAA:

1.     To optimize the end?to?end data flow and the need for intra?day reporting, institutions could load intra-day records into OFSAA. Current application can only handle one set of records per date (incremental loads are not possible).

2.     Users need to adjust and reload data (either full or partial) for the current date.

3.     Users need to adjust and reload data (either full or partial) for any of past dates.

4.     Support incremental consumption of data from staging area.

NOTE:   

The load run is enabled only in the model and is '0' by default in the model. This does not impact data previously available. The enhancements to the OFSAA batch and run framework to cover all the use cases will be taken in a future release.

This release of the OIDF staging model provides a flexibility to load multiple snapshots of the data in the staging tables (Product Processors). A column named N_LOAD_RUN_ID is introduced as part of the primary key of the product processor tables to enable this feature. However, the full fledged functionality to load and manage these snapshots will be part of the platform release at a later stage. In order to leverage this design in this release, the below mentioned changes should be performed as a workaround to load multiple snapshot of data from staging to results tables such as Fact Common Account Summary.

For Loading multiple snapshots of data for the same FIC_MIS_DATE, the existing T2T's should be executed through the Run Rule Framework . Additionally, the load should be filtered accordingly for each load run via the run filter.

To execute this run:

1.     Navigate to $FIC_HOME/ficweb/webroot/conf

2.     Edit the file: excludeURLList.cfg

3.     Add the following entry at the end of the file: [SQLIA]./pr2

NOTE:   

There should not be any blank line in the file.

Design Details

Loading of data into OFSAA can be in any of the following ways:

·        ETL Tool

·        OFSAA F2T

·        OFSAA T2T

·        OFSAA Excel upload

·        OFSAA DIH

OFSAA data model includes load run identifier as part of the primary key for a set of staging tables. This enables data to be stored for multiple load runs for any date. OFSAA data model also has a table to maintain master information about load run and can be used for identifying or filtering load run during run execution within OFSAA.

Implementation

Before loading data into the staging table, generate a Load Run Identifier to stamp the records from the source. These records can be a complete snapshot or can be partial or incremental data too. This load run identifier can be generated by calling the function in the OFSAA atomic schema named FN_REGISTER_LOAD_RUN. The function expects some input parameters and returns a unique load run identifier back to the calling program.

Figure 50: Load Run process flow

NOTE:   

Column n_load_run_id should always be populated only by the value returned by fn_register_load_run.

Function - Register Load Run (fn_register_load_run)

Parameters - Batch ID, MIS-Date, Load Run Name, Load Run Purpose, Load Run Type

Example:

Declare

Result number;

Begin

Result: = fn_register_load_run ('OFSOIDFINFO_20150101_1','20150101','OIDF_LOAD','BA', 'A');

End;

The function registers the request in the table name REV_LOAD_RUN_MASTER and marks load as "In progress". You can use columns LOAD_RUN_NAME and LOAD_PURPOSE as per the requirement.

Column Load Type must have only the permissible value such as:

·        "B - Base

·        "A - Adjustments

·        "P- Backdated Adjustments

NOTE:   

Multiple calls to the procedure can be made to the function for given FIC_MIS_DATE. Each call will return a number which will be unique across the FIC_MIS_DATE or Extraction date. You can use this load identifier to load either one or more staging tables.

Loading OFSAA Staging Tables

After the load run ID is generated as described above, you can use the same in external ETL process to stamp the records from the source system before them loading either in one or the multiple staging tables of OFSAA Staging area.

Load strategy at a customer site falls across two categories:

·        Complete Snapshot

·        Incremental Snapshot

Complete Snapshot Load Example

For example, if we have three Loan Contract accounts in a bank system which is supposed to be loaded into OFSAA Stage Loan Contracts using the Load Run ID = 1 for BASEL Standard Approach, the data after loading staging table will appear as below:

NOTE:   

After each load you need to run fn_register_load_details function mentioned in Post Stage Load Process and Updating Load as Completed which is explained in the following sections.

In order to enable downstream applications to consume only the latest set of record, you need to call another function named FN_POP_LOAD_RUN_MAP. This is mandatory in case of incremental snapshot load scenario. This function populates a intermediate processing table that keep track of latest incoming record identifier.

Function - Populate Load Run Map

Parameters - Batch Id, MIS Date, Stage Table Name, Load Run Id, Load Run Name

Example:

Declare

Result number;

Begin

Result: = fn_pop_load_run_map('OFSOIDFINFO_20150101_1','20150101','STG_ANNUITY_CONTRACTS',1,'OIDF

_LOAD');

END;

NOTE:   

For troubleshooting any errors while making the function calls , refer to FSI_MESSAGE_LOG table for more details.

For the example mentioned above, records in FSI_ACCOUNT_LOAD_RUN_MAP table appears as follows.

There may be a requirement to reload a complete snapshot of data in the OFSAA staging again. This could either be to satisfy an intraday reporting requirement or to load corrected source records in the OFSAA staging table. The earlier design forced users to truncate the staging table to accommodate the new set of dates. However, with the introduction of the Load Run identifier concept, you can retain both sets of data in the staging area and allow the downstream application to choose the correct set for processing. This involves making another call to FN_REGISTER_LOAD_RUN function.

For example in the below table, LOAN1001 and LOAN1002 have some changes since the previous load and will now need to be loaded again staging with a different load run identifier. Additionally, the strategy is to load the complete snapshot again to staging, all the records from the source such as both changed and unchanged records will need to stamp with the new load run identifier.

STG_LOAN_CONTRACTS after load appears as follows.

REV_LOAD_RUN_MASTER after second function call appears as follows.

Incremental Snapshot Load Example

This scenario is applicable when source may to portions of data at different point in time, or handover only the records changed since the last load. This is contrary to example explained under the Complete snapshot load section.

The same scenario in case of incremental snapshot load will appear as below.

NOTE:   

Only the changed source records are stamped with the new load run identifer.

NOTE:   

After each load you need to run FN_REGISTER_LOAD_DETAILS function mentioned in Post Stage Load Process and Updating Load as Completed which is explained in the following sections

To enable downstream applications to consume only the latest set of record, you need to call another function named fn_pop_load_run_map. This is mandatory in case of incremental snapshot load scenario. This function populates a intermediate processing table that keep track of latest incoming record identifier.

Function - Populate Load Run Map

Parameters - Batch ID, MIS Date, Stage Table Name, Load Run ID, Load Run Name

Example

Declare

Result number;

Begin

Result: = fn_pop_load_run_map('OFSOIDFINFO_20150101_1','20150101','STG_ANNUITY_CONTRACTS',1,'OIDF

_LOAD');

END;

NOTE:   

For troubleshooting any errors while making the function calls , refer to fsi_message_log table for more details.

For the example mentioned above, records in FSI_ACCOUNT_LOAD_RUN_MAP table will appear as below:

Post Stage Load Process

Once you load the OFSAA Stage tables successfully using the load run ID which is generated from Load Run Map function, you need to perform certain post load processes in order to complete the loading.

Topics:

·        Register Load Run Details

·        Updating Load as Completed

Register Load Run Details

Once you load the OFSAA Stage tables successfully using the load run ID which is generated from Load Run Map function, you need to register the load run details by calling the following function with the load type whether it was incremental or full snap shot.

Function - Register Load Run Details

Parameters - batch ID, mis-date, load run name, load run ID, stage table name, load type

Example:

Declare

Result number; Begin

Result: =

fn_register_load_details('OFSOIDFINFO_20150101_1','20150101', 'STG_ANNUITY_CONTRACTS',1,'OIDF_LOAD', 'I');

END;

This function populates a table named rev_load_run_details. The columns load type can have only 2 values, such as:

·        "S - Complete/Full Snapshot

·        "I - Incremental Snapshot

Updating Load as Completed

Once you complete these steps, update the status of the record inside rev_load_run_master as Completed.

UPDATE  rev_load_run_master

SET     LOAD_RUN_STATUS = 'Completed' WHERE pMis_Date = '01-Jan-15'

AND     pLoad_Run_Name = 'OIDF_LOAD' and LOAD_RUN_ID = 1;

Loading data into OFSAA Results Tables from Staging Tables

OFSAAI does not support load run versioning for Data Movement from Stage to Results using Batch/Run Framework. The same feature is expected as an enhancement in future release of OFSAAI.

As a work around, currently you can use the following methods to load the results table using different Load Run IDs.

Topics:

·        Complete Snapshot Load Scenario

·        Incremental Load Scenario

Complete Snapshot Load Scenario

To enable the Data Load into Results table from staging using the Load Run concept in case of Full SnapShot, you can use the following Task Level Parameter in the T2T Filter condition and can pass the load run ID to be passed in Batch Framework or Run Framework.

For example: FCPS T2T for Stage Loan Contracts

·        Modify Filter Condition inside T2T:

§       Filter Condition to be used in T2T: The highlighted condition required to be added in T2T

Filter : UPPER(STG_ANNUITY_CONTRACTS.v_gaap_code)='USGAAP' AND STG_ANNUITY_CONTRACTS.fic_mis_date=$MISDATE AND STG_ANNUITY_CONTRACTS.N_LOAD_RUN_ID='[LOADRUN]'

·        Modify the Corresponding Batch Task Each Time with Load Run Idxxx

a.     Select Batch, Task (T2T_FCPS_STG_ANNUITY_CONTRACTS)

b.     Click Edit.

c.     Add Highlighted Condition in Default Value and Save (Each Time we must provide the Load Run ID. Here in the following example, 1 is used.)

[DRCY]=USD,[LOADRUN]=1

d.     Execute the batch after this change. It loads the Result table with the given Load Run ID Records.

e.     Modify the Corresponding Process Task of a Run each time with the Load Run ID.

f.       Select the Process and click Edit.

g.     Choose Components, select the Object (T2T_FCPS_STG_ANNUITY_CONTRACTS) and click Components.

h.     Click the dropdown button associated with the T2T.

i.       Add the Highlighted Condition with the following text, and Save DRCY, USD, LOADRUN, 1.

j.       Execute the Run. It loads the Result table with the given Load Run ID records.

Incremental Load Scenario

To enable the Data Load into Results table from staging using the Load Run concept in case of incremental load, you can use the following T2T join change concept in the T2T ANSI join and can use the Batch Framework or Run Framework to execute the same.

For example, FCPS T2T for Stage Annuity Contracts:

Modify Join Condition inside T2T:

Join Condition to be added in all relevant T2T

For example, STG_ANNUITY_CONTRACTS T2T join

INNER JOIN FSI_ACCOUNT_LOAD_RUN_MAP

ON FSI_ACCOUNT_LOAD_RUN_MAP.V_ACCOUNT_NUMBER =

STG_ANNUITY_CONTRACTS.V_ACCOUNT_NUMBER

AND FSI_ACCOUNT_LOAD_RUN_MAP.N_LOAD_RUN_ID = STG_ANNUITY_CONTRACTS.N_LOAD_RUN_ID AND FSI_ACCOUNT_LOAD_RUN_MAP.FIC_MIS_DATE = STG_ANNUITY_CONTRACTS.FIC_MIS_DATE AND FSI_ACCOUNT_LOAD_RUN_MAP.V_GAAP_CODE = STG_ANNUITY_CONTRACTS.V_GAAP_CODE AND FSI_ACCOUNT_LOAD_RUN_MAP.F_LATEST_LOAD_RUN_FLAG = 'Y'

Execute the batch or fire the Run after the above step.