Search Results bsc_kpi_analysis_measures_b_pk

Overview

BSC_KPI_ANALYSIS_MEASURES_B is a transactional table in the BSC (Balanced Scorecard) schema of Oracle E-Business Suite. It stores analysis measures information that drives the analytical rendering of Key Performance Indicators within the Balanced Scorecard module. Each row represents a configured measure or series definition that governs how a KPI's data is charted, stacked, colored, and defaulted for end users, including benchmark and budget comparison options. The table has 16 documented columns in the ETRM 12.1.1 physical schema and is marked VALID in the subject schema. It serves as the base (non-translated) table, paired with a corresponding _TL table for language-specific descriptive text.

The heuristic Data Vault classification derived from the foreign key structure is satellite-leaning. This suggests the table behaves as a descriptive attribute set attached to a parent business key, rather than as an independent hub or a resolution link. In practical EBS terms, it acts as an extension of the KPI definition, holding configuration detail that qualifies the measures associated with a KPI series.

Key Information Stored

The surrogate primary key is defined by the index BSC_KPI_ANALYSIS_MEASURES_B_PK, composed of the composite columns ANALYSIS_OPTION0, ANALYSIS_OPTION1, INDICATOR, SERIES_ID, and ANALYSIS_OPTION2. This composite key is the unique row identifier used by dependent tables. A separate unique index, BSC_KPI_ANALYSIS_MEASURES_B_U1, covers the same five business columns (INDICATOR, ANALYSIS_OPTION0, ANALYSIS_OPTION1, ANALYSIS_OPTION2, SERIES_ID), reinforcing the business-key candidate nature of that combination. A second unique index, BSC_KPI_ANALYSIS_MEASURES_B_U2, is defined on KPI_MEASURE_ID, providing an alternative single-column uniqueness constraint and a stable internal reference point.

• INDICATOR — Identifies the parent KPI in BSC_KPIS_B; a core component of both the primary key and business-key unique index.
• ANALYSIS_OPTION0, ANALYSIS_OPTION1, ANALYSIS_OPTION2 — Multi-part analysis option descriptors that disambiguate the measure within a KPI. These participate in the primary key, the business-key unique index, and the join columns to the _TL and color tables.
• SERIES_ID — Identifies the data series to which the measure belongs; part of the composite key and used in joins to dependent tables.
• DATASET_ID — References BSC_SYS_DATASETS_B, associating the measure with a system dataset definition.
• AXIS, SERIES_TYPE, STACK_SERIES_ID — Control chart orientation, series rendering type, and stacked-series grouping for visual presentation.
• BM_FLAG, BUDGET_FLAG — Flags indicating whether benchmark and budget comparisons are enabled for the measure.
• DEFAULT_VALUE, SERIES_COLOR, BM_COLOR — Define default values and the colors applied to the series and benchmark lines.
• PROTOTYPE_FLAG — Marks whether the measure is a prototype versus a production definition.
• KPI_MEASURE_ID — The unique measure identifier covered by the U2 index.

Common Use Cases and Queries

This table is queried primarily when resolving how a given KPI's measures should be displayed and when joining measure configuration to language text or color metadata. A typical pattern joins the base table to its translation table:

• Retrieve all measures for a KPI: SELECT * FROM BSC.BSC_KPI_ANALYSIS_MEASURES_B WHERE INDICATOR = :kpi;
• Fetch measure text in the session language: join to BSC_KPI_ANALYSIS_MEASURES_TL on INDICATOR, ANALYSIS_OPTION0/1/2, and SERIES_ID.
• Resolve series color configuration by joining to BSC_KPI_SERIES_COLORS using the same five key columns.
• Validate measure-to-dataset relationships by joining DATASET_ID to BSC_SYS_DATASETS_B.
• Audit prototype versus production measures using PROTOTYPE_FLAG, or list benchmark/budget-enabled measures via BM_FLAG and BUDGET_FLAG.

Because the primary key is composite, migration scripts and ad-hoc queries must supply all five key columns to guarantee a single-row match.

Related Objects

• BSC_KPIS_B — Parent KPI definition; joined on BSC_KPI_ANALYSIS_MEASURES_B.INDICATOR.
• BSC_SYS_DATASETS_B — Dataset definitions; joined on DATASET_ID.
• BSC_KPI_ANALYSIS_MEASURES_TL — Translation table holding language-specific text; joined on INDICATOR, ANALYSIS_OPTION0, ANALYSIS_OPTION1, ANALYSIS_OPTION2, and SERIES_ID.
• BSC_KPI_SERIES_COLORS — Series color metadata; joined on the same five composite key columns.

These dependencies indicate that any change to a measure's composite key must cascade to the _TL and color tables, since both carry foreign keys back to this object.