Results for “bsc_sys_datasets_b”

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

Overview

BSC_SYS_DATASETS_B is the base (non-translated) table that stores dataset definitions for the Oracle E-Business Suite Balanced Scorecard module (product code BSC). Datasets in the Balanced Scorecard architecture define structured collections of measures — and the mathematical operations applied to those measures — that are consumed by scorecard KPIs, indicator definitions, and analytical reporting components. The table is owned by the BSC schema and is marked VALID in the documented 12.1.1 electronic technical reference manual (ETRM).

Its primary key, BSC_SYS_DATASETS_B_PK, is defined on DATASET_ID, and a unique index BSC_SYS_DATASETS_B_U1 also covers DATASET_ID, confirming it as the stable business-key and surrogate identifier for a dataset definition. Applying Data Vault modeling heuristics to its foreign-key topology, the table classifies as hub-leaning: it functions as a central reference point (hub) around which dependent satellites (BSC_SYS_DATASETS_TL, BSC_SYS_DATASET_CALC) and dependent/linking tables (BSC_KPI_ANALYSIS_MEASURES_B, BIS_INDICATORS) cluster. The documented 12.1.1 physical schema contains 15 columns.

Key Information Stored

The most operationally significant columns are:

• DATASET_ID — the surrogate primary key and unique business-key identifier for the dataset; it is also the column propagated to every dependent table.
• MEASURE_ID1 and MEASURE_ID2 — foreign keys into BSC_SYS_MEASURES, defining the two measures combined by the dataset.
• OPERATION — the arithmetic relationship applied between measure 1 and measure 2 (for example, addition, subtraction, ratio, or percentage variance).
• FORMAT_ID — foreign key into BSC_SYS_FORMATS, controlling how the computed dataset value is rendered (currency, percent, decimal precision).
• COLOR_METHOD — determines the color-coding or threshold-band method used when the dataset output is displayed.
• PROJECTION_FLAG — indicates whether the dataset supports projection or trend extension for future periods.
• EDW_FLAG — identifies datasets sourced from or destined for the enterprise data warehouse.
• AUTOSCALE_FLAG — controls automatic scaling of the displayed value.
• SOURCE — indicates the origin of the dataset definition.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard Oracle EBS audit and concurrency columns populated by the application framework.

Common Use Cases and Queries

Developers and analysts query this table when tracing why a scorecard KPI displays a particular computed value, or when auditing which measure pairs drive a given indicator. A typical join resolves both measures and the format:

• Resolve the dataset name by joining to the translation table: SELECT d.DATASET_ID, t.DATASET_NAME FROM BSC.BSC_SYS_DATASETS_B d, BSC.BSC_SYS_DATASETS_TL t WHERE d.DATASET_ID = t.DATASET_ID AND t.LANGUAGE = USERENV('LANG');
• Identify the calculation logic: join MEASURE_ID1/MEASURE_ID2 to BSC_SYS_MEASURES and read OPERATION, FORMAT_ID, and AUTOSCALE_FLAG to reconstruct the displayed formula.
• Impact analysis before modification: query BSC_SYS_DATASET_CALC, BSC_KPI_ANALYSIS_MEASURES_B, BIS_INDICATORS, and BIS_PARAM_COLOR_TRIGGERS by DATASET_ID to determine every consumer of the dataset.
• Warehouse reconciliation: filter on EDW_FLAG = 'Y' to isolate datasets participating in enterprise data warehouse integration, and reconcile against BIS_INDICATORS.DATASET_ID.

Because report definitions are seeded and cached, any direct data change should be validated through the Balanced Scorecard administration screens rather than by SQL update alone.

Related Objects

The following objects have the most significant documented relationships to BSC_SYS_DATASETS_B:

• BSC_SYS_MEASURES — referenced twice, via MEASURE_ID1 and MEASURE_ID2; supplies the underlying measures combined by OPERATION.
• BSC_SYS_FORMATS — referenced via FORMAT_ID; supplies display formatting metadata.
• BSC_SYS_DATASETS_TL — the translated-language child table, joined on DATASET_ID, holding locale-specific dataset names and descriptions.
• BSC_SYS_DATASET_CALC — the calculation detail child, joined on DATASET_ID, holding the computation graph for the dataset.
• BSC_KPI_ANALYSIS_MEASURES_B — references DATASET_ID; links analysis measures to their dataset source.
• BIS_INDICATORS — references DATASET_ID; the core indicator/scorecard definition table that consumes datasets for display.
• BIS_PARAM_COLOR_TRIGGERS — references DATASET_ID; defines color-trigger thresholds applied to dataset output.

Together these relationships establish BSC_SYS_DATASETS_B as the hub of dataset-driven measurement within the Balanced Scorecard and BIS indicator stack.