Search Results bsc_sys_dim_levels_b




Overview
Explore in AI Chat

Overview

The BSC_SYS_DIM_LEVELS_B table is a core repository for dimension level definitions within the now-obsolete Balanced Scorecard (BSC) module of Oracle E-Business Suite (EBS) versions 12.1.1 and 12.2.2. It serves as the master table for storing the structural metadata that defines hierarchical levels within analytical dimensions, such as Time, Geography, or Organization. This table is foundational to the BSC architecture, enabling the configuration of how Key Performance Indicators (KPIs) are sliced, aggregated, and reported across different levels of granularity. Its central role is evidenced by its extensive foreign key relationships with numerous configuration and property tables in the BSC schema.

Key Information Stored

While the provided ETRM metadata does not list specific columns beyond the primary key, the table's description and relationships define its core data. The primary key, DIM_LEVEL_ID, uniquely identifies each dimension level record. Based on standard EBS patterns and the table's purpose, it is logical to infer the table stores attributes such as the associated dimension identifier (DIMENSION_ID), a level sequence or code (LEVEL_CODE), a name or short name, and flags controlling behavior (e.g., for aggregation, data entry, or hierarchy definition). The '_B' suffix indicates it is the base table for a translated entity, with language-specific names stored in the related BSC_SYS_DIM_LEVELS_TL table.

Common Use Cases and Queries

Primary use cases involve the setup and diagnostic reporting of the BSC dimensional model. Administrators would query this table to understand the configured hierarchy levels for a given dimension or to troubleshoot KPI data visibility issues. A common diagnostic query would join to related tables to map a dimension's full hierarchy structure. For instance, to list all levels for a specific dimension and their properties, one might use a pattern similar to the following, noting that actual column names are inferred:

  • SELECT b.DIM_LEVEL_ID, b.LEVEL_CODE, tl.NAME, b.DIMENSION_ID FROM BSC_SYS_DIM_LEVELS_B b, BSC_SYS_DIM_LEVELS_TL tl WHERE b.DIM_LEVEL_ID = tl.DIM_LEVEL_ID AND b.DIMENSION_ID = :dim_id ORDER BY b.SEQUENCE_NUM;

Another critical use case is validating the integrity of dimension level references before data loads or during system upgrades, by checking for orphaned records in foreign key tables like BSC_KPI_DIM_LEVEL_PROPERTIES.

Related Objects

The BSC_SYS_DIM_LEVELS_B table is a central hub in the BSC data model, as confirmed by the extensive foreign key relationships documented in the ETRM. Key related objects include:

  • BSC_SYS_DIM_LEVELS_TL: Translation table joined on DIM_LEVEL_ID for multilingual support.
  • BSC_KPI_DIM_LEVEL_PROPERTIES: Stores KPI-specific properties applicable to a particular dimension level (DIM_LEVEL_ID).
  • BSC_SYS_COM_DIM_LEVELS: References the table twice, for both DIM_LEVEL_ID and PARENT_DIM_LEVEL_ID, defining common dimension level hierarchies.
  • BSC_SYS_DIM_LEVEL_RELS: Also references the table twice (DIM_LEVEL_ID, PARENT_DIM_LEVEL_ID), managing relationships between dimension levels.
  • BSC_SYS_DIM_LEVEL_COLS: Defines the database columns that correspond to a dimension level (DIM_LEVEL_ID).
  • BSC_SYS_FILTERS & BSC_SYS_FILTERS_VIEWS: Reference DIM_LEVEL_ID to attach filters to specific dimension levels.
  • BSC_SYS_DIM_LEVELS_BY_GROUP: Associates dimension levels with groups (DIM_LEVEL_ID).