Analyzing ALM Results

Analyze the results generated by the ALM Engine to verify that processing completed as expected. For example, review the forecast rate audit information to ensure that the ALM Engine generated new business rates and the resulting interest accruals correctly for each scenario.

Review beginning balances, resulting balances for each forecast period, and scenario-specific behavioural assumptions. This review helps ensure that assumptions are defined correctly and applied as intended.

You can perform this analysis by using the seeded reports that are delivered with ALM Analytics. Administrators and power users must validate data and confirm processing results by running queries directly against the relational database before they release reports for analysis in Oracle Business Intelligence.

Use the following key concepts to analyze results:

Table 9-10 Factors to analyze ALM Engine results

Logical name Physical column name Definition and usage
ALM Process Identifier TM_PROCESS_SYS_ID Numeric identifier of the ALM process. Example: 1716904873663.
Execution or Batch Identifier EXECUTION_ID System-generated identifier for the process execution. It typically combines the process ID, As-of Date, and a sequence number. Example: 1716904873663_2015-10-09_1. If a process runs multiple times for the same As-of Date, the final sequence number changes, for example _2 or _3. Results from every execution are stored unless Delete prior results of same As of Date is selected in the process output preferences.
Execution Run ID EXECUTION_RUN_ID System-generated numeric technical identifier of the process execution. A single Execution ID can have more than one Execution Run ID, depending on internal units of work.
Result Type RESULT_TYPE_CD Identifies the result category: 0 = Current Business, 1 = New Business, 2 = Transaction Strategy, 3 = Auto Balancing.
Consolidation Flag CONSOLIDATION_FLAG Identifies currency basis: Y = amounts are in reporting currency; N = amounts are in base currency.
Reporting Currency Code REP_CURRENCY_CD Reporting currency selected in the ALM process definition. Populated only when CONSOLIDATION_FLAG = Y.
Time Bucket Type BUCKET_TYPE Identifies the analysis view: 0 = Income Simulation, 1 = Interest Rate Risk, 2 = Liquidity Risk.
Bucket Parent Start Sequence PARENT_START_SEQ When Dynamic Start Dates are not configured, results contain only the value 0. Values 1 and higher represent buckets for Dynamic Start Dates.
Scenario Number PARENT_START_SEQ Scenario number from the Forecast Rates assumption.
Aggregation Dimension Identifier CFA_ID Numeric identifier for the combination of dimension members used to aggregate ALM results. Dimension and member values are stored in FSI_CFA_DIMENSION_MAP_DTL.

Result Aggregation and Storage

Results are always aggregated by account type and currency. You don't need to select these dimensions explicitly during processing.

For a Static Deterministic process, results are aggregated by the dimensions or MDBSS configured in the process.

For a Dynamic Deterministic process, results are aggregated by the MDBSS configured in the process.

In result tables, rate-related financial elements, such as Net Rate, Gross Rate, and Discount Rate, are stored in interest rate format. For example, a rate of 2 percent is stored as 0.02. In reporting tables, these values are stored as weighted numbers.

In result tables, time-related financial elements, such as Weighted Average Remaining Maturity (WARM), Duration, and Runoff Timing, are stored as years, days, or fractions of a time bucket. In reporting tables, these values are stored as weighted numbers.

Result Tables

The following relational database tables contain results from Static Deterministic and Dynamic Deterministic ALM processes. You can query these tables directly by using a SQL query tool.

Table 9-11 List of database tables of process results and report

Result type Description Table name Sample SQL query
Result Master Stores static and point-in-time information for each product member included in the process, across all process scenarios. Measures include Current Balance, Current Rate, Current Transfer Rate, Yield to Maturity, Market Value, Duration, DV01, and Convexity. Data is stored in both base currency and reporting currency. Rows with CONSOLIDATION_FLAG = N are in base currency; rows with CONSOLIDATION_FLAG = Y are in reporting currency.
  • Process result table: FSI_CFA_ALM_RESULT_MASTER
  • Report table: FSI_BI_ALM_RESULT_MASTER
 SELECT * FROM FSI_CFA_ALM_RESULT_MASTER WHERE EXECUTION_ID = 'xxxxxx' AND CONSOLIDATION_FLAG = 'N' ORDER BY CFA_ID, ISO_CURRENCY_CD, BUCKET_TYPE, PARENT_START_SEQ, SCENARIO_NUM, RESULT_TYPE_CD;
Income Simulation Result Detail Stores cash flows aggregated across Income Simulation financial elements, such as Beginning Balance, Average Balance, Ending Balance, Interest Cash Flow, Accrued Interest, and Principal Cash Flow. Each row represents a time bucket. Data is stored in both base currency and reporting currency.
  • Process result: FSI_CFA_RESULT_DETAILS
  • Report table: FSI_BI_ALM_FINANCIAL_RESULT
SELECT * FROM FSI_CFA_RESULT_DETAILS WHERE EXECUTION_ID = 'xxxxxx' AND CONSOLIDATION_FLAG = 'N' ORDER BY CFA_ID, ISO_CURRENCY_CD, BUCKET_TYPE, PARENT_START_SEQ, SCENARIO_NUM, RESULT_TYPE_CD;
Interest Rate Risk Result Detail Stores cash flows aggregated across Repricing Gap financial elements, such as Gap Runoff and Gap Interest Cash Flow Net. Each row represents a time bucket. Data is stored in both base currency and reporting currency.
  • Process result table: FSI_CFA_IR_RESULT_DETAILS
  • Report table: FSI_BI_ALM_INTEREST_RATE_RISK
 SELECT * FROM FSI_CFA_IR_RESULT_DETAILS WHERE EXECUTION_ID = 'xxxxxx' AND CONSOLIDATION_FLAG = 'N' ORDER BY CFA_ID, ISO_CURRENCY_CD, BUCKET_TYPE, PARENT_START_SEQ, SCENARIO_NUM, RESULT_TYPE_CD;
Liquidity Risk Result Details Stores cash flows aggregated across Liquidity Gap financial elements, such as Gap Runoff, Gap Deferred Runoff, and Gap Interest Cash Flow Net. Each row represents a time bucket. Data is stored in both base currency and reporting currency.
  • Process result table: FSI_CFA_LR_RESULT_DETAILS
  • Report table: FSI_BI_ALM_LIQUIDITY_RISK
SELECT * FROM FSI_CFA_LR_RESULT_DETAILS WHERE EXECUTION_ID = 'xxxxxx' AND CONSOLIDATION_FLAG = 'N' ORDER BY CFA_ID, ISO_CURRENCY_CD, BUCKET_TYPE, PARENT_START_SEQ, SCENARIO_NUM, RESULT_TYPE_CD;
Detailed Cash Flow Stores instrument cash flows when Detailed Cash Flow is selected in ALM process output preferences. Process result and report table: FSI_ALM_CASHFLOW_OUTPUT_HIST SELECT * FROM FSI_ALM_CASHFLOW_OUTPUT_HIST WHERE EXECUTION_ID = 'xxxxxx' ORDER BY ACCOUNT_NUMBER, SCENARIO_NUM, RECORD_SEQUENCE;
Transfer Rate Intermediate Output Stores transfer pricing output at the cash-flow level for adjustable-rate instruments when Detailed Cash Flow is selected in ALM process output preferences. Process result and report table: FSI_ALM_TP_OUTPUT_HIST SELECT * FROM FSI_ALM_TP_OUTPUT_HIST WHERE EXECUTION_ID = 'xxxxxx' ORDER BY ACCOUNT_NUMBER, SCENARIO_NUM, RECORD_SEQUENCE;
Detailed Market Value for Dynamic Time Buckets Stores market value and related results for dynamic start dates, if defined in the time bucket, when Detailed Cash Flow is selected in ALM process output preferences. AS_OF_DATE stores the forward dynamic start date. Process result and report table: FSI_ALM_CASHFLOW_DYN_MV_OUTPUT_HIST SELECT * FROM FSI_ALM_CASHFLOW_DYN_MV_OUTPUT_ HIST WHERE EXECUTION_ID = 'xxxxxx' ORDER BY AS_OF_DATE, ACCOUNT_NUMBER, SCENARIO_NUM, RECORD_SEQUENCE;
Account Summary Stores instrument-level measures for each scenario, such as market price, duration, modified duration, yield, and DV01.
  • Process result: FSI_CFA_ALM_ACCOUNT_SUMMARY
  • Report: FSI_BI_ALM_ACCOUNT_SUMMARY
SELECT * FROM FSI_CFA_ALM_ACCOUNT_SUMMARY WHERE EXECUTION_ID = 'xxxxxx' ORDER BY ID_NUMBER, SCENARIO_NUM;
Time Bucket Details Stores bucket start and end dates for each process execution. Process result table: FSI_CFA_TIME_BUCKET SELECT * FROM FSI_CFA_TIME_BUCKET WHERE EXECUTION_ID = 'xxxxxx' ORDER BY BUCKET_TYPE, PARENT_START_SEQ, BUCKET_NUMBER;
Process Errors Stores account-level messages generated by Cash Flow Engine data validation. Process result and report: FSI_ALM_CFA_CFE_MESSAGES_HIST SELECT A.*, B.MESSAGE_DESCRIPTION FROM FSI_ALM_CFA_CFE_MESSAGES_HIST A, FSI_CFE_MESSAGES_MLS B WHERE A.MESSAGE_ID = B.MESSAGE_ID AND A.EXECUTION_ID = 'xxxxxx';
New Business Synthetic Instruments Dynamic Deterministic Process only. Stores new business instruments, or forecast balances, generated when Detailed Cash Flow - New Business is selected in ALM process output preferences.
  • Process result: FSI_ALM_CFA_FCAST_INSTRUMENTS,FSI_ALM_FCAST_EMBEDDED_OPTION S_SCH,FSI_ALM_FCAST_RATE_TIERS
SELECT * FROM FSI_O_FCAST_IRC_RATES_HIST WHERE EXECUTION_ID = 'xxxxxx';
Forecast Interest Rates Stores forecasted interest rate data for the selected Interest Rate Curve (IRC) for each scenario. Available when Forecast Rates is selected in ALM process output preferences. Process result and report table: FSI_O_FCAST_IRC_ RATES_HIST SELECT * FROM FSI_O_FCAST_IRC_RATES_HIST WHERE EXECUTION_ID = 'xxxxxx';
Forecast IRC Implied Forward Coefficients Stores IRC implied forward coefficients when the forecast method is Implied Forward. Available when Forecast Rates is selected in ALM process output preferences. Process result and report table: FSI_O_FCAST_IRC_IF_COEFF_HIST SELECT * FROM FSI_O_FCAST_IRC_IF_COEFF_HIST WHERE EXECUTION_ID = 'xxxxxx';
Forecast Economic Indicator Stores forecasted rates for selected economic indicators for each scenario. Available when Forecast Rates is selected in ALM process output preferences. Process result and report table: FSI_O_FCAST_ECO_IND_RATES_HIST SELECT * FROM FSI_O_FCAST_ECO_IND_RATES_HIST WHERE EXECUTION_ID = 'xxxxxx';
Forecast Exchange Rate Stores forecasted rates for selected currencies for each scenario. Available when Forecast Rates is selected in ALM process output preferences. Process result and report table: FSI_O_FCAST_CCY _RATES_HIST SELECT * FROM FSI_O_FCAST_CCY_RATES_HIST WHERE EXECUTION_ID = 'xxxxxx';
Forecast Volatility Stores forecasted rates for selected volatility surfaces for each scenario. Available when Forecast Rates is selected in ALM process output preferences. Process result and report table: FSI_O_FCAST_VOL_ RATES_HIST SELECT * FROM FSI_O_FCAST_VOL_RATES_HIST WHERE EXECUTION_ID = 'xxxxxx';