Search Results bsc_bis_dim_objs_vl




Overview
Explore in AI Chat

Overview

BSC_BIS_DIM_OBJS_VL is an Oracle EBS Applications (APPS) view in the BIS product family. In ETRM 12.2.2 metadata it is documented with status VALID. Its name follows the "_VL" convention used in Oracle EBS for views that present validated/joined columns from a level or lookup definition, and its defining text joins two level views: BIS_LEVELS_VL (aliased BIS_LEV) and BSC_SYS_DIM_LEVELS_VL (aliased BSC_LEV).

Functionally, this view exposes the metadata that describes dimension levels — the hierarchy levels available within Oracle Business Intelligence/balanced scorecard (BSC) dimensional models. Because it merges a BIS implementation layer with a BSC system-dimension layer on SHORT_NAME, it allows reporting and integration components to retrieve, for each level, the physical table/view that stores the level values, the source system that owns the level, ordering and display attributes, and design-time flags. It is a metadata view rather than a transactional data view: consumers use it to discover and drive generation of dimension queries.

Underlying Base Objects

The documented ETRM metadata lists no separately documented base objects; the view is defined directly over two other views:

The join condition is BIS_LEV.SHORT_NAME = BSC_LEV.SHORT_NAME, so the two registries are correlated by level short name. Only levels present in both views are returned.

Key Columns

  • SHORT_NAME — the join key and logical identifier of the dimension level.
  • LEVEL_ID / DIM_LEVEL_ID / EDW_DIM_ID / EDW_DIM_LEVEL_ID — identifiers linking the level to its dimension definition and to the enterprise data warehouse hierarchy.
  • APPLICATION_ID — identifies the owning Oracle application.
  • DECODE(...) — a derived column selecting LEVEL_VALUES_VIEW_NAME when SOURCE is 'PMF', otherwise LEVEL_VIEW_NAME; this is the effective values view for the level.
  • DATA_SOURCE — NVL(SOURCE, 'BSC'); flags where the level originates (PMF, BSC, or default BSC).
  • WHERE_CLAUSE, LEVEL_TABLE_NAME, TABLE_TYPE, LEVEL_PK_COL — the physical access metadata used to build level queries.
  • TOTAL_DISP_NAME, COMP_DISP_NAME, ABBREVIATION, COMPARISON_LABEL_CODE — presentation labels for totals and comparisons.
  • VALUE_ORDER_BY, COMP_ORDER_BY — ordering expressions for value and comparison listings.
  • EDW_FLAG — NVL(EDW_FLAG, 0), indicating EDW participation.
  • USER_KEY_SIZE, DISP_KEY_SIZE — key length attributes for user and display keys.
  • MASTER_LEVEL, LONG_LOV, DEFAULT_SEARCH, ENABLED, DRILL_TO_FORM_FUNCTION, HIDE_IN_DESIGN — UI and behavior controls governing level display and drill-down.
  • CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — standard audit columns.

Common Use Cases and Queries

Typical scenarios include validating which dimension levels are enabled for a given source, extracting level table/view metadata for custom extract generation, and reconciling BIS and BSC level definitions by short name.

List enabled levels and their effective views:

  • SELECT SHORT_NAME, LEVEL_ID, DATA_SOURCE, LEVEL_TABLE_NAME, LEVEL_VIEW_NAME FROM APPS.BSC_BIS_DIM_OBJS_VL WHERE ENABLED = 'Y';

Filter levels by source for PMF-targeted integration:

  • SELECT SHORT_NAME, LEVEL_VIEW_NAME, WHERE_CLAUSE FROM APPS.BSC_BIS_DIM_OBJS_VL WHERE DATA_SOURCE = 'PMF';

Identify EDW-relevant levels and their warehouse identifiers:

  • SELECT SHORT_NAME, EDW_FLAG, EDW_DIM_ID, EDW_DIM_LEVEL_ID FROM APPS.BSC_BIS_DIM_OBJS_VL WHERE EDW_FLAG = 1;

Because the view is a join over level registries rather than large fact tables, these queries are lightweight and safe for metadata-driven runtime use.