Results for “bsc_sys_kpi_colors”

50+ results




AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

BSC_SYS_KPI_COLORS is a configuration and rendering table within the Oracle E-Business Suite Balanced Scorecard (BSC) module. It stores the color-coding definitions applied to Key Performance Indicators (KPIs) as they appear on a specific scorecard tab, for a specific period, and across the eight dimensionality levels defined by the BSC model. In functional terms, the table answers the question of what visual color, user override color, and trend indicator should be displayed for a given indicator cell on the scorecard canvas.

The metadata classifies BSC_SYS_KPI_COLORS as satellite-leaning under a heuristic Data Vault model. This suggests the table behaves primarily as a descriptive satellite attached to a parent business key — here the combination of tab and indicator — rather than as an independent hub. The presence of eight repeating DIM_LEVEL columns and period-scoped attributes reinforces that reading: the table captures state and descriptive detail rather than defining new business entities.

The BSC module is documented as obsolete in the ETRM source, and the table is noted as not implemented in the reference database. Consultants encountering it in legacy 12.1.1 or 12.2.2 environments should treat it as a historical artifact of the Balanced Scorecard feature set.

Key Information Stored

The table contains 18 documented columns. The most significant are:

  • TAB_ID and INDICATOR — the two-part foreign key referencing BSC_TAB_INDICATORS. Together they identify which indicator on which tab is being colored.
  • PERIOD_ID — scopes the color definition to a reporting period, allowing colors to vary over time.
  • DIM_LEVEL1 through DIM_LEVEL8 — eight positional columns representing the dimensional members addressed by the row. The user search term dim_level4 maps directly to this group.
  • KPI_COLOR and USER_COLOR — the system-derived and user-overridden color values.
  • ACTUAL_DATA, BUDGET_DATA, KPI_TREND, and TREND_FLAG — measures and flags used to determine color assignment and trend arrows.
  • KPI_MEASURE_ID — participates in the unique index and links the color to a specific measure.

The surrogate primary key is BSC_SYS_KPI_COLORS_PK, defined over the eleven columns DIM_LEVEL1 through DIM_LEVEL8, TAB_ID, PERIOD_ID, and INDICATOR. A unique index, BSC_SYS_KPI_COLORS_U1, provides the business-key candidate by extending that set with KPI_MEASURE_ID. This distinction matters when migrating or deduplicating rows, since the U1 index guarantees one color record per measure per dimensional intersection.

Common Use Cases and Queries

Typical usage centers on reproducing scorecard rendering logic or auditing color overrides. Because the table is obsolete, most queries serve forensic or migration purposes. A representative pattern retrieves the color assigned to a KPI at a given dimensional slice:

  • SELECT KPI_COLOR, USER_COLOR, KPI_TREND FROM BSC_SYS_KPI_COLORS WHERE TAB_ID = :tab AND INDICATOR = :ind AND PERIOD_ID = :period AND DIM_LEVEL4 = :dim4;
  • Joining to BSC_TAB_INDICATORS on TAB_ID and INDICATOR to resolve indicator names alongside their colors.
  • Aggregating TREND_FLAG to report how many KPIs at a given DIM_LEVEL intersection were trending favorably.
  • Comparing KPI_COLOR against USER_COLOR to identify manual overrides for audit purposes.

Reporting extracts should always constrain on PERIOD_ID, since the same dimensional intersection can carry different colors per period.

Related Objects

  • BSC_TAB_INDICATORS — the sole documented foreign key target, joined on TAB_ID and INDICATOR.
  • BSC_SYS_KPI_COLORS_PK — the primary key constraint indexing dimensional, tab, period, and indicator attributes.
  • BSC_SYS_KPI_COLORS_U1 — the unique index extending the key set with KPI_MEASURE_ID.
  • BSC scorecard and KPI definition tables (for example indicator and measure masters) that supply the business context referenced through INDICATOR and KPI_MEASURE_ID.