Search Results edw_dim_level_name

Overview

BSC_SYS_DIM_LEVELS_B is the base table that stores dimension level definitions for Oracle Balanced Scorecard (BSC), an obsolete product in Oracle E-Business Suite 12.1.1 and 12.2.2. A dimension level represents a specific hierarchical tier or structural position within a BSC dimension, such as a product category level, an organizational unit level, or a fiscal period level. This table carries the physical, non-translatable attributes of each level, including the underlying database objects and ordering rules that govern how level members are retrieved and displayed.

Because the table's primary key column, DIM_LEVEL_ID, is referenced by numerous dependent tables throughout the BSC schema, the object functions as a central definition point. Under the heuristic Data Vault classification derived from its foreign key structure, this table leans toward a hub: it holds a stable, uniquely identified business concept (the dimension level) that many satellites and link-style tables point to. Note that BSC is documented as obsolete, and the ETRM metadata states the object is "Not implemented in this database," so it will appear only in environments where the legacy Balanced Scorecard module was previously installed.

Key Information Stored

The table is owned by the BSC schema and contains 22 documented columns. The most significant are:

• DIM_LEVEL_ID — the surrogate primary key, enforced by BSC_SYS_DIM_LEVELS_B_PK. This value is the join key for every dependent table.
• LEVEL_TABLE_NAME — the physical table that supplies the members of the level.
• TABLE_TYPE — indicates the nature of the backing table (for example, fact, dimension, or lookup).
• LEVEL_PK_COL — the primary key column of the level's source table.
• LEVEL_VIEW_NAME — the view through which the level is exposed to BSC functionality.
• ABBREVIATION, SHORT_NAME — display identifiers used in scorecard layouts and reports.
• VALUE_ORDER_BY, COMP_ORDER_BY — sort clauses applied to level values and comparisons.
• CUSTOM_GROUP, USER_KEY_SIZE, DISP_KEY_SIZE — structural and key-composition attributes.
• EDW_FLAG, EDW_DIM_ID, EDW_DIM_LEVEL_ID, EDW_DIM_LEVEL_NAME — warehouse integration attributes mapping the level to enterprise data warehouse structures.
• SOURCE and the standard audit columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN) — provenance and change tracking.

A unique index, BSC_SYS_DIM_LEVELS_B_U1 on DIM_LEVEL_ID, is documented as a business-key candidate, though it duplicates the primary key column rather than introducing a separate natural key.

Common Use Cases and Queries

Typical usage centers on resolving level metadata for dimension-aware reports and validating dimension hierarchies. A common pattern joins the base table to its translation sibling:

• List all levels with their translated names: SELECT b.DIM_LEVEL_ID, b.LEVEL_TABLE_NAME, t.LEVEL_NAME FROM BSC_SYS_DIM_LEVELS_B b, BSC_SYS_DIM_LEVELS_TL t WHERE b.DIM_LEVEL_ID = t.DIM_LEVEL_ID AND t.LANGUAGE = USERENV('LANG');
• Identify all levels backed by a given table: SELECT DIM_LEVEL_ID, ABBREVIATION FROM BSC_SYS_DIM_LEVELS_B WHERE LEVEL_TABLE_NAME = :table_name;
• Trace which KPIs depend on a level via BSC_KPI_DIM_LEVEL_PROPERTIES, and which filters restrict it via BSC_SYS_FILTERS.
• Audit warehouse mappings using the EDW_* columns to confirm which levels are surfaced to the enterprise data warehouse.

Migration and upgrade assessments frequently query this table to inventory legacy dimension definitions before decommissioning the BSC module.

Related Objects

The following objects reference BSC_SYS_DIM_LEVELS_B through DIM_LEVEL_ID and are the most significant dependencies:

• BSC_SYS_DIM_LEVELS_TL — translated level names and descriptions; join on DIM_LEVEL_ID.
• BSC_SYS_DIM_LEVEL_COLS — columns associated with each level; join on DIM_LEVEL_ID.
• BSC_SYS_DIM_LEVEL_RELS — level-to-level relationships; references both DIM_LEVEL_ID and PARENT_DIM_LEVEL_ID.
• BSC_SYS_COM_DIM_LEVELS — common dimension level assignments; references DIM_LEVEL_ID and PARENT_DIM_LEVEL_ID.
• BSC_SYS_DIM_LEVELS_BY_GROUP — groups levels by category; join on DIM_LEVEL_ID.
• BSC_KPI_DIM_LEVEL_PROPERTIES — KPI properties tied to a dimension level; join on DIM_LEVEL_ID.
• BSC_SYS_FILTERS and BSC_SYS_FILTERS_VIEWS — filter definitions and filter views scoped to a dimension level; both join on DIM_LEVEL_ID.