Results for “bsc_kpi_dim_levels_b”

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

Overview

BSC_KPI_DIM_LEVELS_B is a base table in the BSC (Balanced Scorecard) schema of Oracle E-Business Suite, documented as VALID in both release 12.1.1 and 12.2.2. Its description is "Dimension levels information." The table defines the hierarchical levels that make up a KPI dimension set — for example, a "Time" dimension might contain levels such as Year, Quarter, Month, and Day, or a "Region" dimension might contain Country, State, and City. Each row describes one level within a dimension set, together with the physical source table or view, the filter and default behavior, parent-child relationships between levels, display ordering, and status.

The table is multilingual: it stores language-independent attributes in the base (_B) table, while translated level names and descriptions live in BSC_KPI_DIM_LEVELS_TL. This base/translation split is the standard EBS pattern for seeded and user-defined setup data.

Applying the heuristic Data Vault classification supplied in the metadata, BSC_KPI_DIM_LEVELS_B is hub-leaning. It holds a stable, uniquely identified set of dimension-level entities keyed by business attributes, which suggests modeling it as a hub with its translatable descriptive attributes separated into a satellite. The _TL and _USER tables participate as dependent structures rather than independent hubs.

Key Information Stored

The table contains 30 documented columns under the BSC schema in 12.1.1. The primary key is BSC_KPI_DIM_LEVELS_B_PK, defined on the composite of DIM_LEVEL_INDEX, DIM_SET_ID, and INDICATOR. A separate unique index, BSC_KPI_DIM_LEVELS_B_U1, is defined on INDICATOR, DIM_SET_ID, and DIM_LEVEL_INDEX and serves as the business-key candidate, distinct from the surrogate PK constraint.

• INDICATOR — the scorecard indicator to which the dimension level belongs; part of the primary key and the business-key index.
• DIM_SET_ID — identifies the dimension set (for example, the "Time" dimension) that owns the level; foreign key to BSC_KPI_DIM_SETS_TL.
• DIM_LEVEL_INDEX — the ordinal position of the level within the dimension set; part of the primary key.
• LEVEL_TABLE_NAME and LEVEL_VIEW_NAME — the physical table and/or view that supplies the level's members at runtime.
• FILTER_COLUMN and FILTER_VALUE — constrain which rows from the source table qualify as members of the level.
• DEFAULT_VALUE, DEFAULT_TYPE, and DEFAULT_KEY_VALUE — define the default member selection for the level when no explicit choice is made.
• LEVEL_PK_COL — the primary key column of the underlying level table or view.
• PARENT_LEVEL_INDEX and PARENT_LEVEL_REL — describe the parent level and the relational predicate linking a level to its parent; PARENT_LEVEL_INDEX2 and PARENT_LEVEL_REL2 provide the secondary (alternate) hierarchy path.
• TABLE_RELATION — the join condition between this level's source and its parent level's source.
• STATUS — active/inactive flag controlling whether the level participates in scorecard processing.
• POSITION, VALUE_ORDER_BY, and COMP_ORDER_BY — control display ordering and comparison ordering of level members.
• PARENT_IN_TOTAL — indicates whether the parent member is included in the level's total aggregation.
• USER_LEVEL0 through USER_LEVEL2_DEFAULT — user-assigned override levels and their defaults.
• TARGET_LEVEL — flags the level used as the target for KPI computation.

Common Use Cases and Queries

This table is primarily read by the Balanced Scorecard engine at runtime to resolve the SQL needed to expand a dimension into its member hierarchy. It is also queried during configuration of KPI definitions, during migration of dimension setups between environments, and in reporting on scorecard setup completeness.

A typical query lists the levels of a dimension set in display order:

SELECT DIM_LEVEL_INDEX, LEVEL_VIEW_NAME, STATUS, POSITION FROM BSC_KPI_DIM_LEVELS_B WHERE DIM_SET_ID = :p_dim_set_id AND INDICATOR = :p_indicator ORDER BY POSITION;

To retrieve translated level names, join to the translation table on the full business key:

SELECT B.DIM_LEVEL_INDEX, T.NAME, B.STATUS FROM BSC_KPI_DIM_LEVELS_B B, BSC_KPI_DIM_LEVELS_TL T WHERE B.DIM_SET_ID = T.DIM_SET_ID AND B.INDICATOR = T.INDICATOR AND B.DIM_LEVEL_INDEX = T.DIM_LEVEL_INDEX AND T.LANGUAGE = USERENV('LANG');

To inspect hierarchy linkage between levels, select PARENT_LEVEL_INDEX and TABLE_RELATION for rows sharing the same DIM_SET_ID and INDICATOR. Configuration checks often filter on STATUS to find inactive or inconsistent levels.

Related Objects

• BSC_KPI_DIM_SETS_TL — referenced by BSC_KPI_DIM_LEVELS_B via DIM_SET_ID; defines the dimension set that owns the level.
• BSC_KPI_DIM_LEVELS_TL — translation table joining on DIM_SET_ID, INDICATOR, and DIM_LEVEL_INDEX; holds language-specific level names.
• BSC_KPI_DIM_LEVELS_USER — user-level overrides joining on DIM_SET_ID, INDICATOR, and DIM_LEVEL_INDEX.
• BSC_KPI_DIM_LEVELS_B_PK — primary key constraint on DIM_LEVEL_INDEX, DIM_SET_ID, INDICATOR.
• BSC_KPI_DIM_LEVELS_B_U1 — unique index on INDICATOR, DIM_SET_ID, DIM_LEVEL_INDEX; the business-key candidate.
• BSC schema tables under the KPI dimension family — indicator definitions and scorecard configuration tables reference DIM_SET_ID and INDICATOR when building runtime dimension queries.