Search Results bsc_kpi_dim_level_properties




Overview
Explore in AI Chat

Overview

The BSC_KPI_DIM_LEVEL_PROPERTIES table is a core data structure within the now-obsolete Balanced Scorecard (BSC) module of Oracle E-Business Suite (EBS) releases 12.1.1 and 12.2.2. It functions as a junction table, establishing and storing the specific properties linking Key Performance Indicators (KPIs) to dimensional analysis hierarchies. Its primary role is to define which dimension levels (e.g., Time: Month, Quarter, Year; Organization: Department, Division, Company) are applicable and configurable for a given KPI within a specific dimension set. This enables multidimensional analysis and drill-down capabilities for scorecard metrics. Notably, the provided metadata indicates this table was "Not implemented in this database," suggesting it may exist in the data dictionary but was not actively used in some deployments, or its functionality was superseded.

Key Information Stored

The table's composite primary key defines the unique combination of entities it associates. The key columns are DIM_LEVEL_ID, which references a specific level from a dimension hierarchy (e.g., "Quarter"); INDICATOR, which identifies the specific Key Performance Indicator; and DIM_SET_ID, which identifies a predefined grouping of dimensions. While the exact property columns are not detailed in the excerpt, the table's description as "Dimension levels properties" implies it likely stores attributes governing how the KPI interacts with that dimension level, such as default aggregation rules, display formats, or security flags. The structure enforces that a KPI's configuration for a dimension level is always scoped within a particular dimension set.

Common Use Cases and Queries

This table is central to administrative setup and runtime querying for the Balanced Scorecard. A primary use case is the initial configuration or modification of a KPI's analytical dimensions. For example, an administrator would use this table to enable "Revenue" KPI to be analyzed by "Fiscal Quarter" and "Sales Region." For reporting, applications would query this table to determine the available drill paths for a selected scorecard item. A typical SQL pattern retrieves all configured dimension levels for a specific KPI and dimension set:

  • SELECT d.dim_level_id, l.meaning, l.description FROM bsc_kpi_dim_level_properties d, bsc_sys_dim_levels_b l WHERE d.dim_level_id = l.dim_level_id AND d.indicator = :p_kpi_id AND d.dim_set_id = :p_dim_set_id;

Another common query would validate setup by checking for KPIs missing necessary dimension level properties for a given analytical context.

Related Objects

The table maintains strict referential integrity through documented foreign key relationships with two central BSC tables:

  • BSC_SYS_DIM_LEVELS_B: This relationship (BSC_KPI_DIM_LEVEL_PROPERTIES.DIM_LEVEL_ID → BSC_SYS_DIM_LEVELS_B) ensures that every property record points to a valid, predefined dimension level (e.g., "City," "Department") in the system.
  • BSC_KPI_DIM_SETS_TL: This relationship (BSC_KPI_DIM_LEVEL_PROPERTIES.DIM_SET_ID, INDICATOR → BSC_KPI_DIM_SETS_TL) ties the property record to a specific dimension set definition and its associated KPI. The dimension set is a reusable collection of dimensions assigned to a scorecard or KPI.

These relationships highlight that BSC_KPI_DIM_LEVEL_PROPERTIES sits at the intersection of the KPI definition (via BSC_KPI_DIM_SETS_TL), the dimensional hierarchy metadata (BSC_SYS_DIM_LEVELS_B), and the specific configurable properties for their interaction.