Search Results create_virtual_zero_code_level
Overview
APPS.BSC_AW_ADAPTER_DIM is an internal Oracle E-Business Suite package that belongs to the Balanced Scorecard (BSC) analytics framework, historically marketed as Daily Business Intelligence and Oracle Enterprise Planning and Budgeting. It functions as an adapter layer between the EBS transactional data model and the Oracle Analytical Workspace (AW), which is the OLAP engine that materializes the dimensional model used by Balanced Scorecard dashboards and KPI reporting.
The package is responsible for translating EBS dimension definitions, stored in the BSC metadata tables, into physical analytical workspace constructs. It reads dimension, level, and relationship definitions from BSC_OLAP_OBJECT and related tables, generates the corresponding AW programs through DBMS_SQL, executes them, and records the resulting mappings. The g_commands and g_corrected_dim PL/SQL tables declared at the specification level show that the package caches batches of generated AW commands and then executes them as a set, which is essential for performance when rebuilding an entire dimensional model. The g_debug and g_stmt globals support diagnostic tracing, and g_exception provides a controlled error path for adapter failures.
Key Procedures and Functions
The documented API surface comprises 75 procedures and functions. The most significant entry points include:
- CREATE_DIM, CREATE_DIM_OBJECTS, CREATE_DIM_FOR_LEVELS and RESET_DIMENSION_R drive creation and regeneration of the dimension definition, including the recursive variant used for parent-child structures.
- GET_ALL_DIM_LEVELS, GROUP_LEVELS_INTO_SETS, ASSIGN_SET_TO_LEVEL and SET_LEVEL_POSITION orchestrate level discovery, grouping of levels into dimension sets, and positional ordering. The level record type carries position, property, seed values, primary-key metadata, filter and limit variables, and snowflake relation names.
- CREATE_VIRTUAL_ZERO_CODE_LEVEL builds the synthetic zero-code level that represents the unassigned or roll-up node in a dimension hierarchy. The zero_levels_rec type associates a zero-code level name with the real child level for which it substitutes, which is how the adapter ensures referential integrity when facts carry no valid code.
- CREATE_VIRTUAL_REC_LEVEL supports recursive or ragged dimension hierarchies.
- CREATE_CCDIM, CREATE_LEVEL_NAME_DIM and CREATE_RELATION create companion dimensions and relationships required by the OLAP model.
- CREATE_DIM_PROGRAM and CREATE_DIM_PROGRAM_REC emit the AW program text, while IMPLEMENT_DIM_AW executes it.
- DROP_DIM, DROP_KPI_OBJECTS_FOR_DIM and DROP_OLD_DIM_FOR_LEVEL provide cleanup and supersession.
- GET_KPI_FOR_DIM returns KPI associations for a given dimension.
Tables Accessed
The package references BSC_OLAP_OBJECT and BSC_OLAP_OBJECT_RELATION through APPS synonyms, reading dimension and level metadata and writing back the generated object identifiers and relationships. DBMS_SQL is used both as the dynamic SQL engine and as the source of the varchar2_table and PLITBLM collections used for command batching. No base transactional tables are directly modified; the adapter operates purely on the BSC metadata layer and the resulting analytical workspace objects.
Usage Notes
BSC_AW_ADAPTER_DIM is not exposed to end users. It is invoked indirectly by higher-level Balanced Scorecard administration programs, such as dimension and KPI refresh or full analytics rebuild requests, and it is referenced by ten other packages within the BSC schema. Releases 12.1.1 and 12.2.2 retain the same architecture, with the adapter running under the APPS account and depending on a valid AW session. Customizations should call the public wrapper procedures rather than individual internal routines, and any debugging should enable g_debug before invoking dimension rebuilds.