Search Results bsc_sys_dim_level_rels

Overview

The table BSC_SYS_DIM_LEVEL_RELS is a core data structure within the now-obsolete Balanced Scorecard (BSC) module of Oracle E-Business Suite (EBS) versions 12.1.1 and 12.2.2. Its primary function is to define and store the hierarchical relationships between different levels within a business dimension. In the context of a Balanced Scorecard, dimensions such as Organization, Geography, or Product are often structured in parent-child hierarchies (e.g., Region > Country > State). This table acts as the system of record for these hierarchical linkages, enabling the application to navigate dimension hierarchies for data aggregation, security, and analytical reporting. It is critical for the dimensional modeling that underpins scorecard metrics and strategic performance analysis.

Key Information Stored

The table's structure is designed to capture a simple yet essential relationship. It consists of two primary columns that form a composite primary key. The DIM_LEVEL_ID column stores the unique identifier for a specific child level within a dimension hierarchy. The PARENT_DIM_LEVEL_ID column stores the unique identifier for the immediate parent level of the child level specified in DIM_LEVEL_ID. The relationship defined by a row in this table is that the dimension level identified by DIM_LEVEL_ID is a direct child of the dimension level identified by PARENT_DIM_LEVEL_ID. This allows for the construction of multi-level hierarchies through a series of such parent-child records.

Common Use Cases and Queries

The primary use case is to query and traverse dimension hierarchies for reporting and data roll-up. A common analytical query would involve joining this table to the dimension levels master table (BSC_SYS_DIM_LEVELS_B) to retrieve the names or codes of related levels. For instance, to list all parent-child relationships for a specific dimension, one might execute a query such as: SELECT child.LEVEL_NAME AS CHILD_LEVEL, parent.LEVEL_NAME AS PARENT_LEVEL FROM BSC_SYS_DIM_LEVEL_RELS rel JOIN BSC_SYS_DIM_LEVELS_B child ON rel.DIM_LEVEL_ID = child.DIM_LEVEL_ID JOIN BSC_SYS_DIM_LEVELS_B parent ON rel.PARENT_DIM_LEVEL_ID = parent.DIM_LEVEL_ID WHERE child.DIMENSION_ID = :p_dimension_id;. This data is fundamental for generating hierarchical picklists, enforcing data security based on hierarchy (e.g., a manager sees data for their child organizations), and correctly aggregating metric values from lower to higher levels in the scorecard.

Related Objects

The BSC_SYS_DIM_LEVEL_RELS table has a direct and exclusive dependency on the dimension levels master table, BSC_SYS_DIM_LEVELS_B. This is evidenced by the two documented foreign key constraints. Both key columns in BSC_SYS_DIM_LEVEL_RELS reference the same parent table.

• BSC_SYS_DIM_LEVELS_B: The table is referenced twice. The foreign key on DIM_LEVEL_ID ensures that every child level ID stored in the relationship table is a valid, existing dimension level. The foreign key on PARENT_DIM_LEVEL_ID ensures the same validity for every parent level ID. This enforces referential integrity across the entire dimension hierarchy structure.