Search Results bsc_user_kpi_access




Overview
Explore in AI Chat

Overview

BSC_USER_KPI_ACCESS is a table in the BSC (Balanced Scorecard) product schema of Oracle E-Business Suite, present and valid in both release 12.1.1 and 12.2.2. Its documented description is "Indicator permissions." The table functions as a security and authorization mapping store: it associates a Balanced Scorecard indicator (KPI) with an EBS responsibility, thereby determining which responsibilities are permitted to view or work with a given indicator. In practice, it is the mechanism by which administrators scope KPI visibility across the organization rather than exposing every indicator to every user.

The ETRM metadata classifies this object, using a heuristic Data Vault assessment mined from its foreign-key structure, as standalone. As a modeling suggestion, this indicates the table is not a dependent satellite hanging off a single parent hub, nor does it exhibit the multi-parent signature of a true link table. It is best treated as an independent authorization/assignment entity whose grain is one row per (indicator, responsibility) pairing. The primary key BSC_USER_KPI_ACCESS_PK enforces that uniqueness directly.

Key Information Stored

The table carries nine documented columns. The two most important are the composite key members, which also serve as the business-key candidate:

  • INDICATOR — identifies the Balanced Scorecard indicator (KPI) to which permission is being granted. Part of the primary key.
  • RESPONSIBILITY_ID — the EBS responsibility receiving access to the indicator. Part of the primary key and the join column back to the responsibility definition.
  • START_DATE — the date from which the permission becomes effective.
  • END_DATE — the date on which the permission expires, supporting time-bounded access.
  • CREATION_DATE, CREATED_BY — standard EBS audit trail recording when and by whom the permission row was inserted.
  • LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — standard audit columns capturing the most recent modification and the login session responsible for it.

The surrogate-style primary key BSC_USER_KPI_ACCESS_PK is defined over (INDICATOR, RESPONSIBILITY_ID), while the unique index BSC_USER_KPI_ACCESS_U1 is defined over (RESPONSIBILITY_ID, INDICATOR). Both constrain the same pair of columns in reversed order, guaranteeing that no responsibility is granted duplicate permission to the same indicator. The START_DATE and END_DATE columns distinguish this from a simple static mapping, allowing permissions to be dated.

Common Use Cases and Queries

Typical use cases include security auditing (who can see a given KPI), provisioning reports (which indicators a responsibility should display), and troubleshooting missing-indicator symptoms where a user cannot see a scorecard element because no access row exists. A representative query to list responsibilitites authorized for an indicator:

  • SELECT responsibility_id, indicator, start_date, end_date FROM bsc.bsc_user_kpi_access WHERE indicator = :indicator AND (end_date IS NULL OR end_date > SYSDATE);
  • SELECT indicator FROM bsc.bsc_user_kpi_access WHERE responsibility_id = :resp_id AND SYSDATE BETWEEN start_date AND NVL(end_date, SYSDATE + 1);
  • Audit of stale or expired grants: SELECT * FROM bsc.bsc_user_kpi_access WHERE end_date < SYSDATE;

Because the table is standalone, reporting joins are driven by the responsibility and indicator values held in its own columns rather than by enforced foreign keys.

Related Objects

The following objects are the most significant to consider alongside this table:

  • FND_RESPONSIBILITY — joined on FND_RESPONSIBILITY.RESPONSIBILITY_ID = BSC_USER_KPI_ACCESS.RESPONSIBILITY_ID to resolve responsibility names.
  • BSC_KPI_INDICATORS (indicator definitions) — joined on INDICATOR to resolve KPI names and definitions.
  • BSC_USER_KPI_ACCESS_PK — the primary key constraint on (INDICATOR, RESPONSIBILITY_ID).
  • BSC_USER_KPI_ACCESS_U1 — the unique index on (RESPONSIBILITY_ID, INDICATOR), the business-key candidate.
  • FND_USER — indirectly related through responsibility assignments for end-user access reporting.

No foreign keys are documented for this table, so referential integrity to indicator and responsibility masters is maintained by application logic rather than by the database.