Search Results bsc_kpi_dim_levels_tl

Overview

The BSC_KPI_DIM_LEVELS_TL table is a core data object within the Oracle E-Business Suite Balanced Scorecard (BSC) application. It functions as a translation table, storing multilingual descriptions for dimension levels used in Key Performance Indicator (KPI) definitions. In the context of EBS 12.1.1 and 12.2.2, this table enables the global deployment of the Balanced Scorecard module by allowing dimension level names and descriptions to be presented in a user's preferred language (NLS). It is intrinsically linked to its base table, BSC_KPI_DIM_LEVELS_B, which holds the language-independent structural data.

Key Information Stored

The table stores translated text for dimension level attributes, primarily the name and description. Its structure is defined by a composite primary key that links it to the base table and specifies the language. The critical columns, as per the provided metadata, are:

• DIM_LEVEL_INDEX: A numeric identifier for the specific level within a dimension hierarchy. This is part of the primary key and a foreign key to the base table.
• DIM_SET_ID: Identifies the dimension set to which this level belongs. Part of the primary and foreign key.
• INDICATOR: References the associated KPI or indicator. Part of the primary and foreign key.
• LANGUAGE: The language code (e.g., 'US', 'DE', 'JA') for the translated text. This completes the primary key.
• NAME and DESCRIPTION: While not explicitly listed in the brief metadata, these are the standard columns in Oracle EBS translation tables (TL) that hold the actual translated strings for the dimension level's name and descriptive text.

Common Use Cases and Queries

The primary use case is retrieving user-friendly, localized dimension level information for reports, analytics, and the BSC user interface. A common SQL pattern involves joining this table with its base table to get a complete, language-specific view of dimension levels for a given KPI or dimension set. For example, to fetch all Spanish translations for dimension levels related to a specific indicator, one might use:

SELECT b.DIM_LEVEL_INDEX, t.NAME, t.DESCRIPTION
FROM BSC_KPI_DIM_LEVELS_B b, BSC_KPI_DIM_LEVELS_TL t
WHERE b.INDICATOR = :p_indicator
AND b.DIM_LEVEL_INDEX = t.DIM_LEVEL_INDEX
AND b.DIM_SET_ID = t.DIM_SET_ID
AND b.INDICATOR = t.INDICATOR
AND t.LANGUAGE = 'ES';

This table is essential for any reporting or data extraction process that must present dimension metadata in the end-user's language.

Related Objects

As documented in the provided foreign key metadata, BSC_KPI_DIM_LEVELS_TL has a strict, mandatory relationship with its base table. The related objects and their join columns are:

• Base Table: BSC_KPI_DIM_LEVELS_B
  The TL table's data is meaningless without its corresponding base record. They are joined on three key columns:
  • BSC_KPI_DIM_LEVELS_TL.DIM_LEVEL_INDEX = BSC_KPI_DIM_LEVELS_B.DIM_LEVEL_INDEX
  • BSC_KPI_DIM_LEVELS_TL.INDICATOR = BSC_KPI_DIM_LEVELS_B.INDICATOR
  • BSC_KPI_DIM_LEVELS_TL.DIM_SET_ID = BSC_KPI_DIM_LEVELS_B.DIM_SET_ID

This relationship ensures that every translation record is anchored to a valid, defined dimension level within the Balanced Scorecard's KPI structure.