Search Results bsc_sys_dim_level_cols

Overview

The table BSC_SYS_DIM_LEVEL_COLS is a core data dictionary object within the Oracle E-Business Suite Balanced Scorecard (BSC) module. It defines the structural metadata for dimension levels, which are fundamental to the analytical and reporting capabilities of the BSC application. This table acts as a repository that maps specific database columns to their corresponding dimension levels, thereby establishing the hierarchical and descriptive framework necessary for organizing and aggregating performance measurement data. Its role is critical for the proper configuration and functioning of scorecard dimensions, enabling users to drill down from summary to detail views of key performance indicators (KPIs).

Key Information Stored

The table stores the association between a dimension level and the physical database columns that constitute it. The primary key is a composite of DIM_LEVEL_ID and COLUMN_NAME, ensuring a unique definition for each column within a level. The DIM_LEVEL_ID is a foreign key that links the column to its parent dimension level definition in the BSC_SYS_DIM_LEVELS_B table. The COLUMN_NAME field holds the name of the actual database column (typically from a dimension table or view) that supplies the data for this level. This structure allows a single dimension level to be composed of multiple columns, supporting complex level definitions.

Common Use Cases and Queries

This table is primarily accessed during system configuration, metadata validation, and dynamic report generation. Administrators may query it to audit or document the dimensional model. A common use case is retrieving the full column structure for a specific dimension level to understand its composition or to troubleshoot data visibility issues in scorecards. For example, to list all columns for a given dimension level, one would execute a query joining to BSC_SYS_DIM_LEVELS_B:

• SELECT dlc.column_name, dl.level_name FROM bsc_sys_dim_level_cols dlc JOIN bsc_sys_dim_levels_b dl ON dlc.dim_level_id = dl.dim_level_id WHERE dl.level_name = 'REGION';

Another critical use is during the generation of analytical SQL, where the application dynamically constructs queries based on the columns defined here to support drill-down operations.

Related Objects

The table has a direct and essential relationship with the BSC_SYS_DIM_LEVELS_B table, which holds the master definition of dimension levels. The documented foreign key relationship is:

• BSC_SYS_DIM_LEVELS_B: This table is referenced via the foreign key column BSC_SYS_DIM_LEVEL_COLS.DIM_LEVEL_ID. The join condition is BSC_SYS_DIM_LEVEL_COLS.DIM_LEVEL_ID = BSC_SYS_DIM_LEVELS_B.DIM_LEVEL_ID. This relationship ensures that every column definition is tied to a valid, pre-existing dimension level.

As a foundational metadata table, BSC_SYS_DIM_LEVEL_COLS is indirectly related to various application objects and runtime tables that implement the dimensional hierarchy and KPI calculations defined by this structure.