Search Results bsc_sys_dim_levels_by_group

Overview

The table BSC_SYS_DIM_LEVELS_BY_GROUP is a core data structure within the now-obsolete Balanced Scorecard (BSC) module of Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2. It functions as a junction or mapping table, defining the many-to-many relationship between dimension groups and dimension levels. In the context of the Balanced Scorecard, dimension groups are logical collections used for organizing and analyzing performance data, while dimension levels represent specific tiers or hierarchies within a dimension (e.g., Country, State, City within a Geography dimension). This table's primary role was to associate specific levels of detail with the groups used for scorecard reporting and analysis, thereby controlling the granularity of data available within each analytical group.

Key Information Stored

The table's structure is defined by a composite primary key, which also constitutes the entirety of its core data columns. The primary purpose is to store association records, not descriptive attributes. The key columns are:

• DIM_LEVEL_ID: A foreign key column referencing a unique dimension level defined in the BSC_SYS_DIM_LEVELS_B table. This identifies the specific hierarchical level (e.g., "Region") being assigned to a group.
• DIM_GROUP_ID: A foreign key column referencing a unique dimension group defined in the BSC_SYS_DIM_GROUPS_TL table. This identifies the analytical group to which the dimension level is made available.

The combination of these two IDs forms the primary key (BSC_SYS_DIM_LEVELS_BY_GROUP_PK), ensuring that any specific dimension level can only be associated with a given dimension group once.

Common Use Cases and Queries

This table was central to administrative setup and data security within the BSC module. A primary use case was during the configuration of a scorecard perspective or objective, where an administrator would need to determine which dimension levels were permissible for data entry or reporting within a selected dimension group. For reporting and validation, common queries would involve joining to the related descriptive tables. For example, to list all dimension levels available for a specific group named 'Sales Geography', a query would join through this association table:

SELECT dg.NAME, dl.LEVEL_NAME
FROM BSC_SYS_DIM_GROUPS_TL dg,
BSC_SYS_DIM_LEVELS_B dl,
BSC_SYS_DIM_LEVELS_BY_GROUP dlg
WHERE dlg.DIM_GROUP_ID = dg.DIM_GROUP_ID
AND dlg.DIM_LEVEL_ID = dl.DIM_LEVEL_ID
AND dg.NAME = 'Sales Geography';

Conversely, to find all groups that have access to a specific dimension level, the join logic would be reversed, starting from the dimension level.

Related Objects

The functionality of BSC_SYS_DIM_LEVELS_BY_GROUP is entirely dependent on its relationships with two primary master tables, as defined by its foreign key constraints:

• BSC_SYS_DIM_GROUPS_TL: This is the translatable table storing the names and descriptions of dimension groups. The relationship is established via BSC_SYS_DIM_LEVELS_BY_GROUP.DIM_GROUP_ID → BSC_SYS_DIM_GROUPS_TL.
• BSC_SYS_DIM_LEVELS_B: This is the base table defining the individual dimension levels within the system. The relationship is established via BSC_SYS_DIM_LEVELS_BY_GROUP.DIM_LEVEL_ID → BSC_SYS_DIM_LEVELS_B.

These relationships ensure referential integrity, meaning a record in this mapping table cannot exist without a valid corresponding record in both the dimension group and dimension level master tables.