Results for “bsc_kpi_dim_levels_tl”

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

Overview

BSC_KPI_DIM_LEVELS_TL is the translation (language) table for dimension levels within the Oracle E-Business Suite Balanced Scorecard (BSC) module. It stores the multilingual, user-facing text associated with each level of a KPI dimension set — the hierarchical breakdowns (such as region, product line, or organizational tier) against which scorecard metrics are aggregated and reported. In Oracle EBS 12.1.1 and 12.2.2, this table is owned by the BSC schema and is documented as VALID. Its stated product purpose is "Translated dimension levels information."

The table follows the standard EBS translated-table pattern: the base (language-independent) attributes reside in BSC_KPI_DIM_LEVELS_B, and the descriptive, language-dependent columns reside here, keyed by LANGUAGE. The documented foreign key maps DIM_LEVEL_INDEX, INDICATOR, and DIM_SET_ID in this table back to BSC_KPI_DIM_LEVELS_B, confirming the one-to-many relationship between a base dimension-level record and its translated rows.

From a Data Vault modeling perspective, the heuristic classification of this object is satellite-leaning. It carries descriptive, context-dependent attributes (names and display labels) attached to a parent business key rather than independent entities or associations. Practitioners designing a warehouse layer may therefore treat it as a satellite associated with the dimension-level hub represented by the _B table.

Key Information Stored

The table contains nine documented columns. The most significant are:

• INDICATOR — Part of the composite primary key and of the unique business-key index; identifies the scorecard indicator context to which the dimension level belongs.
• DIM_SET_ID — Identifies the dimension set; part of the primary key and unique index.
• DIM_LEVEL_INDEX — The ordinal position or index of the level within the dimension set; part of the primary key and the FK to BSC_KPI_DIM_LEVELS_B.
• LANGUAGE — The language code of the translated row; part of the primary key and the discriminator that makes this a _TL table.
• SOURCE_LANG — The source language of the base record, used by the translation framework to resolve the origin of the text.
• NAME — The translated name of the dimension level, presented to users in the selected language.
• HELP — Translated help or descriptive text shown alongside the dimension level.
• TOTAL_DISP_NAME — The translated display name used for the "total" or aggregate row of the dimension level.
• COMP_DISP_NAME — The translated display name used for the comparison view of the dimension level.

The surrogate primary key is BSC_KPI_DIM_LEVELS_TL_PK, defined over (DIM_LEVEL_INDEX, DIM_SET_ID, LANGUAGE, INDICATOR). The documented unique index BSC_KPI_DIM_LEVELS_TL_U1 covers (INDICATOR, DIM_SET_ID, DIM_LEVEL_INDEX, LANGUAGE) and constitutes the business-key candidate, enforcing one translated row per dimension level per language.

Common Use Cases and Queries

Typical usage involves joining the translation table to its base table to retrieve localized level names for scorecard reporting, dashboards, and interface list-of-values. A representative query resolves dimension levels for a specific language:

• Join pattern: BSC_KPI_DIM_LEVELS_TL TL joined to BSC_KPI_DIM_LEVELS_B B on TL.DIM_LEVEL_INDEX = B.DIM_LEVEL_INDEX AND TL.DIM_SET_ID = B.DIM_SET_ID AND TL.INDICATOR = B.INDICATOR, filtered by TL.LANGUAGE = :p_lang.
• Reporting: list all translated level names and display names for a given DIM_SET_ID, ordered by DIM_LEVEL_INDEX, to render an accurate hierarchical drill-down.
• Administration: verify that every base level has a corresponding translated row, or identify languages missing NAME entries for a dimension set.
• Localization: compare SOURCE_LANG against LANGUAGE to detect rows that remain untranslated or fall back to the source language.

Related Objects

• BSC_KPI_DIM_LEVELS_B — The base dimension-levels table; the FK from this translation table references it on DIM_LEVEL_INDEX, INDICATOR, and DIM_SET_ID.
• BSC_KPI_DIM_LEVELS_TL_PK — Primary key constraint enforcing uniqueness of the composite key.
• BSC_KPI_DIM_LEVELS_TL_U1 — Unique index acting as the business-key candidate.
• BSC_KPI_DIMENSIONS_B / _TL — Parent dimension definitions that own dimension sets referenced by DIM_SET_ID.
• BSC_KPI_INDICATORS_B / _TL — Indicator definitions referenced by the INDICATOR column.
• BSC_KPI_DIM_LEVELS_VL — The conventional EBS view that unions base and translation rows for direct query access.