Search Results level_view_name

Overview

BSC_BIS_DIM_LEVELS_V is a database view belonging to the Oracle Balanced Scorecard (BSC) product family, which is documented as obsolete in Oracle EBS 12.1.1 and 12.2.2. The view presents the BIS/BSC dimension levels used by IBUILDER, the internal component responsible for constructing and resolving dimensional hierarchies within the Balanced Scorecard and Business Intelligence System (BIS) framework. Its purpose is to expose level metadata — the named levels that sit beneath a dimension — so that downstream reporting and integration logic can resolve the correct level view, source system, and import status.

The view is of interest when users search for level_view_name, because that column is central to how the view resolves the physical or logical view that supplies level values. For dimensions sourced from the Enterprise Data Warehouse (EDW), the view substitutes a naming convention (SHORT_NAME || '_LTC'); otherwise it falls back to the source level values view name. A separate column captures the BSC-specific level view name. Because BSC is obsolete, this object should be regarded as legacy metadata; it is not implemented in the current database per the documented DBA data, meaning no materialized instance exists in a standard installation even though the definition is retained.

Underlying Base Objects

The documented ETRM metadata records no referenced base objects, but the view text itself reveals that it is defined over two base views: BIS_LEVELS_VL, which holds BIS level definitions, and BSC_SYS_DIM_LEVELS_VL, which holds the BSC system-level definitions. The two are joined with an outer join on SHORT_NAME, so that BIS levels are always returned even when no matching BSC level exists. A second branch of a UNION appends BSC-only levels whose SOURCE is 'BSC' and which have no corresponding BIS record, assigning a sentinel DIMENSION_ID of -999.

This pattern — a UNION of a joined branch and a BSC-only branch — produces a consolidated list of levels from both subsystems. The NVL(SL.DIM_LEVEL_ID, -999) construct applies the same sentinel convention at the level identifier, allowing rows without a native BSC level to be distinguished reliably. The outer-join column BSC_SOURCE defaults to 'PMF' when the BSC level is absent, and IMPORTED_FLAG is derived from the BSC source: NULL yields 0, 'PMF' yields 1, and all other sources yield 0.

Key Columns

• DIM_LEVEL_ID — Identifier of the dimension level; defaults to -999 when no BSC level is present.
• NAME — Descriptive name of the level from the BIS level view.
• SHORT_NAME — Abbreviated level name used as the join key between BIS and BSC definitions.
• DIMENSION_ID — Identifier of the owning dimension; set to -999 for BSC-only rows.
• LEVEL_VIEW_NAME — The resolved view supplying level values; for EDW sources this is the short name suffixed with '_LTC', otherwise the source view name.
• BSC_LEVEL_VIEW_NAME — The level view name recorded on the BSC system-level record.
• BSC_SOURCE — Source system of the BSC level, defaulting to 'PMF' when absent.
• SOURCE — Effective source system for the row (EDW, BSC, PMF, or similar).
• IMPORTED_FLAG — Indicator (0 or 1) of whether the level has been imported from BSC.

Common Use Cases and Queries

Typical use is to resolve the correct level view for a given dimension and level before generating a query or a dashboard data source. A representative query lists all levels and their resolved view names:

• SELECT dimension_id, dim_level_id, short_name, level_view_name, bsc_level_view_name, source FROM bsc_bis_dim_levels_v ORDER BY dimension_id, short_name;
• SELECT short_name, level_view_name FROM bsc_bis_dim_levels_v WHERE source = 'EDW'; — isolates EDW-sourced levels, whose view names follow the '_LTC' convention.
• SELECT short_name, bsc_source FROM bsc_bis_dim_levels_v WHERE imported_flag = 1; — identifies levels imported from the PMF source.

Because the view is documented as obsolete and not implemented in the database, these queries are relevant primarily for legacy migration, code review of historical customizations, or understanding surviving references to BSC metadata in an upgraded environment.