Search Results bsc_sys_com_dim_levels

Overview

The BSC_SYS_COM_DIM_LEVELS table is a core data repository within the Oracle E-Business Suite Balanced Scorecard (BSC) module, versions 12.1.1 and 12.2.2. It serves as a critical junction table that defines and manages the hierarchical relationships between common dimensions and the specific tabs or perspectives within a scorecard. Its primary role is to store the mapping and structural information that dictates how dimension levels (such as Geography, Organization, or Time hierarchies) are organized and presented across different analytical tabs, enabling consistent dimensional analysis and data roll-up throughout the BSC application.

Key Information Stored

The table's structure centers on establishing these hierarchical relationships within a specific tab context. The primary key is a composite of DIM_LEVEL_INDEX and TAB_ID, ensuring a unique definition for each dimension level's position within a tab. Key columns include TAB_ID, which links to the BSC_TABS_B table to identify the specific scorecard tab or perspective. The DIM_LEVEL_ID column references BSC_SYS_DIM_LEVELS_B to identify the specific dimension level (e.g., "Country" within a "Geography" dimension). The PARENT_DIM_LEVEL_ID, also referencing BSC_SYS_DIM_LEVELS_B, defines the immediate parent level in the hierarchy (e.g., "Region" for "Country"), establishing the roll-up path. The DIM_LEVEL_INDEX column indicates the ordinal position or sequence of this level within the hierarchy for the given tab.

Common Use Cases and Queries

This table is essential for queries that traverse dimensional hierarchies for reporting, data validation, and configuration audits within the Balanced Scorecard. A common use case is to generate a report of all dimension hierarchies defined for a specific tab. A typical query would join BSC_SYS_COM_DIM_LEVELS with BSC_TABS_B to get the tab name and with BSC_SYS_DIM_LEVELS_B (aliased twice) to get the names of the current and parent dimension levels.

• Hierarchy Validation: SQL to identify orphaned dimension levels or validate parent-child relationships for a given TAB_ID.
• Configuration Analysis: Querying the DIM_LEVEL_INDEX to understand the order and depth of dimensions used in a specific analytical perspective.
• Impact Analysis: Determining which tabs are affected by a change to a specific dimension level by querying for a given DIM_LEVEL_ID.

Related Objects

The BSC_SYS_COM_DIM_LEVELS table is centrally connected to other core BSC configuration tables via documented foreign key relationships.

• BSC_TABS_B: Linked via TAB_ID. This table stores master information about the tabs or perspectives within the scorecard.
• BSC_SYS_DIM_LEVELS_B: Linked via two separate foreign keys.
  • DIM_LEVEL_ID: References the primary key of BSC_SYS_DIM_LEVELS_B to identify the specific dimension level used.
  • PARENT_DIM_LEVEL_ID: References the primary key of BSC_SYS_DIM_LEVELS_B to identify the parent level, thereby defining the hierarchical structure.

These relationships ensure referential integrity and form the backbone of the dimensional model in the Balanced Scorecard, connecting the abstract dimension definitions to their concrete application within scorecard user interfaces.