Search Results create_dbi_dim_tables

Overview

APPS.BSC_UPDATE_DIM is a PL/SQL package belonging to the Oracle Balanced Scorecard (BSC) application module within Oracle E-Business Suite. Its principal business function is the maintenance and propagation of dimensional metadata used by the Balanced Scorecard analytics engine. In the Oracle BSC architecture, dimensions represent the analytical axes — such as organization, time, customer, or product hierarchy — along which key performance indicators (KPIs) are aggregated and reported. The BSC_UPDATE_DIM package is responsible for detecting changes to dimension definitions, synchronizing those changes into the underlying database objects, and rebuilding the supporting database structures (tables, views, and materialized view sources) that store denormalized dimension data.

The package is a core component of the metadata layer that bridges the BSC repository tables and the physical structures used for reporting and analysis. It is classified as OTHER in the API registry, indicating that it is an internal, engine-level utility rather than a public, supported extension API. It operates under the APPS schema and holds a VALID status in the documented environment.

Key Procedures and Functions

The package exposes 52 documented procedures and functions. These fall into several functional groups:

• Change detection: ANY_ITEM_CHANGED_ANY_RELATION determines whether dimension items or their relationships have been modified, providing the trigger logic that drives incremental dimension synchronization.
• Table and view generation: CREATE_DBI_DIM_TABLES, CREATE_DBI_DIM_TEMP_TABLES, CREATE_AW_DIM_TEMP_TABLES, and CREATE_AW_DIM_TEMP_TABLES_AT build the physical dimension tables and temporary staging structures required by the DBI (Daily Business Intelligence) and Analytic Workspace (AW) layers.
• Metadata retrieval: GET_ALL_DBI_DIM_DATA, GET_DBI_DIM_DATA, GET_DBI_DIM_PARENT_COLUMNS, GET_DBI_DIM_VIEW_NAME, GET_AUX_FIELDS_DIM_TABLE, and GET_CHILD_DIMENSIONS retrieve dimension definitions, parent-child column mappings, view names, auxiliary fields, and hierarchical relationships for downstream consumers.
• Denormalization: DENORM_ENI_ITEM_VBH_CAT, DENORM_ENI_ITEM_ITM_CAT, DENORM_HRI_PER_USRDR_H, and DENORM_PJI_ORGANIZATIONS flatten source EBS entities (items, categories, users, organizations) into dimensional form.
• Deletion and cleanup: DELETE_CODES_CASCADE, DELETE_CODES_CASCADEMN, and DELETE_KEY_VALUES_IN_TABLES remove dimension codes and key values, propagating deletions across dependent tables.
• Usage analysis: DIMENSION_USED_IN_AW_KPI and DIMENSION_USED_IN_MV_KPI check whether a given dimension participates in an Analytic Workspace KPI or a materialized view KPI, guarding against unsafe modifications.

Tables Accessed

Through APPS synonyms, the package reads and writes the BSC metadata repository. BSC_DB_TABLES, BSC_DB_TABLES_COLS, and BSC_DB_TABLES_RELS hold the definitions of dimension tables, their columns, and their relationships. BSC_DB_VALIDATION stores validation rules. BSC_KPIS_B, BSC_KPI_ANALYSIS_MEASURES_B, BSC_KPI_DIM_LEVELS_B, and BSC_KPI_PROPERTIES define KPIs, their measures, and the dimension levels they reference — used when assessing dimension usage. BSC_SYS_DIM_LEVELS_B, BSC_SYS_DIM_LEVEL_COLS, BSC_SYS_DIM_LEVEL_RELS, and BSC_SYS_COM_DIM_LEVELS define system dimension levels and their column bindings. BSC_SYS_BENCHMARKS_B and BSC_SYS_BENCHMARKS_TL store benchmark definitions. AD_DDL provides DDL execution services used when creating and altering dimension table structures.

Usage Notes

BSC_UPDATE_DIM is not intended for direct invocation by end users or custom code. It is invoked internally by the BSC engine, specifically through packages that reference it, including BSC_METADATA, BSC_METADATA_OPTIMIZER_PKG, BSC_UPDATE, BSC_UPDATE_BASE, BSC_UPDATE_BASE_V2, BSC_UPDATE_VAL, BSC_UPDATE_LOCK, BSC_DIMENSION_EDIT, BSC_MIGRATION, and BSC_BSC_ADAPTER. The BSC_DIMENSION_EDIT reference indicates that dimension edits performed through the administrative HTML interface trigger this package to regenerate underlying structures. Because of its dependency on BSC_UPDATE_UTIL and its role in creating and dropping database objects, it is typically executed within controlled maintenance windows or under the concurrency safeguards provided by BSC_UPDATE_LOCK. Any modification to dimension metadata should be followed by a corresponding BSC engine refresh so that this package can rebuild the affected tables and views.