5.3.6 Preseeded Glossaries

After creating the sandbox a default glossary named EST, DL, and DIMENSION is created.

Note:

Ensure that you create all the glossary terms related to STSA under the respective glossary because STSA does not support any user-created glossaries.

The following list of glossary terms are created under the EST and DIMENSION global glossary.

Table 5-1 List of glossary terms created in STSA

Name Display Name Description
DATEMANAGEMENT Date Management Map all required date columns
DIMENSION Dimension Create Dimension Glossary Terms
CREDIT_RATING Credit Rating Dimension This is credit rating dimension sample glossary term
DATE Date Dimension Map Date Columns
PRODUCT Product Dimension This is product dimension sample glossary term
CURRENCY Currency Dimension This is currency dimension sample glossary term
PDTS_VARIABLE_NAME PDTS Variable Name Map PDTS Variable Name Column
PDTS_VARIABLE_DESCRIPTION PDTS Variable Description Map PDTS Variable Description Column
PDST_SCENARIO_CODE PDST Scenario Code Map PDST Scenario Code Column
PDTS_DATA_SOURCE PDTS Data Source Map PDTS Data Source Column
PDTS_DEFAULT_PROBABILITY_TYPE PDTS Default Probability Type Map PDTS Default_Probability Type Column
PDTS_DETAIL_ID PDTS Detail Id Map PDTS Detail Id Column
PDTS_FICMISDATE PDTS FICMISDATE Map PDTS FICMISDATE Column
PDTS_FREQUENCY PDTS Frequency Frequency Map PDTS Frequency Frequency Column
PDTS_FREQUENCY_UNIT PDTS Frequency Unit Map PDTS Frequency Unit Column
PDTS_ID PDTS Id Map PDTS Id Column
PDTS_TERM_POINTS PDTS Term Points Map PDTS Term Points Column
PDTS_TYPE PDTS Type Type Map PDTS Type Type Column
PDTS_TYPE_HIERARCHY PDTS Type Hierarchy Map PDTS Type Hierarchy Column
PDTS_PERCENT PDTS Percent Map PDTS Percent Column
CCFTS_VARIABLE_NAME CCFTS Variable Name Map CCFTS Variable Name Column
CCFTS_VARIABLE_DESCRIPTION CCFTS Variable Description Map CCFTS Variable Description Column
CCFTS_CATEGORY CCFTS Category Map CCFTS Category Column
CCFTS_SCENARIO_CODE CCFTS Scenario Code Map CCFTS Scenario Code Column
CCFTS_DATA_SOURCE CCFTS Data Source Map CCFTS Data Source Column
CCFTS_DEFAULT_PROBABILITY_TYPE CCFTS Default Probability Type Map CCFTS Default Probability Type Column
CCFTS_DETAIL_ID CCFTS Detail Id Map CCFTS Detail Id Column
CCFTS_FICMISDATE CCFTS FICMISDATE Map CCFTS FICMISDATE Column
CCFTS_FREQUENCY_UNIT CCFTS Frequency Unit Map CCFTS Frequency Unit Column
CCFTS_ID CCFTS Id Map CCFTS Id Column
CCFTS_TERM_POINTS CCFTS Term Points Map CCFTS Term Points Column
CCFTS_TERM_VALUE CCFTS Term Value Map CCFTS Term Value Column
CCFTS_TYPE CCFTS Type Type Map CCFTS Type Type Column
CCFTS_TYPE_HIERARCHY CCFTS Type Hierarchy Map CCFTS Type Hierarchy Column
LGDTS_VARIABLE_NAME LGDTS Variable Name Map LGDTS Variable Name Column
LGDTS_VARIABLE_DESCRIPTION LGDTS Variable Description Map LGDTS Variable Description Column
LGDTS_CATEGORY LGDTS Category Map LGDTS Category Column
LGDTS_SCENARIO_CODE LGDTS Scenario Code Map LGDTS Scenario Code Column
LGDTS_DATA_SOURCE LGDTS Data Source Map LGDTS Data Source Column
LGDTS_DEFAULT_PROBABILITY_TYPE LGDTS Default Probability Type Map LGDTS Default_Probability Type Column
LGDTS_DETAIL_ID LGDTS Detail Id Map LGDTS Detail Id Column
LGDTS_FICMISDATE LGDTS FICMISDATE Map LGDTS FICMISDATE Column
LGDTS_FREQUENCY_UNIT LGDTS Frequency Unit Map LGDTS Frequency Unit Column
LGDTS_ID LGDTS Id Map LGDTS Id Column
LGDTS_TERM_POINTS LGDTS Term Points Map LGDTS Term Points Column
LGDTS_TERM_VALUE LGDTS Term Value Map LGDTS Term Value Column
LGDTS_TYPE LGDTS Type Type Map LGDTS Type Type Column
LGDTS_TYPE_HIERARCHY LGDTS Type Hierarchy Map LGDTS Type Hierarchy Column
TM_VARIABLE_NAME TM Variable Name Map TM Variable Name Column
TM_VARIABLE_DESCRIPTION TM Variable Description Map TM Variable Description Column
TM_COMPUTATION_BASIS TM Computation Basis Map TM Computation Basis Column
TM_COMPUTATION_INDICATOR TM Computation Indicator Map TM Computation Indicator Column
TM_DETAIL_ID TM Detail Id Map TM Detail Id Column
TM_DIM_SEGMENT_ID TM Dim Segment Id Map TM Dim Segment Id Column
TM_FICMISDATE TM FICMISDATE Map TM FICMISDATE Column
TM_FREQUENCY TM Frequency Map TM Frequency Column
TM_ID TM Id Map TM Id Column
TM_NSEGMENT_ID TM Nsegment Id Map TM Nsegment Id Column
TM_ROLL_RATE_APPLICABILITY_INDICATOR TM Roll Rate Applicability Indicator Map TM Roll Rate Applicability Indicator Column
TM_TYPE TM Type Type Map TM Type Type Column
TM_DIM_VSEGMENT_ID TM Dim Vsegment Id Map TM Dim Vsegment Id Column
TM_DIM_NSEGMENT_ID TM Dim Nsegment Id Map TM Dim Nsegment Id Column
TM_SOURCE_CREDIT_RISK_BASIS_CODE TM Source Credit Risk Basis Code Map TM Source Credit Risk Basis Code Column
TM_TARGET_CREDIT_RISK_BASIS_CODE TM Target Credit Risk Basis Code Map TM Target Credit Risk Basis Code Column
TM_TRANSITION_RATE TM Transition Rate Map Transition Rate Column
ER_FICMISDATE ER FICMISDATE Map ER FICMISDATE Column
ER_FROM_CURRENCY ER FROM CURRENCY Map ER FROM CURRENCY Column
ER_TO_CURRENCY ER TO CURRENCY Map ER TO CURRENCY Column
ER_RATE_DATA_SORUCE_CODE ER RATE DATASOURCE CODE Map ER RATE DATASOURCE CODE Column
ER_TENOR ER TENOR Map ER TENOR Column
Here is a list of all the predefined glossary terms and their details:
  • DIMENSION Glossary Term - create all your STSA dimension glossary terms for example currency, date, product under this glossary term.

    The DIMENSION glossary is used in variable, portfolios and scenario objects of STSA to map various glossary terms to dimensions.

    For example, you have a dimension table known as DIM_CURRENCY and a column inside that known as V_ISO_CURRENCY_CD.

    You have a stage table called STG_INVESTMENTS and a column inside that known as V_CCY_CODE.

    Another stage table called STG_MM_CONTRACTS and a column inside that known as V_CCY_CODE.

    A glossary term named DIMENSION is associated with dimension tables. This term must be tagged at the dimension table level.

    The following table provides the table and column names along with the dimension tags.

    Table 5-2 Dimension Glossary Term Mapping

    Table Name Column Name Tag
    DIM_CURRENCY V_ISO_CURRENCY_CD DIMNESION
    STG_INVESTMENTS V_CCY_CODE  
    STG_MM_CONTRACTS V_CCY_CODE  

    You can now map all these tables and columns to the Currency glossary term created under DIMENSION glossary using the Glossary term mapping to table and columns.

    When the Currency glossary term is mapped to both the tables and columns, the currency term points to both STG_INVESTMENTS table, V_CCY_CODE column and STG_MM_CONTRACTS table and V_CCY_CODE column where, Currency=DIM_CURRENCY.V_ISO_CURRENCY_CD=STG_INVESTMENTS.V_CCY_CODE=STG_MM_CONTRACTS.V_CCY_CODE

    The following table shows the result of the tables after mapping the currency glossary term.

    Table 5-3 Results of Dimension Glossary Term Mapping

    Source Table Source Column Dimension Table Dimension Column
    STG_INVESTMENTS V_CCY_CODE DIM_CURRENCY V_ISO_CURRENCY_CD
    STG_MM_CONTRACTS V_CCY_CODE DIM_CURRENCY V_ISO_CURRENCY_CD
  • DATEMANAGEMENT glossary term- use this glossary term to map the date columns of all the tables in the Data Catalog that are required for stress testing.
    To compute stress testing or identify the risks in future, the data is replicated from the base reference date along with futuristic dates. And since some of the other dates and calculations like renewal date, maturity date, expiry date also have to be calculated with respect to the future dates and cannot be the same as of the base reference date. If the dates are not updated, this would result to exposures with positive current outstanding whose maturity date has been passed or facility has been expired. Since the production data is of a past date, the stress testing results date is calculated for a future date. And, this calculation can be done in two ways:
    • Default Date Management - This is the standard method to move the date from the past to a future date. This is calculated as, STG_INVESTMENTS.D_MATURITY_DATE + (Day difference between Reference Date and Pseudo Date)

      Note:

      You can define a pseudo future date in the configuration file.
    • STSA Date Management - This is additional calculation to move the date from past to future date with combination of default date management.

      If the day difference between STG_INVESTMENTS.D_MATURITY_DATE and Reference Date > 0, then, STSA Date Management = Default Date Management + Months between Pseduo Date and Reference Date.

      If the day difference between STG_INVESTMENTS.D_MATURITY_DATE and Reference Date < 0, then, STSA Date Management = Default Date Management + Day difference between Pseudo Date and Projected MIS Date.

    The DATEMANAGEMENT glossary is used in analysis configuration, scenario and project objects of STSA.

    For example, a STG_INVESTMENTS table has a D_MATURITY_DATE column.

    A STG_LOAN_CONTRACTS table has a D_MATURITY_DATEcolumn.

    And, we have a DIM tag to identify the tags.

    The following information is represented in a tabular format.

    Table 5-4 DATEMANAGEMENT glossary term mapping

    Table Name Column Name
    DIM_DATE D_CALENDAR_DATE
    STG_INVESTMENTS FIC_MIS_DATE

    You can map all these tables and columns to the DATEMANAGEMENT glossary term using the Glossary term mapping to table and columns.

    After mapping the DATE glossary term, the table is updated as DATE=DIM_DATE.D_CALENDAR_DATE=STG_INVESTMENTS.FIC_MIS_DATE

    Table 5-5 Result for DATEMANAGEMENT glossary term mapping

    Table Name Column Name Tag Glossary Term
    DIM_DATE D_CALENDAR_DATE DIM DATE
  • DL glossary term - this glossary term represents the download specification or expectation for the given process. This is where you can map all the tables required for a process on Data Catalog at the table level. For example, you have a STG_PD_TERM_STRUCTURE table, then you can map the DL glossary to this table using the pipeline id such as {Pipeline_ID}_DL.
    For example, we have two tables known as STG_PD_TERM_STRUCTURE and STG_PD_TERM_STRUCTURE_DTL. Assuming we want to link a process for these two tables, we can do this using the DL glossary term using the process or model id in the following format: {Pipeline_ID}_DL.

    Note:

    You can get the process or model id from OpenMetadata.
    The following table represents the DL glossary term mapping.

    Table 5-6 Result of DL glossary term mapping

    Table Name Glossary Term
    STG_PD_TERM_STRUCTURE {Pipeline_ID}_DL
    STG_PD_TERM_STRUCTURE_DTL {Pipeline_ID}_DL

    After creating the DL glossary terms, the sub-glossary terms with INPUT_<GLOSSARYTERM> and the OUTPUT_<GLOSSARYTERM> sub-glossary terms are created.

    For example, after creating a DL glossary term, INPUT_DIMESNION and OUTPUT_DIMENSION sub-glossary terms are created.

    The DL glossary is used in metrics and auto-sequencing of process and model objects.

  • IRC glossary term - this glossary term also known as a yield curve, is a graphical representation of the relationship between interest rates (or yields) and different maturities (time periods).
    The following table lists the glossary terms that are created for IRC.

    Table 5-7 IRC Glossary terms and their descriptions

    Name DisplayName Description
    IRC_CODE IRC Code Map IRC Code
    IRC_CURRENCY_CODE IRC Currency Map IRC Currency
    IRC_DESCRIPTION IRC Description Map IRC Description
    IRC_FR_DI_FIC_MIS_DATE IRC FIC MIS Date Map IRC FIC MIS Date
    IRC_FR_DI_RATE Interest Rate Map Interest Rate
    IRC_FR_DI_RATE_CODE Interest Rate Code Map Interest Rate Code
    IRC_FR_DI_RATE_MULT Interest Rate Multiplier Map Interest Rate Multiplier
    IRC_FR_DI_RATE_TERM Interest Rate Term Map Interest Rate Term
    IRC_MULT IRC Multiplier Map IRC Multiplier
    IRC_NAME IRC Name Map IRC Name
    IRC_TERM IRC Term Map IRC Term
    IRC_TERM_CODE IRC Term Code Map IRC Term Code

    For example, consider three IRC tables, EST_IRC_IRCS, EST_IRC_RATE_TERMS and EST_IRC_DIRECT_INPUT with the following columns.

    Table 5-8 Details about the EST_IRC_IRCS table

    COLUMN_NAME DATA_TYPE NULLABLE
    N_INTEREST_RATE_CD NUMBER(10,0) No
    V_IRC_NAME VARCHAR2(100 CHAR) No
    V_IRC_DESC VARCHAR2(1000 CHAR) Yes
    ISO_CURRENCY_CD VARCHAR2(30 CHAR) Yes

    Table 5-9 Details about EST_IRC_RATE_TERMS table

    COLUMN_NAME DATA_TYPE NULLABLE
    N_INTEREST_RATE_CD NUMBER(10,0) No
    N_INTEREST_RATE_TERM NUMBER(5,0) No
    C_INTEREST_RATE_TERM_MULT CHAR(1 CHAR) No

    Table 5-10 Details about EST_IRC_DIRECT_INPUT table

    COLUMN_NAME DATA_TYPE NULLABLE
    D_FIC_MIS_DATE DATE No
    N_INTEREST_RATE_CD NUMBER(10,0) No
    N_INTEREST_RATE_TERM NUMBER(5,0) No
    C_INTEREST_RATE_TERM_MULT CHAR(1 CHAR) No
    N_INTEREST_RATE NUMBER(10,6) No
    Then, map the following table column values to the IRC glossary terms this way:

    Table 5-11 Mapping table and column to IRC variables

    TABLE_NAME COLUMN_NAME GLOSSARY_TERM
    EST_IRC_DIRECT_INPUT D_FIC_MIS_DATE IRC_FR_DI_FIC_MIS_DATE
    EST_IRC_DIRECT_INPUT N_INTEREST_RATE_CD IRC_FR_DI_RATE_CODE
    EST_IRC_DIRECT_INPUT N_INTEREST_RATE_TERM IRC_FR_DI_RATE_TERM
    EST_IRC_DIRECT_INPUT C_INTEREST_RATE_TERM_MULT IRC_FR_DI_RATE_MULT
    EST_IRC_DIRECT_INPUT N_INTEREST_RATE IRC_FR_DI_RATE
    EST_IRC_IRCS N_INTEREST_RATE_CD IRC_CODE
    EST_IRC_IRCS V_IRC_NAME IRC_NAME
    EST_IRC_IRCS V_IRC_DESC IRC_DESCRIPTION
    EST_IRC_IRCS ISO_CURRENCY_CD IRC_CURRENCY_CODE
    EST_IRC_RATE_TERMS N_INTEREST_RATE_CD IRC_TERM_CODE
    EST_IRC_RATE_TERMS N_INTEREST_RATE_TERM IRC_TERM
    EST_IRC_RATE_TERMS C_INTEREST_RATE_TERM_MULT IRC_MULT

Also, you can link different tables in different workspace to the same glossary term.

For example, you have two different workspaces, SANDBOX1 and SANDBOX2 with different tables.

SANDBOX1 workspace has two tables:

  • TABLE1.COLUMN1
  • TABLE1.COLUMN2

SANDBOX2 workspace has two tables:

  • TABLE2.COLUMN1
  • TABLE2.COLUMN2

You can map a single glossary term to both workspaces and to all four tables. In the analysis configuration and project creation, the tables and columns are picked up using the workspace linked to the glossary.

Map the physical tables and its columns to the preseeded glossary terms mentioned in the above table. For more information, see the Uploading Glossary Terms Mapping to Table Columns and Tags section.