Search Results dim_level2

Overview

The BSC_SYS_KPI_COLORS table 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 store the specific color assignments for Key Performance Indicators (KPIs) based on their performance state, as displayed within a given scorecard tab. This table enables the visual representation of performance data, allowing users to quickly discern status (e.g., red for poor, yellow for caution, green for good) across various dimensions and time periods. The table's design, which includes numerous dimension level columns and a composite primary key, reflects the multi-dimensional analysis capabilities of the Balanced Scorecard application. It is important to note that the BSC module is marked as obsolete, indicating that while this table may exist in legacy implementations, it is not part of Oracle's strategic development path.

Key Information Stored

The table's structure is defined by a complex composite primary key and several dimension columns. The primary key, BSC_SYS_KPI_COLORS_PK, uniquely identifies a color record through a combination of eleven columns: DIM_LEVEL1, DIM_LEVEL2, DIM_LEVEL3, DIM_LEVEL4, DIM_LEVEL5, DIM_LEVEL6, DIM_LEVEL7, DIM_LEVEL8, TAB_ID, PERIOD_ID, and INDICATOR. The DIM_LEVEL1 through DIM_LEVEL8 columns represent the hierarchical dimension members (e.g., Organization, Geography, Product) against which the KPI is measured. The TAB_ID identifies the specific scorecard tab or view, the INDICATOR is the KPI code, and the PERIOD_ID denotes the time period for the measurement. While the metadata does not explicitly list a COLOR column, the table's description implies it stores the resulting color code or value based on the combination of these key attributes.

Common Use Cases and Queries

The primary use case is retrieving the visual status color for a KPI in a specific context for reporting or dashboard rendering. A typical query would join this table to the KPI definition and dimension tables to fetch the applicable color for a given set of dimension values, tab, and period. For example, to find the color for a specific indicator (INDICATOR) within a particular tab (TAB_ID) for a given period (PERIOD_ID) and a specific dimension member at level 1 (DIM_LEVEL1), a query would filter on these key columns. In a reporting context, a SQL analytic function might be used to compare performance against targets stored elsewhere, with the result driving a lookup or logic to determine the color stored in this table.

Related Objects

Based on the provided foreign key relationship data, BSC_SYS_KPI_COLORS has a direct dependency on the BSC_TAB_INDICATORS table. The foreign key is defined on the TAB_ID and INDICATOR columns, meaning that a record in BSC_SYS_KPI_COLORS must correspond to a valid combination of a tab and an indicator defined in BSC_TAB_INDICATORS. This ensures referential integrity, guaranteeing that color assignments are only stored for configured KPIs within existing scorecard tabs. Given the table's role, it is also logically related to dimension tables (for DIM_LEVEL values) and period tables (for PERIOD_ID), though these specific relationships are not detailed in the provided metadata.