Results for “sql_stmt”

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

Overview

BSC_KPI_DATA_TABLES is a configuration and metadata repository within the Oracle EBS Balanced Scorecard (BSC) module. It records the definition of data tables — both physical database tables and materialized views — that supply the source data for Key Performance Indicators (KPIs) tracked in a Balanced Scorecard deployment. The object is classified as Obsolete in the current BSC product documentation, meaning it is retained for backward compatibility with earlier 11i and early 12.x implementations but is not implemented in freshly provisioned 12.1.1 or 12.2.2 databases. Despite this status, the table continues to appear in migration scripts, legacy support engagements, and ETRM (E-Business Tables and Relationships Model) reference material, and it is frequently searched by DBAs auditing residual BSC schema objects.

From a modeling perspective, the ETRM relationship data classifies BSC_KPI_DATA_TABLES as a link entity in the Data Vault heuristic. This classification is appropriate: the table resolves the many-to-many relationship between KPI dimension sets, periodicities, and the underlying source data tables that feed indicator values. Rather than storing measurements, it stores the join topology and transformation metadata that connect scorecard indicators to their data origins.

Key Information Stored

The table comprises 11 documented columns in the 12.1.1 physical schema. The most significant are:

• DIM_SET_ID — Identifies the KPI dimension set supplying the dimensional context for the indicator.
• PERIODICITY_ID — References the KPI periodicity (daily, weekly, monthly, etc.) governing how the indicator aggregates.
• INDICATOR — The KPI indicator key to which the data-table linkage applies.
• LEVEL_COMB — Specifies the level combination within the dimension hierarchy at which the data is sourced.
• TABLE_NAME — The physical database table or view name that provides the source data.
• FILTER_CONDITION — Optional SQL predicate restricting rows drawn from the source table.
• MV_NAME — Materialized view name, when the indicator is populated from a pre-aggregated MV.
• DATA_SOURCE — Classifies the data origin (e.g., table, MV, external).
• SQL_STMT — The SQL statement used to extract or transform source data.
• PROJECTION_SOURCE and PROJECTION_DATA — Govern projection of KPI values across dimensions and levels.

The surrogate primary key is BSC_KPI_DATA_TABLES_PK, composed of DIM_SET_ID, PERIODICITY_ID, LEVEL_COMB, INDICATOR. A unique index, BSC_KPI_DATA_TABLES_U1, exists on INDICATOR, PERIODICITY_ID, DIM_SET_ID, LEVEL_COMB — the same attribute set in a different column order, serving as the documented business-key candidate.

Common Use Cases and Queries

Typical usage centers on tracing which source objects feed a given KPI and understanding filtering or aggregation logic. A common audit query retrieves all source tables linked to a specific indicator:

SELECT d.TABLE_NAME, d.DATA_SOURCE, d.FILTER_CONDITION, d.MV_NAME
FROM   BSC_KPI_DATA_TABLES d
WHERE  d.INDICATOR = :p_indicator
AND    d.DIM_SET_ID = :p_dim_set_id;

Impact analysis — determining which KPIs would be affected by a change to a source table — is performed by joining on TABLE_NAME. Reporting on periodicity configuration joins to BSC_KPI_PERIODICITIES. Because the product is obsolete, these queries primarily support remediation, archiving, or pre-upgrade validation rather than active configuration.

Related Objects

• BSC_KPI_PERIODICITIES — referenced via INDICATOR and PERIODICITY_ID; defines aggregation intervals.
• BSC_DB_TABLES — referenced via TABLE_NAME; holds registered source-table metadata.
• BSC_KPI_DIM_SETS_TL — referenced via DIM_SET_ID; the translated dimension-set definitions.
• BSC_KPI_INDICATORS — parent of the INDICATOR attribute in practice.
• BSC_KPI_DIM_LEVELS and BSC_KPI_LEVELS_TL — supply hierarchy context implied by LEVEL_COMB.