Search Results bsc_sys_com_dim_levels_u1

Overview

The BSC_SYS_COM_DIM_LEVELS table is a core repository within the Oracle E-Business Suite (EBS) Balanced Scorecard (BSC) module, specifically for versions 12.1.1 and 12.2.2. It serves as a master definition table that stores the hierarchical structure of common dimension levels, which are reusable analytical categories (like Time, Geography, or Organization) used across different performance measurement tabs. Its primary role is to define the order and parent-child relationships between these levels, enabling consistent drill-down navigation and data aggregation in BSC analytics and reports. The table is fundamental to configuring and rendering the dimension selection lists within the application's user interface.

Key Information Stored

The table's structure is designed to capture dimension hierarchy metadata through a concise set of columns. The TAB_ID identifies the specific Balanced Scorecard tab or module component utilizing the dimension. The DIM_LEVEL_INDEX and DIM_LEVEL_ID define the sequence and unique identifier, respectively, for a given level within its hierarchy on that tab. The hierarchical relationship is established via PARENT_LEVEL_INDEX and PARENT_DIM_LEVEL_ID, which point to the immediate parent level's index and identifier. This design allows for the efficient reconstruction of multi-level dimension trees. The unique index (BSC_SYS_COM_DIM_LEVELS_U1) on TAB_ID and DIM_LEVEL_INDEX enforces data integrity for the level ordering within each tab.

Common Use Cases and Queries

This table is primarily accessed for administrative configuration and analytical reporting. A common use case involves querying the complete hierarchy of a common dimension for a specific tab to understand its drill-down path, such as from Year to Quarter to Month. The standard query pattern, as indicated in the ETRM, retrieves all columns. For practical application, one might join this table to dimension master tables (like BSC_SYS_DIM_LEVELS_B/TL) to get descriptive names. A typical analytical query would be:

• Retrieve Dimension Hierarchy for a Tab:
  SELECT bscl.TAB_ID, bscl.DIM_LEVEL_INDEX, dlb.NAME, bscl.PARENT_LEVEL_INDEX
  FROM BSC.BSC_SYS_COM_DIM_LEVELS bscl,
      BSC.BSC_SYS_DIM_LEVELS_B dlb
  WHERE bscl.DIM_LEVEL_ID = dlb.DIM_LEVEL_ID
  AND bscl.TAB_ID = :p_tab_id
  ORDER BY bscl.DIM_LEVEL_INDEX;

This supports report development, data validation, and troubleshooting the presentation of dimension lists in the BSC interface.

Related Objects

Based on the provided dependency information, the BSC_SYS_COM_DIM_LEVELS table is referenced by the APPS synonym of the same name (APPS.BSC_SYS_COM_DIM_LEVELS), which is the standard access point for all application code. While the ETRM excerpt does not list foreign key relationships, in practice, the DIM_LEVEL_ID and PARENT_DIM_LEVEL_ID columns logically relate to the primary key of the dimension level definition table, typically BSC_SYS_DIM_LEVELS_B. The TAB_ID would logically reference a tab definition table within the BSC schema. The table's unique index (BSC_SYS_COM_DIM_LEVELS_U1) is the primary key constraint, and the non-unique index (BSC_SYS_COM_DIM_LEVELS_N1) on DIM_LEVEL_ID optimizes queries filtering by that identifier.