Search Results bsc_kpi_data_tables

Overview

The BSC_KPI_DATA_TABLES table is a core data repository within the now-obsolete Balanced Scorecard (BSC) module of Oracle E-Business Suite (EBS) versions 12.1.1 and 12.2.2. Its primary role was to manage the metadata linking Key Performance Indicators (KPIs) to the underlying physical database tables that stored their actual performance data. This table functioned as a critical mapping layer, enabling the BSC application to dynamically locate and retrieve the raw metric values for a given KPI based on its specific dimensionality and reporting periodicity. It is essential to note that the BSC module itself is marked as obsolete, meaning this table and its associated functionality are part of a legacy framework that is no longer under active development.

Key Information Stored

The table's structure is defined by its composite primary key, which uniquely identifies a specific data source for a KPI. The key columns are DIM_SET_ID, PERIODICITY_ID, LEVEL_COMB, and INDICATOR. These fields collectively describe the context of the data: the dimension set defining the analytical perspective (e.g., organization, time, product), the time frequency (e.g., monthly, quarterly), the specific level combination within the dimension hierarchy, and the KPI identifier itself. A critical non-key column is TABLE_NAME, which stores the name of the physical database table (referenced via BSC_DB_TABLES) where the actual KPI data values for this specific context are stored. This design allowed for flexible data sourcing, where a single KPI could pull data from different underlying tables depending on its analytical context.

Common Use Cases and Queries

The primary use case was to support the runtime data retrieval engine of the Balanced Scorecard. When a user requested a scorecard or report, the application would query this table to determine the precise source table for each KPI's data. Common queries involved joining to related metadata tables to generate data source maps or troubleshoot ETL processes. A typical pattern would be to identify all data tables for a specific KPI or periodicity.

• Sample Query: To find all data source mappings for a specific indicator and periodicity:
  SELECT kdt.indicator, kdt.periodicity_id, kdt.table_name, dbt.table_type
FROM bsc_kpi_data_tables kdt,
     bsc_db_tables dbt
WHERE kdt.table_name = dbt.table_name
AND kdt.indicator = :p_indicator
AND kdt.periodicity_id = :p_periodicity;

Related Objects

BSC_KPI_DATA_TABLES is centrally connected to several other metadata tables in the BSC schema via foreign key relationships, forming the core of the KPI definition framework.

• BSC_KPI_PERIODICITIES: References (INDICATOR, PERIODICITY_ID). Validates the time frequency defined for the KPI.
• BSC_DB_TABLES: References TABLE_NAME. Validates and provides additional attributes for the physical data table.
• BSC_KPI_DIM_SETS_TL: References DIM_SET_ID (and INDICATOR). Validates the set of dimensions associated with the KPI.

The table's primary key, BSC_KPI_DATA_TABLES_PK, is referenced by other objects (like materialized views or fact tables) not listed in the provided metadata but inherent to the module's architecture for storing and aggregating KPI data.