Results for “bsc_db_measure_by_dim_set_v”

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

Overview

BSC_DB_MEASURE_BY_DIM_SET_V is a database view belonging to the Balanced Scorecard (BSC) product family in Oracle E-Business Suite. In ETRM documentation, BSC is flagged as obsolete, and this view is explicitly documented as "Not implemented in this database." Its declared purpose is to present a flattened list of measures organized by dimension set, exposing the relationship between an indicator, a dimension set, and the measure assigned to that dimension set. It therefore serves a reporting and integration role: instead of requiring consumers to navigate the normalized dataset/dimension-set structures directly, the view projects a simple three-column result that links measures to their dimension set context.

Because the view is not implemented in the delivered database, it should be treated as documented metadata describing historical or internal BSC design rather than an object that can be queried in a standard 12.1.1 or 12.2.2 instance. Any usage should be validated against the actual database before relying on it.

Underlying Base Objects

The documented view text defines BSC_DB_MEASURE_BY_DIM_SET_V over two referenced objects:

• BSC_DB_DATASET_DIM_SETS_V — the dataset-to-dimension-set association view.
• BSC_SYS_DATASETS_VL — the system datasets view, which supplies the MEASURE_ID1 and MEASURE_ID2 columns used to derive the measure.

The definition is a UNION of two SELECT statements. The first selects DB.INDICATOR, DB.DIM_SET_ID, and DS.MEASURE_ID1 as MEASURE_ID, joining the two objects on DATASET_ID. The second selects DB.INDICATOR, DB.DIM_SET_ID, and DS.MEASURE_ID2 as MEASURE_ID, joined on DATASET_ID, with the added predicate that DS.MEASURE_ID2 IS NOT NULL. The effect is to produce one row for each dimension set's primary measure and an additional row when a secondary measure exists.

The ETRM metadata notes no directly documented base tables, only these referenced views. Since the view is unimplemented, no physical dependency chain is active in the delivered schema.

Key Columns

The view exposes three columns, each significant for measure-to-dimension-set reporting:

• INDICATOR — sourced from BSC_DB_DATASET_DIM_SETS_V. It identifies the indicator associated with the dataset/dimension-set combination, providing the performance context for the measure.
• DIM_SET_ID — the dimension set identifier. It groups measures by the dimension set to which they belong, enabling aggregation or filtering by dimension set.
• MEASURE_ID — derived from either MEASURE_ID1 or MEASURE_ID2 on BSC_SYS_DATASETS_VL. It represents the measure assigned to the dimension set. Because the UNION emits MEASURE_ID1 unconditionally and MEASURE_ID2 only when populated, a dimension set may appear with one or two measure rows.

Common Use Cases and Queries

The principal use case is mapping dimension sets to their measures, particularly when a single dataset carries both a primary and secondary measure. Reporting logic could use the view to enumerate every measure tied to each dimension set, driving scorecard display, measure validation, or migration analysis. A representative query, consistent with the documented definition, is:

• SELECT INDICATOR, DIM_SET_ID, MEASURE_ID FROM BSC_DB_MEASURE_BY_DIM_SET_V WHERE DIM_SET_ID = :dim_set_id;
• SELECT MEASURE_ID, COUNT(*) FROM BSC_DB_MEASURE_BY_DIM_SET_V GROUP BY MEASURE_ID;
• SELECT INDICATOR, DIM_SET_ID FROM BSC_DB_MEASURE_BY_DIM_SET_V WHERE MEASURE_ID = :measure_id;

The last query is especially relevant to the user's search for "measure_id," since MEASURE_ID is the view column that resolves a searched measure to its indicator and dimension set. Because the view is obsolete and not implemented, these queries should be regarded as reference patterns; equivalent logic must be constructed from BSC_DB_DATASET_DIM_SETS_V and BSC_SYS_DATASETS_VL in any live environment.