Results for “bsc_kpi_calculations”

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

Overview

BSC_KPI_CALCULATIONS is a table in the BSC (Balanced Scorecard) product schema of Oracle E-Business Suite, valid in both 12.1.1 and 12.2.2. Its documented purpose is to hold "calculations information" — the association between a Balanced Scorecard indicator (KPI) and the calculation definition used to derive that indicator's value. In practical terms, it is the mapping table that answers the question: "Which system calculation feeds this KPI, and how should that calculation's output be interpreted for a given user level?"

The table is owned by the BSC schema and contains eight documented columns. Based on the mined foreign-key structure, the heuristic Data Vault classification is satellite-leaning. This is a modeling suggestion only: the table's composite primary key (CALCULATION_ID, INDICATOR) describes a relationship plus descriptive attributes (the USER_LEVEL* and DEFAULT_VALUE columns), which is characteristic of a satellite attached to the business keys defined by BSC_KPIS_B and BSC_SYS_CALCULATIONS rather than a standalone hub.

Key Information Stored

The primary key is documented as BSC_KPI_CALCULATIONS_PK, composed of the columns CALCULATION_ID and INDICATOR. A separate unique index, BSC_KPI_CALCULATIONS_U1 (INDICATOR, CALCULATION_ID), reinforces the same business key in reverse column order. This means the combination of an indicator and a calculation is unique; neither column alone is a business key.

• INDICATOR — The KPI identifier. Foreign key to BSC_KPIS_B.INDICATOR.
• CALCULATION_ID — The system calculation identifier. Foreign key to BSC_SYS_CALCULATIONS.CALCULATION_ID.
• USER_LEVEL0 — User-level dimension value, level 0.
• USER_LEVEL1 — User-level dimension value, level 1.
• USER_LEVEL1_DEFAULT — Default flag or value for level 1.
• USER_LEVEL2 — User-level dimension value, level 2.
• USER_LEVEL2_DEFAULT — Default flag or value for level 2.
• DEFAULT_VALUE — The default value applied to the calculation for this indicator.

The USER_LEVEL* columns provide the dimensional context in which a calculation applies, allowing a single KPI to resolve to different calculation behaviour depending on the level at which it is being evaluated.

Common Use Cases and Queries

Typical usage centres on resolving which calculation drives a KPI, and on reporting which indicators depend on a given calculation. A standard join resolves the KPI and calculation descriptors:

• Resolve calculations for a KPI: SELECT c.indicator, c.calculation_id, c.default_value FROM bsc.bsc_kpi_calculations c WHERE c.indicator = :indicator_id.
• Reverse dependency check: query by CALCULATION_ID to find all indicators impacted before changing a shared calculation.
• User-level reporting: aggregate by USER_LEVEL0/1/2 to confirm which levels carry overridden or defaulted values.
• Integrity auditing: outer-join to BSC_KPIS_B and BSC_SYS_CALCULATIONS to detect orphaned calculation mappings.

Because the table is small and configuration-oriented, it is commonly exposed through the Balanced Scorecard setup forms and reporting extracts rather than high-volume transactional queries.

Related Objects

• BSC_KPIS_B — joined on INDICATOR; the KPI master (base) table.
• BSC_SYS_CALCULATIONS — joined on CALCULATION_ID; the calculation definitions.
• BSC_KPI_CALCULATIONS_USER — references this table on INDICATOR and CALCULATION_ID; stores user-specific calculation settings.

These three tables form the core join path around BSC_KPI_CALCULATIONS: BSC_KPIS_B supplies the indicator, BSC_SYS_CALCULATIONS supplies the calculation logic, and BSC_KPI_CALCULATIONS_USER provides user-level overrides keyed on the same composite key.