Search Results bsc_sys_dim_level_rels_n1

Overview

The BSC_SYS_DIM_LEVEL_RELS table is a core data dictionary object within the Oracle E-Business Suite (EBS) Balanced Scorecard (BSC) module. Its primary role is to define and store the hierarchical relationships between different levels of a dimension, which is a fundamental concept for organizing and analyzing performance data. This table enables the system to understand how dimension members (e.g., individual cities) roll up to their parents (e.g., regions, countries), thereby supporting the creation of drill-down paths and aggregated reporting essential for strategic performance management in releases 12.1.1 and 12.2.2.

Key Information Stored

The table's structure is designed to capture the specifics of each level-to-level relationship. The most critical columns include DIM_LEVEL_ID, which identifies the child level in the relationship, and PARENT_DIM_LEVEL_ID, which identifies the immediate parent level. The RELATION_TYPE column defines the cardinality of the relationship (e.g., one-to-many or many-to-many). The RELATION_COL field specifies the database column that physically stores the foreign key linking the two levels in the application tables. Supplemental columns like DATA_SOURCE and DATA_SOURCE_TYPE provide metadata on the origin of the relationship data, while DIRECT_RELATION acts as a flag to indicate if the relationship is immediate or requires traversal through intermediate levels.

Common Use Cases and Queries

This table is central to metadata-driven queries that navigate dimension hierarchies. A primary use case is generating a hierarchy map for a given dimension to understand its structure. Developers and system administrators might query it to troubleshoot drill-down functionality or to validate configuration. A typical query to retrieve the full parent-child relationship structure for analysis or validation would be:

• SELECT dim_level_id, parent_dim_level_id, relation_type, relation_col FROM bsc.bsc_sys_dim_level_rels ORDER BY dim_level_id;

Another common pattern is to join this table with BSC_SYS_DIM_LEVELS_B (or similar dimension level tables) to get the descriptive names of the levels involved, enabling more readable reports on the configured dimension hierarchies.

Related Objects

As a foundational metadata table, BSC_SYS_DIM_LEVEL_RELS is referenced by various application objects within the BSC schema. The provided dependency information confirms it is referenced by an APPS synonym (BSC_SYS_DIM_LEVEL_RELS), which is the standard access point for application code. It has a direct logical relationship with the table BSC_SYS_DIM_LEVELS_B, which stores the definition of the individual dimension levels themselves (like 'City', 'State'). The two unique indexes, BSC_SYS_DIM_LEVEL_RELS_U1 on (DIM_LEVEL_ID, PARENT_DIM_LEVEL_ID) and BSC_SYS_DIM_LEVEL_RELS_N1 on RELATION_COL, are critical for enforcing data integrity and optimizing query performance for hierarchy navigation.