Search Results bsc_kpi_dim_levels_b

Overview

The BSC_KPI_DIM_LEVELS_B table is a core data definition table within the Oracle E-Business Suite Balanced Scorecard (BSC) module. It serves as the base table for storing the structural hierarchy of dimension levels associated with Key Performance Indicator (KPI) dimension sets. In essence, it defines the granularity at which a KPI can be analyzed across different business dimensions, such as time, geography, or organization. This table is fundamental to the multidimensional analysis capabilities of the Balanced Scorecard, enabling performance data to be rolled up and drilled down across predefined hierarchical levels. Its status is VALID across both the 12.1.1 and 12.2.2 versions of Oracle EBS.

Key Information Stored

The table's primary key uniquely identifies each dimension level record through a composite of three columns: DIM_LEVEL_INDEX, DIM_SET_ID, and INDICATOR. The DIM_SET_ID links the level to its parent dimension set defined in BSC_KPI_DIM_SETS_TL. The INDICATOR column associates the dimension level with a specific KPI. The DIM_LEVEL_INDEX is a numeric identifier that dictates the level's position within the dimension hierarchy. While the provided metadata does not list all columns, typical columns in such a base table would include creation date, created by, last update date, and last updated by for auditing purposes. The descriptive, translatable names for these levels are stored in the related BSC_KPI_DIM_LEVELS_TL table.

Common Use Cases and Queries

This table is primarily accessed during the configuration of KPI dimensions and during runtime when generating scorecard reports that require data aggregation or disaggregation. A common administrative use case is querying all dimension levels for a specific KPI to validate or document its analytical hierarchy. A typical query would join to the translation table to retrieve user-friendly names:

• SELECT tl.NAME, b.DIM_LEVEL_INDEX FROM BSC_KPI_DIM_LEVELS_B b, BSC_KPI_DIM_LEVELS_TL tl WHERE b.INDICATOR = :p_kpi_id AND b.DIM_SET_ID = :p_dim_set_id AND b.DIM_LEVEL_INDEX = tl.DIM_LEVEL_INDEX AND b.INDICATOR = tl.INDICATOR AND b.DIM_SET_ID = tl.DIM_SET_ID ORDER BY b.DIM_LEVEL_INDEX;

From a reporting perspective, this table's structure is used to construct the logic for drill-down paths within the Balanced Scorecard interface, determining the next available level of detail when a user drills on a KPI value.

Related Objects

The BSC_KPI_DIM_LEVELS_B table sits at the center of a key relationship cluster within the BSC schema, as confirmed by the documented foreign keys.

• Referenced Foreign Key (Parent): The table references BSC_KPI_DIM_SETS_TL via the DIM_SET_ID column. This links a dimension level to its overarching dimension set definition.
• Referencing Foreign Keys (Child Tables):
  • BSC_KPI_DIM_LEVELS_TL: Stores the translated names for the dimension levels. It joins to the base table using the composite primary key (DIM_LEVEL_INDEX, INDICATOR, DIM_SET_ID).
  • BSC_KPI_DIM_LEVELS_USER: Likely stores user-specific preferences or security rules related to dimension levels. It also joins on the composite primary key (DIM_LEVEL_INDEX, INDICATOR, DIM_SET_ID).