3.3.1 Maintaining Hierarchies and Business Processes in Application Policy Materialized View

To extend, modify, or delete the hierarchy or business process definitions for the materialized view FCT_CRA_AGG_SUMMARY_APPLN_POLICY_MV, you can configure the required details in the seeded reference table CRA_APPLN_POLICY_MV_REF_TBL.

Table 3-3 CRA_APPLN_POLICY_MV_REF_TBL - Column Descriptions

Column Name	Description
TABLE_NAME	The materialized view for which the hierarchy or business process is being configured. Use FCT_CRA_AGG_SUMMARY_APPLN_POLICY_MV for application policy mview.
COLUMN_TYPE	The type of column to add. Valid values: HIERARCHY' or BUSINESS_PROCESS.
VAR_NAME	Name of the hierarchy or business process to be added.
VAR_DESC_EXPR	Expression used to populate the HCRA% column. Example: dim_service_channel.v_channel_desc.
VAR_EXPR	Expression used to populate the PK_HCRA% column. Example: n_channel_skey.
DIMENSION_TABLE	Name of the dimension table used. Example: dim_channel.
DIMENSION_ALIAS	Optional alias name if multiple hierarchies share the same dimension table. Example: dim_service_channel.
ANSI_JOIN_COND	Join condition between the transaction and dimension tables.

Example:

• To add a hierarchy (for example, Service Channel):
  • COLUMN_TYPE: 'HIERARCHY'
  • VAR_NAME: Based on available hierarchy code series (e.g., HCRA0088)
  • VAR_DESC_EXPR: dim_service_channel.v_channel_desc
  • VAR_EXPR: n_channel_skey
  • DIMENSION_TABLE: dim_channel
  • DIMENSION_ALIAS: dim_service_channel
  • ANSI_JOIN_COND: Join condition between fsi_cra_application_summary and dim_service_channel
• To add a business process:
  • COLUMN_TYPE: 'BUSINESS_PROCESS'
  • VAR_NAME: Appropriate series name
  • VAR_DESC_EXPR: Required aggregation expression
  • Other columns are filled as per requirement