Search Results bsc_bis_measures_data_u1

Overview

BSC.BSC_BIS_MEASURES_DATA is a transactional table in the Oracle E-Business Suite Balanced Scorecard (BSC) product family. It functions as a pre-aggregated metric staging and display store used by the Balanced Scorecard Intelligence System (BIS) to hold the numeric values that back scorecard indicators, tied to the specific user, responsibility, and analytical context under which those values are rendered. The table resides in the APPS_TS_TX_DATA tablespace with a PCT Free of 10, and its unique index BSC_BIS_MEASURES_DATA_U1 is stored separately in APPS_TS_TX_IDX.

From a Data Vault modeling perspective, this object is heuristic-classified as standalone. Although a single foreign key path exists from SERIES_ID to BSC.BSC_SYS_SERIES_COLORS, the table's grain is defined by the combination of user, responsibility, indicator, analytical options, and series — effectively a context-specific fact row rather than a pure hub, link, or satellite. In a Data Vault refactor, the natural hub would be the (INDICATOR, SERIES_ID) combination, with a satellite carrying the ACTUAL_DATA and BUDGET_DATA measures and the remaining descriptive attributes.

Per the Oracle ETRM documentation, this is marked "Oracle Internal Use Only" and Oracle does not support direct data access except through standard Oracle Applications programs. Any external read should be treated as a diagnostic or reporting activity rather than an integration contract.

Key Information Stored

The documented physical schema exposes 15 columns. The most operationally significant are:

• USER_ID and RESPONSIBILITY_ID — together with the analytical columns, they scope each row to a specific user session and menu context, ensuring scorecard values are personalized.
• INDICATOR — the scorecard indicator (KPI) to which the measure belongs.
• ANALYSIS_OPTION0, ANALYSIS_OPTION1, ANALYSIS_OPTION2 — dimensional slicers that parameterize how the indicator is broken down (for example, by period, organization, or product dimension members).
• SERIES_ID — identifies the series definition; it is the only column with a documented foreign key, pointing to BSC.BSC_SYS_SERIES_COLORS.
• CACHING_KEY — a VARCHAR2(200) key used to invalidate or retrieve the cached measure set.
• ACTUAL_DATA and BUDGET_DATA — the two principal numeric payloads. ACTUAL_DATA carries realized performance; BUDGET_DATA carries the budgeted or target value. These two columns are the reason this table is frequently targeted by budget-versus-actual reporting.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — the standard Oracle WHO columns recording audit and concurrency information.

The unique index BSC_BIS_MEASURES_DATA_U1 defines the business key as (USER_ID, RESPONSIBILITY_ID, INDICATOR, ANALYSIS_OPTION0, ANALYSIS_OPTION1, ANALYSIS_OPTION2, SERIES_ID). No surrogate numeric primary key column is documented; the unique index itself is the de facto row identifier.

Common Use Cases and Queries

The most common reporting scenario is a budget-versus-actual comparison for a given indicator and user context. A typical query pattern is:

• Variance reporting: select INDICATOR, ACTUAL_DATA, BUDGET_DATA, and the computed difference (ACTUAL_DATA - BUDGET_DATA) filtered by USER_ID and RESPONSIBILITY_ID for a specific period represented in the ANALYSIS_OPTION columns.
• Indicator drill-down: filter by INDICATOR and each ANALYSIS_OPTIONn to explode a KPI across its configured dimensional breakdowns.
• Series validation: join SERIES_ID to BSC_SYS_SERIES_COLORS to confirm which series color and definition a cached measure row belongs to.
• Cache health checks: inspect CACHING_KEY and LAST_UPDATE_DATE to identify stale or orphaned measure rows.
• Audit and access review: group by USER_ID and RESPONSIBILITY_ID to determine which user contexts have materialized scorecard data.

All queries should respect the documented column list and avoid reliance on undocumented physical attributes. Because the table is populated by standard BSC concurrent and online programs, values may be refreshed or invalidated at any time, so reporting extracts should be point-in-time.

Related Objects

The documented dependency footprint is narrow. The significant related objects are:

• BSC.BSC_SYS_SERIES_COLORS — joined via BSC_BIS_MEASURES_DATA.SERIES_ID to resolve the series definition and color assigned to each measure row.
• APPS.BSC_BIS_MEASURES_DATA — the APPS-layer synonym or view through which EBS code and supported queries normally access the table.
• BSC.BSC_BIS_MEASURES_DATA_U1 — the unique index enforcing the business key; critical for query plan tuning on USER_ID, RESPONSIBILITY_ID, and INDICATOR predicates.
• BSC schema objects (Balanced Scorecard) — indicator and scorecard definition tables within the BSC product that supply the semantic meaning of INDICATOR and the ANALYSIS_OPTION dimensions.

No other database objects are documented as referencing or being referenced by this table, confirming its role as a leaf-level measure store within the Balanced Scorecard Intelligence System.