Users can combine the result sets from multiple processing runs if needed for reporting. This chapter contains the following sections, which describe how to merge results from multiple Oracle Asset Liability Management processes:
Topics:
· Check the Process Unique Identifier
· Create a new ALM Process rule for your Consolidated Results
· Two Approaches to Consolidating Results
· Columns in the Result Detail Table
· Result_Master Table (Market Value Results)
Confirm that the results were generated with identical assumptions for the following:
· Modeling Date Buckets
· Dynamic Gap Date Buckets and Start Dates
· Gap Profiles (for example, were gap profiles generated on all processing runs?)
· Market Value Start Dates
· Market Valuation (for example, were market values performed on all processing runs?)
· Multiple Modeling Rates (for example, Model with Transfer rates option enabled)
· Auto-Balancing (for example, was auto-balancing performed in each processing run?)
· All assumption rules are the same (Optional: see Pre-Populate the Supporting Result Files)
· If any of these assumptions are not consistent (with the possible exception of Assumption rules), your consolidated results are not valid.
To report off your consolidated results, you must create an Asset Liability Management (ALM) Process rule for your consolidated results. Remember that you cannot consolidate multiple ALM process results through the ALM Process interface. This step merely enables you to report off your consolidated results using the Oracle Financial Services Asset Liability Management Analytics (ALM BI ) (or a 3rd party) reporting interface.
There are four tables that get updated with the results of an ALM processing run. Two of them do not hold actual results, but rather the supporting information necessary to report off a processing run:
· FSI_O_Result_Bucket: The FSI_O_Result_Bucket table is updated with all of the date bucket header information (that is start and end dates for all date buckets). This table is accessed in all date related reports.
· FSI_O_Result_Header: The FSI_O_Result_Header table is updated with the names of all assumption rules that make up the processing run. The rule names can also be accessed when generating reports.
To populate these tables for your Consolidated Results - ALM Processing rule, you can set up an ALM process with a single record and run it. The result is that both the FSI_ O_Result_Bucket and FSI_O_Result_Header tables are populated, as well as the results in FSI_O_Result_Master and Res_ Dtl_<Sys_ID_Num>. The results in FSI_O_Result_ Master and Res_Dtl_<Sys_ID_Num> are not accurate and must be replaced in Step Consolidate Results Data.
Deterministic Results in Oracle ALM are stored in four tables:
Detail Result Tables: RES_DTL_<Sys_ID_Num> and Cons_Dtl_<Sys_ID_ Num>. This contains cash flow and gap results for an individual ALM deterministic processing rule.
FSI_O_RESULT_MASTER and FSI_O_CONSOLIDATED_MASTER: This contains market value results for all ALM deterministic processing rules.
· All ALM processing runs have an associated table for the storage of detailed results. A few key characteristics of these tables which are important to understand when consolidating results:
· There are 240 columns used to store forecast information BUCKET_001... BUCKET_240.
· The meaning in any of the BUCKET columns is determined by the dimension columns (listed as follows). In other words, the same column is used to store any type of financial information - you must interpret the key columns to determine what the value represents.
· All information can be consolidated by simply summing up the Bucket columns. The numbers are stored so that for weighted average purposes you need only to sum up the values and group by the relevant key columns.
There are two approaches you can take to consolidating results:
· Physically consolidate the data into the existing Result Detail table. If you want to physically update theRes_Dtl_<Sys_ID_Num> table, remember to delete all of the data that exists in the table (if any) from Step Pre-Populate the Supporting Result Files.
· Drop the existing Result Detail table and replace this with a database view.
An alternative to physically inserting the consolidated data into the Res_Dtl_ <Sys_ ID_Num> table is to drop the physical Res_Dtl_<Sys_ID_Num> table and create a Database View with the identical name. Advantages to this approach are: a view saves disk space, and always reflects the latest underlying results. If you want to store the consolidated results, but not the underlying detail, however, you must store the consolidated results in a physical table.
Sample SQL: View Approach
CREATE VIEW RES_DTL_101 AS SELECT * FROM RES_DTL_1 UNION SELECT
* FROM RES_DTL_2
This approach merely appends the results from both underlying results set. This is acceptable because all reports sum records with the same key values together. Replace original Result_Sys_Id with the Id of new ALM process created for consolidated results.
If the Database Administrators create a view, you must grant access to that view to the appropriate users.
The reporting engine sums all records with the same key values together.
There are three categories of columns in the Result Detail table:
· Dimension Columns: Always include these columns in your group-by statement.
· Result Columns: These columns should always be summed.
· Other Columns: These columns are irrelevant to the results, but must be included in the consolidation table or view for reporting to function properly.
If you want to use a more efficient method for physically consolidating data, create a SQL statement that groups by the Dimension Columns, sums the Result Columns, and Defaults the Other Columns. This approach saves space over the simple approach of appending the results from all detailed ALM Processing rules.
Table 1: Dimension Columns of Result Detail table
Column |
Description |
ALM Product Dimension |
Your ALM product (or Chart of Accounts) dimension as specified in Application Preferences |
Common Chart of Account dimension |
Your Common Chart of Account dimension |
Organization Unit dimension |
If results are consolidated to the entity level, this may be irrelevant |
ISO Currency Code |
Currency in which results are held |
Financial Element |
The dimension that gives meaning to the row of data in the table - for example, are the numbers in bucket_001-bucket _240 referring to ending balance, ending rate, average balance, and so on. |
Scenario Number |
Results are stored by scenario number |
Start Date Index |
Start Date 0 = Cash Flow Information and > = 1 are used for Gap results, Market Value results, and Dynamic Start Dates |
Result Type Code |
Code describing the source of the record, current position, new business, or formula results. This column may be omitted in the group by statement if you do not want to maintain this distinction in your consolidated results. |
Table 2: Details of Result Columns of Result Detail table
Column |
Description |
Bucket_001 ….. Bucket_240 |
|
Table 3: Details of Other Columns of Result Detail table
Column |
Description |
All other Dimension Columns |
These columns are not relevant to your simulation |
The market value results in the result_master table must be manually consolidated through a SQL statement. As with the Detail Results table, the columns in the Result_ Master Table can be grouped into the same three categories:
Table 4: Details of Dimension Columns of Result Detail table for Market Value Results
Column |
Description |
ALM Product Dimension |
Your ALM product (or Chart of Accounts) dimension as specified in Application Preferences |
Common Chart of Account dimension |
Your Common Chart of Account dimension |
Organization Unit dimension |
If results are consolidated to the entity level, this may be irrelevant |
Result Sys ID |
The unique identifier for process results. This identifier maps to a specific ALM Process rule |
ISO Currency Code |
Currency in which results are held |
Scenario Number |
Results are stored by scenario number |
Start Date Index |
Start Date 0 = Cash Flow Information and > = 1 are used for Gap results, Market Value results, and Dynamic Start Dates |
Financial Rollup |
A code indicating the financial account type of the line item |
Leg Type |
Indicates the leg type of the instrument |
Result Type Code |
Code describing the source of the record, current position, new business, or formula results. This column may be omitted in the group by statement if you do not want to maintain this distinction in your consolidated results. |
· AVERAGE_LIFE
· CONVEXITY
· CUR_DEFER_BAL_C
· CUR_INTR_REC_ACCR
· CUR_NET_PAR_BALANCE
· CUR_NET_RATE_W
· CUR_PAR_BAL
· CUR_TRANSFER_RATE
· CUR_WARM
· DURATION
· DV01
· LEG_TYPE
· MARKET_VALUE
· MARKET_VALUE_CLEAN
· MODIFIED_DURATION
· NEW_GROSS_BALANCE
· NEW_NET_BALANCE
· YTM
Table 5: Details of Other Columns of Result Detail table for Market Value Results
Column |
Description |
Result_Sys_ID |
This is a key column for associating the results with your ALM Process. Plug this with the value from the System ID of your consolidated processing rule. |
All other Dimension Columns |
These columns are not relevant to your simulation. |
Sample SQL:
The following statement was used to consolidate the results of sys_id_num = 1 and 2 into a sys_id_num = 101:
INSERT INTO FSI_O_RESULT_MASTER SELECT 101 AS RESULT_SYS_ID,
SCENARIO_NUM, ORG_UNIT_ID,
0 AS GL_ACCOUNT_ID,
COMMON_COA_ID, PRODUCT_ID,
FINANCIAL_ROLLUP, START_DATE_INDEX,
LEG_TYPE, SUM(CUR_PAR_BAL), SUM(CUR_NET_PAR_BAL), SUM(CUR_DEFER_BAL_C), SUM(CUR_NET_RATE_W), SUM(CUR_WARM), SUM(CUR_INTR_REC_ACCR),
SUM(NEW_GROSS_BALANCE), SUM(NEW_NET_BALANCE), SUM(MARKET_VALUE), SUM(DURATION), SUM(CONVEXITY), SUM(CUR_TRANSFER_RATE), SUM(MARKET_VALUE_CLEAN), SUM(MODIFIED_DURATION), SUM(YTM), SUM(AVERAGE_LIFE)
FROM FSI_O_RESULT_MASTER WHERE RESULT_SYS_ID IN (1,2) GROUP BY
SCENARIO_NUM, ORG_UNIT_ID, COMMON_COA_ID, PRODUCT_ID, FINANCIAL_ROLLUP,
START_DATE_INDEX, RESULT_TYPE_CD, LEG_TYPE
After the above steps of consolidating data in result tables, you must execute ALMBI transformation using the Sys Id of new ALM process created for the purpose. This will populate results in BI tables and user can view reports using new ALM process id.