Search Results bsc_kpi_dim_groups




Overview
Explore in AI Chat

Overview

The table BSC_KPI_DIM_GROUPS is a core data structure within the now-obsolete Balanced Scorecard (BSC) module of Oracle E-Business Suite (EBS) versions 12.1.1 and 12.2.2. Its primary function is to manage the association between Key Performance Indicators (KPIs) and the analytical dimensions used to evaluate them. The table acts as a junction, linking a specific KPI (INDICATOR) and its defined dimension set (DIM_SET_ID) to the individual dimension groups (DIM_GROUP_ID) that comprise that set. This relationship is fundamental for enabling multi-dimensional performance analysis, allowing a single KPI to be sliced and reported across various business perspectives such as geography, product line, or time period.

Key Information Stored

The table's structure is defined by a composite primary key, ensuring a unique mapping for each combination of its core columns. The key columns are:

  • DIM_GROUP_ID: A foreign key referencing BSC_SYS_DIM_GROUPS_TL. This identifies a specific dimension group, which is a logical collection of related dimension members (e.g., a "North America" group containing countries like USA and Canada).
  • DIM_SET_ID: A foreign key referencing BSC_KPI_DIM_SETS_TL. This identifies a dimension set, which is a collection of dimension groups assembled for a particular analytical purpose.
  • INDICATOR: The identifier for the specific Key Performance Indicator. This column, along with DIM_SET_ID, links to the BSC_KPI_DIM_SETS_TL table, establishing which dimension set is applied to which KPI.
Together, these columns store the essential metadata defining how a KPI's performance data is organized and can be disaggregated for detailed scorecard reporting.

Common Use Cases and Queries

This table is primarily accessed for metadata queries to understand the analytical framework of a scorecard. A common use case is to generate a report listing all dimension groups available for analyzing a specific KPI. The following sample SQL pattern retrieves this information by joining to the corresponding translation tables for descriptive names:

SELECT kdg.INDICATOR,
      dst.NAME DIM_SET_NAME,
      dgt.NAME DIM_GROUP_NAME
FROM BSC_KPI_DIM_GROUPS kdg,
     BSC_KPI_DIM_SETS_TL dst,
     BSC_SYS_DIM_GROUPS_TL dgt
WHERE kdg.DIM_SET_ID = dst.DIM_SET_ID
  AND kdg.DIM_GROUP_ID = dgt.DIM_GROUP_ID
  AND dst.LANGUAGE = USERENV('LANG')
  AND dgt.LANGUAGE = USERENV('LANG')
  AND kdg.INDICATOR = :p_kpi_code;

Another critical use case is validation during KPI setup, ensuring that a dimension group is not accidentally assigned multiple times to the same dimension set for a given indicator, which would be prevented by the primary key constraint.

Related Objects

BSC_KPI_DIM_GROUPS sits at the intersection of two key metadata entities in the Balanced Scorecard, as defined by its documented foreign key relationships:

  • BSC_KPI_DIM_SETS_TL: This translation table provides the name and description for the dimension set (DIM_SET_ID). The relationship is defined on the columns BSC_KPI_DIM_GROUPS.DIM_SET_ID and BSC_KPI_DIM_GROUPS.INDICATOR referencing BSC_KPI_DIM_SETS_TL.
  • BSC_SYS_DIM_GROUPS_TL: This translation table provides the name and description for the dimension group (DIM_GROUP_ID). The relationship is defined on BSC_KPI_DIM_GROUPS.DIM_GROUP_ID referencing BSC_SYS_DIM_GROUPS_TL.
These relationships are essential for retrieving human-readable labels when querying the dimensional structure of a KPI. The table itself is central to the obsolete BSC module's data model for configuring scorecard analytics.