8.2 Hierarchies and measures in Materialized View
Table 8-1 Hierarchy codes mapping to MVs
| HCY Code | DIM Table | Hierarchy Name | CRAMV01 | CRAMV02 | CRAMV04 | FCT_CRA_AGG_SUMMARY_MV | FCT_CRA_AGG_SUMMARY_MV_APPLN |
|---|---|---|---|---|---|---|---|
| HCRA0001 | DIM_RUN | v_run_execution_id | Y | Y | Y | Y | |
| HCRA0002 | DIM_CURRENCY | v_currency_name | Y | Y | Y | Y | Y |
| HCRA0004 | DIM_STANDARD_PARTY_TYPE | V_STANDARD_PARTY_TYPE_D ESC | Y | Y | Y | Y | |
| HCRA0009 | DIM_PRODUCT_TYPE | v_acct_prod_type_desc | Y | Y | Y | Y | Y |
| HCRA0010 | DIM_STANDARD_PRODUCT_T YPE | v_standard_product_type_desc | Y | Y | Y | Y | Y |
| HCRA0011 | DIM_Product | v_prod_desc | Y | Y | Y | Y | Y |
| HCRA0014 | DIM_CREDIT_STATUS | V_CREDIT_STATUS_DESC | Y | Y | Y | Y | |
| HCRA0016 | DIM_LOB | V_LINE_OF_BUSINESS_DESC | Y | Y | Y | Y | Y |
| HCRA0020 | DIM_WHOLESALE_RETAIL_CAT EGORY | V_WHOLESALE_RETAIL_DESC | Y | Y | Y | Y | |
| HCRA0022 | DIM_DELQ_BAND | V_BAND_SHORT_DESC | Y | Y | Y | Y | |
| HCRA024 | DIM_RUN | FIC_MIS_DATE | Y | Y | Y | Y | Y |
| HCRA025 | DIM_ORG_STRUCTURE | V_ENTITY_CODE | Y | Y | Y | Y | Y |
| HCRA026 | DIM_INDUSTRY | V_D_INDUSTRY_DESC | Y | Y | Y | Y | |
| HCRA028 | DIM_AGE_BOOK_BAND | V_BAND_SHORT_DESC | Y | Y | Y | Y | |
| HCRA029 | DIM_ORG_UNIT | V_ORG_UNIT_CODE | Y | Y | Y | Y | Y |
| HCRA0041 | DIM_LTV_BAND | V_BAND_SHORT_DESC | Y | Y | Y | Y | |
| HCRA043 | DIM_ACCT_INSURED | V_FLAG_CODE | Y | Y | Y | Y | |
| HCRA044 | DIM_EVER_30_DELQ | V_FLAG_DESC | Y | Y | Y | Y | |
| HCRA0045 | DIM_EVER_60_DELQ | V_FLAG_DESC | Y | Y | Y | Y | |
| HCRA0046 | DIM_EVER_90_DELQ | V_FLAG_DESC | Y | Y | Y | Y | |
| HCRA0047 | DIM_EVER_X_DELQ | V_FLAG_DESC | Y | Y | Y | Y | |
| HCRA0049 | DIM_CHANNEL | V_CHANNEL_DESC | Y | Y | Y | Y | Y |
| HCRA0051 | DIM_UTILIZATION_BAND | V_BAND_SHORT_DESC | Y | Y | Y | Y | |
| HCRA0052 | DIM_APPLICATION_STATUS | N_APPLN_STATUS_SKEY | Y | ||||
| HCRA0054 | DIM_DATES | N_DATE_SKEY | Y | ||||
| HCRA0055 | DIM_PROPERTY_TYPE | v_property_type_desc | Y | Y | Y | Y | Y |
| HCRA0056 | DIM_LOCATION | V_LOCATION_DESC | Y | ||||
| HCRA0057 | DIM_APPROVED_DATE | N_DATE_SKEY | Y | ||||
| HCRA0058 | DIM_LENDING_PROGRAM | V_LENDING_PROGRAM_DESC | Y | Y | Y | Y | Y |
| HCRA0059 | DIM_APPRAISAL_TYPE | V_APPRAISAL_TYPE_DESC | Y | ||||
| HCRA0060 | DIM_INTEREST_TYPE | V_INTEREST_TYPE_DESC | Y | Y | Y | Y | Y |
| HCRA0061 | DIM_APPLICATION_TYPE | V_APPLN_TYPE_DESC | Y | ||||
| HCRA0062 | DIM_MORTGAGE_OCCUPANCY | V_MORTGAGE_OCCUPANY_D ESC | Y | Y | Y | Y | Y |
| HCRA0063 | DIM_APP_FUND_DATE | D_CALENDAR_DATE | Y | Y | Y | Y | Y |
| HCRA0064 | DIM_STD_COUNTRY_DIVISION | V_STD_DIVISION_DESCRIPTIO N | Y | Y | Y | Y | Y |
| HCRA0065 | DIM_STD_COUNTRY_REGION | V_STD_REGION_DESCRIPTIO N | Y | Y | Y | Y | Y |
| HCRA0067 | DIM_PREPAY_IND_FLAG | V_FLAG_DESC | Y | Y | Y | Y | |
| HCRA0068 | DIM_REV_GDS_BAND | N_BAND_SKEY | Y | ||||
| HCRA0069 | DIM_REV_TDS_BAND | V_BAND_SHORT_DESC | Y | Y | Y | Y | Y |
| HCRA0070 | DIM_SUBMITTED_DATE | N_DATE_SKEY | Y | ||||
| HCRA0071 | DIM_ORIG_LTV_RANGE | N_BAND_SKEY | Y | ||||
| HCRA0072 | DIM_MAX_ORG_BEACON_SCO RE_RANGE | V_BAND_SHORT_DESC | Y | Y | Y | Y | Y |
| HCRA0073 | DIM_REV_BEACON_SCORE_R ANGE | V_BAND_SHORT_DESC | Y | ||||
| HCRA0074 | DIM_INDICATOR_VALUES | V_INDICATOR_VALUE_DESCRI PTION | Y | Y | Y | Y | |
| HCRA0078 | DIM_HIGH_RISK_ACCOUNT_FL AG | V_FLAG_DESC | Y | Y | Y | Y | |
| HCRA0079 | DIM_CONFORMING_FLAG | V_FLAG_DESC | Y | Y | Y | Y | Y |
| HCRA0080 | DIM_DEVIATION_FLAG | V_FLAG_DESC | Y | Y | Y | Y | Y |
| HCRA0085 | DIM_CRA_ORIG_AMRT_BAND | V_BAND_SHORT_DESC | Y | Y | Y | Y | Y |
| HCRA0086 | DIM_CRA_AMRT_BAND | V_BAND_SHORT_DESC | Y | Y | Y | Y | |
| HCRA0087 | DIM_LTI_BANDS | V_BAND_SHORT_DESC | Y | Y | Y | Y | Y |
| HCRA0088 | DIM_SERVICE_CHANNEL | V_CHANNEL_DESC | Y | Y | Y | Y | Y |
| HCRA0089 | DIM_ADJU_CHANNEL | V_CHANNEL_DESC | Y | Y | Y | Y | Y |
| HCRA0090 | DIM_RESIDENCY_STATUS_IND | V_INDICATOR_VALUE_DESCRI PTION | Y | Y | Y | Y | Y |
| HCRA0091 | DIM_APPLICATION_PURPOSE | V_APPLN_PURPOSE_DESC | Y | ||||
| HCRA0092 | DIM_LOAN_TERM_BAND | V_BAND_SHORT_DESC | Y | Y | Y | Y | |
| HCRA0093 | DIM_ADJUDICATION_TYPE | V_INDICATOR_VALUE_DESCRI PTION | Y | Y | Y | Y | |
| HCRA0094 | DIM_PROPERTY_USE | V_PROPERTY_USE_CODE_DE SC | Y | Y | Y | Y | Y |
| HCRA0095 | DIM_REV_LTV_BAND | V_BAND_SHORT_DESC | Y | Y | Y | Y | Y |
| HCRA0096 | DIM_MAX_CUR_BEACON_SCO RE_RANGE | V_BAND_SHORT_DESC | Y | Y | Y | Y | |
| HCRA0097 | DIM_INSURER | V_INSURER_NAME | Y | Y | Y | Y | |
| HCRA0098 | DIM_COMBINED_HELOC_FLAG | V_FLAG_DESC | Y | Y | Y | Y |
List of Measures
Table 8-2 Mapping of all Dimensions and their descriptions
| Measure Name (CRAMV01) | Measure Name (CRAMV02) | Measure Name (CRAMV04) | Measure Name (FCT_CRA_AGG_SUMMARY_MV) | Measure Name (FCT_CRA_AGG_SUMMARY_MV_APPLN) | Description |
|---|---|---|---|---|---|
| Account Adjusted Outstanding Balance | CRA -MV - CreditLine Adjusted Outstanding Balance | CRA -MV - Adjusted Outstanding | CRA - Adjusted Outstanding Balance | The adjusted remaining balance. | |
| Account Current Credit Limit | CRA -MV - CreditLine Current Credit Limit | CRA -MV - Current Credit Limit | CRA - Account Current Credit Limit | The current maximum credit available. | |
| Account Outstanding Balance | CRA -MV - CreditLine Outstanding Balance | CRA -MV - Outstanding Balance | CRA - Outstanding Balance | The total unpaid balance due. | |
| Account Original Credit Limit | CRA -MV - CreditLine Original Credit Limit | CRA -MV - Original Credit Limit | CRA - Account Original Credit Limit | The initial credit limit set. | |
| Account Exposed Amount | CRA -MV - CreditLine Exposed Amount | CRA -MV - Exposed Amount | CRA - Exposure Amount RCY - MV | The total amount at risk or exposed. | |
| Account Impaired Amount | CRA -MV - CreditLine Exposed Amount | CRA -MV - Impaired Amount | CRA - Impaired Amount | The portion of the balance that is unlikely to be recovered. | |
| Account Delinquent Amount | CRA -MV - CreditLine Delinquent Amount | CRA -MV - Delinquent Amount | CRA - Delinquent Amount | The overdue amount not paid by the due date. | |
| CRA -MV - Account written Off | CRA -MV - Number of Credit Line written Off | CRA -MV - Number of Accounts written Off | CRA - Number of accounts written off | The total amount deemed uncollectible and written off. | |
| CRA -MV - Account Greater than 60 Delinquent Amount | CRA -MV - Credit Line Greater than 60 Delinquent Amount | CRA -MV - Greater than 60 Delinquent Amount | CRA - Greater than 60 Delinquent Amount | The balance overdue by more than 60 days. | |
| CRA -MV - Account Greater than 30 Delinquent Amount | CRA -MV - Credit Line Greater than 30 Delinquent Amount | CRA -MV - Greater than 30 Delinquent Amount | CRA - Greater than 30 Delinquent Amount | The balance overdue by more than 30 days. | |
| CRA -MV - Account Greater than 90 Delinquent Amount | Credit Line Greater than 90 Delinquent Amount | CRA -MV - Greater than 90 Delinquent Amount | CRA - Greater than 90 Delinquent Amount | The balance overdue by more than 90 days. | |
| CRA -MV - Accounts Total Income | CRA -MV - Credit Line Total Income | CRA -MV - Total Income | CRA - Accounts Total Income | The total income associated. | |
| CRA -MV - Account Funded Amount | CRA -MV - Credit Line Funded Amount | CRA -MV - Funded Amount | CRA - Account Funded Amount | The amount of funds provided. | |
| CRA -MV - Account Overdrawn Amount | CRA -MV - Credit Line Overdrawn Amount | CRA -MV - Overdrawn Amount | CRA - Account Overdrawn Amount | The amount by which the balance exceeds the credit limit. | |
| CRA - MV - Account Drawn Amount | CRA - MV - Credit Line Drawn Amount | CRA - MV - Drawn Amount | CRA - Account Drawn Amount | The amount withdrawn or utilized. | |
| CRA - MV - Account Funded Amount MTD | CRA - MV - Credit Line Funded Amount MTD | CRA - MV - Funded Amount MTD | CRA - Account Funded Amount MTD | CRA - Application Funded Amount MTD | The amount funded month-to-date. |
| CRA - MV - Account Funded Amount QTD | CRA - MV - Credit Line Funded Amount QTD | CRA - MV - Funded Amount QTD | CRA - Account Funded Amount QTD | CRA - Application Funded Amount QTD | The amount funded quarter-to-date. |
| CRA - MV - Account Funded Amount YTD | CRA - MV - Credit Line Funded Amount YTD | CRA - MV - Funded Amount YTD | CRA - Account Funded Amount YTD | CRA - Application Funded Amount YTD | The amount funded year-to-date. |
| CRA - MV - Account Current Loan To Income | CRA - MV - Credit Line Current Loan To Income | CRA -MV - Current Loan to Value | CRA - Account Current Loan to Value | The current loan amount as a ratio of the borrower's income. | |
| CRA -MV - Account Current Loan to Value | CRA -MV - CreditLine Current Loan to Value | CRA -MV - Value of Security | CRA - Account Value of Security | The current loan amount as a percentage of the value. | |
| CRA -MV - Account Value of Security | CRA -MV - CreditLine Value of Security | CRA - MV - Current Loan To Income | CRA - Account Current Loan To Income | The value of assets securing. | |
| CRA - MV - Account Revised Origination LTI | CRA - MV - Credit Line Revised Origination LTI | CRA - MV - Revised Origination LTI | CRA - Account Revised Origination LTI | The loan-to-income ratio revised at origination. | |
| CRA -MV - Account ECS Weighted By Outstanding Computation | CRA -MV - Credit Line ECS Weighted By Outstanding Population | CRA -MV - ECS Weighted By Outstanding Computation | Current Weighted Maximum Bureau Score - MV | The economic capital score weighted by outstanding balance. | |
| CRA -MV - Account Origination Credit Limit | CRA -MV - Credit Line Origination Credit Limit | CRA -MV - Origination Credit Limit | CRA - Account Origination Credit Limit | The credit limit established at origin. | |
| CRA -MV - Account Origination Max Credit Score | CRA -MV - Credit Line Origination Max Credit Score | CRA -MV - Origination Max Credit Score | CRA - Account Origination Max Credit Score | The highest credit score at origination. | |
| CRA -MV - Account 1 Year PD | CRA -MV - Credit Line 1 Year PD | CRA -MV - 1 Year PD | CRA - Account 1 Year PD | The probability of default within one year. | |
| CRA - MV - Account Write Off Amount | CRA - MV - Credit Line Write Off Amount | CRA - MV - Write Off Amount | CRA - Account Write Off Amount | The total amount written off. | |
| CRA - MV - Account Write Off Amount MTD | CRA - MV - Credit Line Write Off Amount MTD | CRA - MV - Write Off Amount MTD | CRA - Account Write Off Amount MTD | The amount written off month-to-date. | |
| CRA - MV - Account Write Off Amount QTD | CRA - MV - Credit Line Write Off Amount QTD | CRA - MV - Write Off Amount QTD | CRA - Account Write Off Amount QTD | The amount written off quarter-to-date. | |
| CRA - MV - Account Write Off Amount YTD | CRA - MV - Credit Line Write Off Amount YTD | CRA - MV - Write Off Amount YTD | CRA - Account Write Off Amount YTD | The amount written off year-to-date. | |
| CRA - MV - Account Recovery Amount | CRA - MV - Credit Line Recovery Amount | CRA - MV - Recovery Amount | CRA - Account Recovery Amount | The amount recovered from previously written-off balances. | |
| CRA - MV - Account Recovery Amount MTD | CRA - MV - Credit Line Recovery Amount MTD | CRA - MV - Recovery Amount MTD | CRA - Account Recovery Amount MTD | The recovery amount month-to-date. | |
| CRA - MV - Account Recovery Amount QTD | CRA - MV - Credit Line Recovery Amount QTD | CRA - MV - Recovery Amount QTD | CRA - Account Recovery Amount QTD | The recovery amount quarter-to-date. | |
| CRA - MV - Account Recovery Amount YTD | CRA - MV - Credit Line Recovery Amount YTD | CRA - MV - Recovery Amount YTD | CRA - Account Recovery Amount YTD | The recovery amount year-to-date. | |
| CRA - MV - Account Net Write Off Amount | CRA - MV - Credit Line Net Write Off Amount | CRA - MV - Net Write Off Amount | CRA - Account Net Write Off Amount | The net amount written off after recoveries. | |
| CRA - MV - Account Net Write Off Amount MTD | CRA - MV - Credit Line Net Write Off Amount MTD | CRA - MV - Net Write Off Amount MTD | CRA - Account Net Write Off Amount MTD | The net write off amount month-to-date. | |
| CRA - MV - Account Net Write Off Amount QTD | CRA - MV - Credit Line Net Write Off Amount QTD | CRA - MV - Net Write Off Amount QTD | CRA - Account Net Write Off Amount QTD | The net write off amount quarter-to-date. | |
| CRA - MV - Account Net Write Off Amount YTD | CRA - MV - Credit Line Net Write Off Amount YTD | CRA - MV - Net Write Off Amount YTD | CRA - Account Net Write Off Amount YTD | The net write off amount year-to-date. | |
| CRA -MV - Account Weighted CLTV by Outstanding | CRA - MV - Credit Line Net Write Off Amount YTD | CRA -MV - Weighted CLTV by Outstanding | CRA - Account Weighted CLTV by Outstanding | The combined loan-to-value ratio weighted by outstanding balance. | |
| CRA -MV - Account Principal Portion of Current Payment Amount | CRA -MV - Credit Line Principal Portion of Current Payment Amount | CRA -MV - Principal Portion of Current Payment | CRA - Account Principal Portion of Current Payment Amount | The principal amount in the current payment. | |
| CRA -MV - Account Gross Credit Exposure | CRA -MV - Credit Line Gross Credit Exposure | CRA -MV - Gross Credit Exposure | CRA - Account Gross Credit Exposure | The total credit exposure. | |
| CRA -MV - Account Revised Origination TDS | CRA -MV - Credit Line Revised Origination TDS | CRA -MV - Revised Origination TDS | CRA - Account Revised Origination TDS | The total debt service ratio revised at origination. | |
| CRA -MV - Account Revised Origination GDS | CRA -MV - Credit Line Revised Origination GDS | CRA -MV - Revised Origination GDS | CRA - Account Revised Origination GDS | The gross debt service ratio revised at origination. | |
| CRA -MV - Account Exposure At Default | CRA -MV - Credit Line Exposure At Default | CRA -MV - Exposure At Default | CRA - Exposure at Default | The exposure amount at the point of default. | |
| CRA -MV - Account Principal Outstanding Balance | CRA -MV - Credit Line Principal Outstanding Balance | CRA -MV - Principal Outstanding Balance | CRA - Principal Outstanding Balance | The principal amount still outstanding. | |
| CRA -MV - Number Of Outstanding Accounts | CRA -MV - Number of Outstanding Facilities | CRA -MV - Number of Outstanding Accounts | CRA - Number of outstanding accounts | The total number of balances outstanding. | |
| CRA -MV - Account Weighted Current LTV By Exposure | CRA -MV - Credit Line Weighted Current LTV By Exposure | CRA -MV - Weighted CLTV by Exposure | CRA - Weighted Current LTV By Exposure - MV | The current loan-to-value ratio weighted by exposure. | |
| CRA - MV - Account Current Credit Score | CRA -MV - CreditLine Current Credit Score | CRA -MV - Current Credit Score | CRA - Account Current Credit Score | The current credit score. | |
| CRA - MV - Account Readvanceable Outstanding | CRA -MV - Credit Line Readvanceable Outstanding | CRA -MV - Readvanceable Outstanding | CRA - Account Readvanceable Outstanding | The amount available for future draws or advances. | |
| CRA - MV - Account Weighted Original Interest Rate By Funded Amount | CRA - MV - Credit Line Weighted Original Interest Rate By Funded Amount | CRA - MV - Weighted Original Interest Rate By Funded Amount | CRA - Account Weighted Original Interest Rate By Funded Amount | The original interest rate weighted by the funded amount. | |
| CRA - MV - Account Weighted Current Interest Rate by Outstanding | CRA - MV - Credit Line Weighted Current Interest Rate by Outstanding | CRA - MV - Weighted Current Interest Rate by Outstanding | CRA - Account Weighted Current Interest Rate by Outstanding | The current interest rate weighted by the outstanding balance. | |
| CRA - MV - Account Non Readvanceable Outstanding | CRA -MV - Credit Line Non Readvanceable Outstanding | CRA -MV - Non Readvanceable Outstanding | CRA - Account Non Readvanceable Outstanding | The outstanding amount that cannot be re-advanced. | |
| CRA - MV - Account Current Payment Amount | CRA -MV - Credit Line Current Payment Amount | CRA -MV - Current Payment Amount | CRA - Account Current Payment Amount | The total amount due in the current payment. | |
| CRA - MV - Account 1 Year PD weighted by EAD | CRA -MV - Credit Line 1 Year PD weighted by EAD | CRA -MV - 1 Year PD weighted by EAD | CRA - 1 Year PD weighted by EAD | The one-year probability of default weighted by exposure at default (EAD). |
Table 8-3 Dimensions and their descriptions
| Measure Name (FCT_CRA_AGG_SUMMARY_MV_APPLN) | Description |
|---|---|
| CRA - Number of Funded Applications | The number of applications that have been funded. |
| CRA - Application Origination Credit Limit - MV | The maximum credit limit at the time of application origination. |
| CRA - Weighted Revised GDS by Origination Credit Limit -MV | The gross debt service ratio weighted by the origination credit limit. |
| CRA - Weighted Revised TDS by Origination Credit Limit - MV | The total debt service ratio weighted by the origination credit limit. |
| CRA - Weighted Revised LTV by Origination Credit Limit - MV | The loan-to-value ratio weighted by the origination credit limit. |
| CRA - Weighted ECS by Origination Credit Limit - MV | The economic capital score weighted by the origination credit limit. |
| CRA - Origination Max Bureau Score | The highest bureau score at origination. |
| CRA - House Price | The price of the property. |
| CRA - Number Of First Auto Approval Applications | The number of applications that received first auto-approval. |
| CRA - Number Of First Auto Decline Applications | The number of applications that received first auto-decline. |
| CRA - Application Adjusted Outstanding Balance | The adjusted remaining balance on the application. |
| CRA - Application Revised Origination LTI - MV | The loan-to-income ratio revised at application origination. |
| CRA - Application Revised Origination LTI Weighted Origination Credit Limit - MV | The revised loan-to-income ratio weighted by the origination credit limit. |
| CRA - Total Property Value | The total value of the property. |
| CRA - Number of Approved Applications | The total number of approved applications. |
| CRA - Number of Approved Applications MTD | The number of applications approved month-to-date. |
| CRA - Number of Approved Applications QTD | The number of applications approved quarter-to-date. |
| CRA - Number of Approved Applications YTD | The number of applications approved year-to-date. |
| CRA - Revised LTV | The revised loan-to-value ratio. |
| CRA - Revised GDS | The revised gross debt service ratio. |
| CRA - Revised TDS | The revised total debt service ratio. |
| CRA - Number of Rejected Applications | The total number of rejected applications. |
| CRA - Total Number of Applications | The total number of applications submitted. |
| CRA - Applications Total Income | The total income from applications. |
| CRA - Application Funded Amount | The amount of funds provided to the application. |