Search Results bsc_kpi_cause_effect_rels_n2




Overview
Explore in AI Chat

Overview

The BSC_KPI_CAUSE_EFFECT_RELS table is a core data structure within the Oracle E-Business Suite (EBS) Balanced Scorecard (BSC) application, versions 12.1.1 and 12.2.2. It serves as a relational repository for defining and managing cause-and-effect linkages between performance indicators. This table is fundamental to modeling strategic relationships, enabling the analysis of how changes in one Key Performance Indicator (KPI) or dataset (the cause) may influence another KPI or dataset (the effect). By storing these relationships, the table underpins advanced analytical features such as strategy maps and impact analysis within the BSC module, allowing organizations to visualize and understand the drivers of performance.

Key Information Stored

The table's structure is concise, designed to map a cause indicator to an effect indicator along with their respective object types. The primary columns are:

  • CAUSE_INDICATOR (NUMBER): A foreign key referencing the unique code (typically from BSC_KPIS_B) for the indicator acting as the causal factor.
  • EFFECT_INDICATOR (NUMBER): A foreign key referencing the unique code for the indicator that is impacted. The presence of a non-unique index on this column (BSC_KPI_CAUSE_EFFECT_RELS_N1) suggests frequent queries to find all causes for a given effect.
  • CAUSE_LEVEL (VARCHAR2(10)): Specifies the object type of the cause indicator. The documented values are 'KPI' or 'DATASET'.
  • EFFECT_LEVEL (VARCHAR2(10)): Specifies the object type of the effect indicator, also 'KPI' or 'DATASET'.
The combination of CAUSE_INDICATOR and EFFECT_INDICATOR forms the table's primary key (BSC_KPI_CAUSE_EFFECT_RELS_PK), ensuring each cause-effect relationship is unique.

Common Use Cases and Queries

This table is primarily accessed for strategic analysis and reporting. A common use case is generating a strategy map or impact report to visualize the network of influences between KPIs. Administrators may also query it to audit or modify strategic relationships. A fundamental query retrieves all relationships for analysis: 

SELECT CAUSE_INDICATOR, EFFECT_INDICATOR, CAUSE_LEVEL, EFFECT_LEVEL
FROM BSC.BSC_KPI_CAUSE_EFFECT_RELS
ORDER BY EFFECT_INDICATOR, CAUSE_INDICATOR;
To analyze all potential causes behind a specific underperforming KPI (effect), a query would join to the KPIs table: 
SELECT rel.CAUSE_INDICATOR, kpi_cause.NAME AS CAUSE_NAME, rel.CAUSE_LEVEL
FROM BSC.BSC_KPI_CAUSE_EFFECT_RELS rel,
     BSC_KPIS_B kpi_cause
WHERE rel.EFFECT_INDICATOR = :p_effect_kpi_id
AND   rel.CAUSE_INDICATOR = kpi_cause.INDICATOR (+)
AND   rel.CAUSE_LEVEL = 'KPI';
Conversely, to understand the downstream impacts of a KPI, one would query using the CAUSE_INDICATOR column, which is also indexed (BSC_KPI_CAUSE_EFFECT_RELS_N2).

Related Objects

The table has defined foreign key relationships that are critical for data integrity and joins in application logic and custom reports. The documented relationships are:

  • Primary Key: BSC_KPI_CAUSE_EFFECT_RELS_PK on (CAUSE_INDICATOR, EFFECT_INDICATOR).
  • Foreign Key (Cause): The CAUSE_INDICATOR column references the BSC_KPIS_B table. This links the cause side of the relationship to the master definition of an indicator.
  • Foreign Key (Effect): The EFFECT_INDICATOR column also references the BSC_KPIS_B table. This links the effect side of the relationship to the master definition of an indicator.
These relationships confirm that the table primarily manages linkages between defined KPIs, though the LEVEL columns allow for potential extension to other object types like datasets. The table is referenced by the APPS synonym BSC_KPI_CAUSE_EFFECT_RELS, which is the standard access point for all application code.