1. Credit Risk Analytics User Guide
  2. Input Data Preparation
  3. Setup Tables

3.1 Setup Tables

A Setup Table is a set of Seeded Data that is static and does not change at regular intervals.

You must update the SETUP_MASTER table with the required data. During the Dimension Population Process, the SETUP_MASTER table is accessed by the Slowly Changing Dimension (SCD) Component.

The following table lists the variables and their descriptions.

Table 3-1 Variables and Their Descriptions

Variable Name Variable Description Variable Dummy Value
CRA_FISCAL_MONTH Stores the Fiscal Month in DD/MM format. 01/04
DEFAULT_GAAP Stores the default GAAP code. USGAAP
DEFAULT_FX_RATE_SRC Stores the default Foreign Exchange Rate source code. DEFAULT
CRA_REP_CCY Stores the Reporting Currency. -1
CRA_LLFP Stores the LLFP Run Skey. -1
RA_STD_CCY_CD Stores the Standard Currency used in currency conversion. USD
CRA_MRMM Stores the CRA MRMM Execution details. -1
CRA_REGCAP Stores the CRA Basel Execution details. -1
CRA_ALM Stores the ALM Execution details. -1
CRA_CASHFLOW_PROCESS_ID_LIST Stores the list of ALM Cash Flow Process ID. -1
CRA_STANDARD_PROD_EXCLUSION Excludes comma separated values of V_STANDARD_PRODUCT_TYPE_CODE while loading data in to the FCT_CREDITRISK_ACCOUNT_SUMMARY table. EQ
CRA_LOAN_EXP_STD_PROD Considers comma separated values of V_STANDARD_PRODUCT_TYPE_CODE for loan exposure computation PCFC, PERLOANS, LOANS, CC, CSHCRED, LGS, DDL, SGUA, GURR, GUARANTEE, PC, SCPT, LCD, EDULNS, OD, LEASE
CRA_INVESTMENT_EXP_STD_PROD Considers comma separated values of V_STANDARD_PRODUCT_TYPE_CODE for investment exposure computation CP, ZCB
CRA_DERIVATIVE_EXP_STD_PROD Considers comma separated values of V_STANDARD_PRODUCT_TYPE_CODE for derivative exposure computation CFUT, FDS, SWPS, OPT
CRA _STANDARD_PROD_EXCLUSION Excludes comma separated values of V_STANDARD_PRODUCT_TYPE_CODE while loading data in to the FCT_CREDITRISK_ACCOUNT_SUMMARY table. EQ
CRA_STATS_PARALLEL_DOP CRA_STATS_PARALLEL_DOP 8
DT_PARALLEL_DOP Degree of parallelism to be used in DML and Queries statements in data transformations 2
DT_PARALLEL_ENABLE Enables parallel sessions for DML and Queries statements in data transformations Y
GATHER_TABLE_STATS If the value is set to Y then process will gather table stats N

OFS CRA Application provides the capability of reporting figures from other applications such as OFS Loan Loss Forecasting and Provisioning (LLFP), OFS Market Risk Measurement and Management (MRMM), OFS Asset and Liability Management (ALM), and OFS Basel Regulatory Capital. If the LLFP MRMM, ALM, and Basel Regulatory Capital implemented by the financial institution are from an external vendor, then you must manually populate the relevant target tables in the OFS CRA Application.

The following table lists the target tables to be populated.

Table 3-2 Target Tables to be Populated

Target Tables Application Name
FCT_LLFP_ACCOUNT_SUMMARY Loan Loss Forecasting and Provisioning
FCT_MR_PFE_GROUP_OUTPUT Market Risk Measurement and Management
FCT_MR_PFE_TRADE_OUTPUT Market Risk Measurement and Management
FCT_MR_MNTCRLO_SIM_EXEC_DTL Market Risk Measurement and Management
FCT_CP_CVA_DETAILS -
FCT_PROCESS_CASHFLOW Asset and Liability Management
FCT_REG_CAP_ACCOUNT_SUMMARY Basel Regulatory Capital

Stage tables are populated in product processors or other stage tables as listed in the Run Chart. Product Processor is an entity that stores data from the Operational Systems of the Bank. This entity is created based on the various financial products that the Bank caters to. A snapshot of the data is expected as a download in all the Stage Tables. The Stage Data moves to the processing (fact) tables through Table-to-Table transformations (T2Ts).

The following are the main Fact Tables for OFS Credit Risk Analytics, which store all the download values:
  • Fact Common Account Summary
  • Fact Net Exposure
  • Fact Counterparty Product Type Summary

Any computed measures are stored in the Fact Credit Risk Account Summary table.

Additionally, a Counterparty and a Product Granularity table is populated with the downloaded values at the counterparty level and product type level. The computed measures are calculated considering the data from the Fact Credit Risk Account Summary table and flows to the Fact Counterparty Product Type Summary table. Any additional measures are computed and stored in these tables.

The type of data expected as a download in the Stage tables is as follows:
  • Account Granularity Data

    This input data is expected in the following Product Processor tables:

    • STG_BILLS_CONTRACTS
    • STG_CARDS
    • STG_GUARANTEES
    • STG_INVESTMENTS
    • STG_LC_CONTRACTS
    • STG_LEASES_CONTRACTS
    • STG_LOAN_CONTRACTS
    • STG_MM_CONTRACTS
    • STG_OD_ACCOUNTS
    • STG_FUTURES
    • STG_FX_CONTRACTS
    • STG_OPTION_CONTRACTS
    • STG_REPO_CONTRACTS
    • STG_SWAPS_CONTRACTS
    • STG_CREDIT_DERIVATIVES
    • STG_FORWARDS
  • Customer Data

    This input data is expected as a download in the following tables:

    • Stage Party Master
    • Stage Party Role Map
    • Stage Party Type
    • Stage Party Details
    • Stage Rating Details

      Data flows from the preceding tables to the corresponding Dimension Table, which is the:

    • Dim Party Table
    • Dim Party Type
    • Fact Party Details
    • Fact Rating Details
  • Rating Granularity Data

    This input data is expected as a download in the Stage Party Rating Details table which moves to the Fact Party Ratings Details table.

  • Mitigant Data

    This input data is expected as a download in the Stage Mitigants tables. Data flows from these Stage tables to the Fact Mitigants table.

    Additionally, data flows from the Stage Account Mitigant Map table to the Fact Account Mitigant Map table. This Stage table stores the mapping between the accounts and the respective mitigants.

  • Exchange Rate Granularity Data

    This input data is expected as a download in the Stage Forward Exchange Rates table. From the Stage table, data is populated to the FSI Exchange Rate tables.

  • Net Exposure

    This input data is expected as a download in the Stage Net Exposure table. Data flows from this Stage table to the Fact Net Exposure Table. The computed measures at the netting level from the Fact Net Exposure table are stored in the Fact Credit Risk Account Summary table at the account level granularity. The measures are aggregated at a product type, party and legal entity level and stored in the Fact Counterparty Product Summary table.