8.28 Analyzing Results

You should always analyze results obtained from the ALM Engine. For example, you should review the forecast rate – audit information to ensure that the new business rates and resulting interest accruals have been generated correctly for each scenario.

Also, you should review both your beginning balances and resulting balances in each forecast period as well as your scenario-specific behavioral assumptions to verify that assumptions have been defined and applied correctly.

This type of review is performed by analyzing the seeded reports delivered as part of the ALM Business Intelligence application. Many times, administrators and power users must analyze data and confirm processing results by running queries directly against the relational data prior to releasing reports for analysis through Oracle Business Intelligence.

The following relational database tables are populated with ALM Static Deterministic and Dynamic Deterministic process results and can be queried directly by a SQL query tool such as Oracle SQL Developer.

Table 8-9 List of Database tables of process results

Table Name	Description	Sample SQL query
Result Master	Static and point in time information such as Current Balance, Current Rate, Current Transfer Rate, Yield To Maturity, Market Value, Duration, DV01, and Convexity are stored in this table for each Product COA member included in the process, for all scenarios associated with the process. Result Master data is stored in original or base currency.	select * from FSI_O_RESULT_MASTER where result_sys_id=xxxxxx order by start_date_index, scenario_num, product_id;

Result Detail	Cash flow results are stored in result detail and are spread across columns based on the Active time bucket rule used when the process was run. Each row of data represents a distinct Financial Element such as Beginning Balance, Average Balance, Ending Balance, Interest Cash Flow, and Principal Cash Flow, and so on. Result Detail data is stored in original or base currency.	select * from res_dtl_xxxxxx order by start_date_index, scenario_num, product_id, financial_elem_id, result_type_cd;
Consolidated Master	The Consolidated Master table is populated only when processing data containing multiple currencies and the Consolidate to Reporting Currency option is selected. The data in this table will be expressed in a single designated currency (reporting currency), but will otherwise have the same structure as described above for Result Master.	select * from fsi_o_consolidated_master where result_sys_id=xxxxxx order by start_date_index, scenario_num, product_id;

Consolidated Detail

The Consolidated Detail table is populated only when processing data containing multiple currencies and the Consolidate to Reporting Currency option is selected. The data in this table will be expressed in a single designated currency (reporting currency), but will otherwise have the same structure as described above for Result Detail.

select * from cons_dtl_xxxxxx order by start_date_index, scenario_num, product_id, financial_elem_id, result_type_cd;

The following relational database tables are populated with ALM Static Stochastic and Dynamic Stochastic process results:

Table 8-10 Relational database tables are populated with ALM Static Stochastic and Dynamic Stochastic process results

Table Name	Description	Sample SQL query
Stochastic VaR	VaR results are output and stored in this table for each Product COA member included in the process.	select * from fsi_o_stoch_var where tm_process_sys_id = xxxxxx order by leaf_node, probability;
Stochastic Total VaR	VaR results are output and stored at the portfolio level in this table.	select * from fsi_o_stoch_tot_var where tm_process_sys_id = xxxxxx order by probability;
EAR Leaf Average	Earnings at Risk results are output and stored at the Product COA level as an average amount across all of the selected rate paths.	select * from ear_leaf_avg_xxxxxx order by end_date, leaf_node;

EAR Leaf Detail	Earnings at Risk results are output and stored at the Product COA level for each rate path selected in the process.	select * from ear_leaf_dtl_xxxxxx order by rate_path_num, end_date, leaf_node;
EAR Total Average	Earnings at Risk results in this table are aggregated to include only net interest income and net income and are shown as an average amount across all the selected rate paths.	select * from ear_total_avg_xxxxx order by end_date;
EAR Total Detail	Earnings at Risk results in this table are aggregated to include only net interest income and net income and shown for each rate path.	select * from ear_total_dtl_xxxxxx order by rate_path_num,end_date;

During Stochastic processing, if source data is expressed in multiple currencies, it is converted to reporting currency at the start of processing.