Results for “parent_dim_level_id”

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

Overview

BSC_SYS_COM_DIM_LEVELS is a table in the BSC schema (Balanced Scorecard module) in Oracle EBS 12.1.1 and 12.2.2. Its documented description is "Common dimension levels information" — it stores the level definitions that make up the common dimension hierarchies used in the Balanced Scorecard / Strategic Enterprise Management (SEM) framework. Within EBS, Balanced Scorecard relies on dimensions and their hierarchical levels to structure scorecard views, KPIs, and analytic reporting, so this table acts as the structural metadata that ties a level to its owning tab (scorecard/dimension container) and to a dimension-level definition.

The documented ETRM metadata classifies this object heuristically as a link in Data Vault terms. This is a modeling suggestion only: the table's role of resolving associations between BSC_TABS_B and BSC_SYS_DIM_LEVELS_B (its two foreign-key targets, one of which is referenced twice) fits a link/relationship table shape more than a pure hub or satellite, since the surrogate key is composed of two identifying columns.

Key Information Stored

The table has five documented columns. The most significant are:

• DIM_LEVEL_INDEX — the level ordinal/index identifying the position of the level. Part of the composite primary key and part of the unique business-key index.
• TAB_ID — foreign key to BSC_TABS_B, identifying the owning tab (scorecard/dimension container). Also part of the composite primary key and unique index.
• DIM_LEVEL_ID — foreign key to BSC_SYS_DIM_LEVELS_B, referencing the underlying dimension-level definition.
• PARENT_DIM_LEVEL_ID — a second foreign key to BSC_SYS_DIM_LEVELS_B, establishing the parent-child relationship for the hierarchy.
• PARENT_LEVEL_INDEX — the index/ordinal of the parent level, enabling reconstruction of the hierarchy ordering.

Surrogate primary key: the BSC_SYS_COM_DIM_LEVELS_PK constraint is documented on the composite of (DIM_LEVEL_INDEX, TAB_ID). A unique business-key candidate is documented as BSC_SYS_COM_DIM_LEVELS_U1 on (TAB_ID, DIM_LEVEL_INDEX). Note that the PK and the unique index cover the same two columns in different order, so they are structurally equivalent sets of attributes rather than distinct keys — this is common in EBS where the PK leads with one column for access paths.

Common Use Cases and Queries

Typical reporting and integration uses include retrieving all levels for a given tab, and traversing parent-child relationships within a common dimension.

• List levels for a scorecard tab, ordered by index:
  SELECT TAB_ID, DIM_LEVEL_INDEX, DIM_LEVEL_ID, PARENT_LEVEL_INDEX, PARENT_DIM_LEVEL_ID FROM BSC.BSC_SYS_COM_DIM_LEVELS WHERE TAB_ID = :tab_id ORDER BY DIM_LEVEL_INDEX;
• Resolve a level to its dimension definition and its parent's definition:
  SELECT c.TAB_ID, c.DIM_LEVEL_INDEX, d1.NAME dim_level_name, d2.NAME parent_level_name FROM BSC.BSC_SYS_COM_DIM_LEVELS c, BSC.BSC_SYS_DIM_LEVELS_B d1, BSC.BSC_SYS_DIM_LEVELS_B d2 WHERE c.DIM_LEVEL_ID = d1.DIM_LEVEL_ID AND c.PARENT_DIM_LEVEL_ID = d2.DIM_LEVEL_ID;
• Detect root levels (those with no parent) for a tab, useful for rendering top of hierarchy in scorecard UI.
• Joining to BSC_TABS_B to map a level back to its scorecard tab for cross-tab reporting.

Related Objects

• BSC_TABS_B — referenced by BSC_SYS_COM_DIM_LEVELS.TAB_ID; the parent tab/scorecard container.
• BSC_SYS_DIM_LEVELS_B — referenced by DIM_LEVEL_ID and by PARENT_DIM_LEVEL_ID; provides the dimension-level definitions and their names.
• BSC_SYS_COM_DIM_LEVELS_PK — the composite primary key constraint over (DIM_LEVEL_INDEX, TAB_ID).
• BSC_SYS_COM_DIM_LEVELS_U1 — the unique index on (TAB_ID, DIM_LEVEL_INDEX).