Search Results bsc_kpi_dim_groups_u1

Overview

The BSC_KPI_DIM_GROUPS table is a core data structure within the Oracle E-Business Suite (EBS) Balanced Scorecard (BSC) application, versions 12.1.1 and 12.2.2. It functions as a junction or mapping table that defines the association between Key Performance Indicators (KPIs), dimension sets, and dimension groups. Its primary role is to organize and manage how analytical dimensions are grouped and applied to specific performance indicators, enabling structured multidimensional analysis and reporting within the BSC module. The table is essential for configuring the analytical framework that underpins scorecard dashboards and performance measurement systems.

Key Information Stored

The table stores a concise set of columns that establish critical relationships and ordering. The INDICATOR column stores the unique identifier for the Key Performance Indicator. The DIM_SET_ID is a foreign key referencing a defined set of dimensions (BSC_KPI_DIM_SETS_TL). The DIM_GROUP_ID is a foreign key referencing a specific logical grouping of dimensions (BSC_SYS_DIM_GROUPS_TL). The DIM_GROUP_INDEX column is significant as it stores a numerical value that dictates the display order or hierarchical sequence of the dimension group within the context of its associated indicator and dimension set. The primary key is a composite of INDICATOR, DIM_SET_ID, and DIM_GROUP_ID, ensuring a unique mapping for each combination.

Common Use Cases and Queries

A primary use case is auditing or reporting on the dimension group configuration for a specific KPI. For instance, to retrieve all dimension groups and their display order for a given indicator code (e.g., 1001), a developer or analyst would execute a query joining to the relevant translation tables for descriptive names. Another common scenario involves impact analysis before modifying a dimension group, requiring queries to identify all KPIs and dimension sets linked to a specific DIM_GROUP_ID.

• Query to list dimension group mappings for an indicator:
  SELECT bkdg.INDICATOR, bkdg.DIM_SET_ID, bkdg.DIM_GROUP_ID, bkdg.DIM_GROUP_INDEX, ds.NAME DIM_SET_NAME, dg.NAME DIM_GROUP_NAME FROM BSC.BSC_KPI_DIM_GROUPS bkdg JOIN BSC_KPI_DIM_SETS_TL ds ON bkdg.DIM_SET_ID = ds.DIM_SET_ID JOIN BSC_SYS_DIM_GROUPS_TL dg ON bkdg.DIM_GROUP_ID = dg.DIM_GROUP_ID WHERE bkdg.INDICATOR = 1001 ORDER BY bkdg.DIM_GROUP_INDEX;
• Query to find all references to a specific dimension group (ID 205):
  SELECT INDICATOR, DIM_SET_ID, DIM_GROUP_INDEX FROM BSC.BSC_KPI_DIM_GROUPS WHERE DIM_GROUP_ID = 205;

Related Objects

The table maintains defined foreign key relationships that are central to data integrity within the BSC schema. It references two translation tables to resolve IDs to user-friendly names. Specifically, the DIM_SET_ID column references the BSC_KPI_DIM_SETS_TL table, which stores the translated names for dimension sets. The DIM_GROUP_ID column references the BSC_SYS_DIM_GROUPS_TL table, which stores the translated names for system dimension groups. The table itself is referenced by the APPS synonym BSC_KPI_DIM_GROUPS, which is the standard access point for all application code and queries. The unique index BSC_KPI_DIM_GROUPS_U1 enforces the primary key constraint on INDICATOR, DIM_SET_ID, and DIM_GROUP_ID.