1. User Guide
  2. Dimension Population
  3. Time Dimension Population
  4. How does ALM BI Use DIM Dates

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