Results for “bsc_sys_dim_groups_tl”

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

Overview

BSC_SYS_DIM_GROUPS_TL is the translation table for dimension group definitions within the Oracle E-Business Suite Balanced Scorecard (BSC) module. Balanced Scorecard provides the framework by which organizations define, cascade, and monitor key performance indicators across strategic perspectives. Dimension groups are the logical containers that organize the analytical dimensions — such as time, geography, product line, or organizational unit — against which scorecard metrics are evaluated. Because Oracle EBS is deployed in multilingual environments, every user-facing dimension group name must be available in the languages enabled for the instance. This table stores the language-specific, translated attributes of each dimension group, while the base (untranslated) attributes reside in the corresponding base table.

From a heuristic Data Vault modeling perspective, the metadata classifies this object as hub-leaning. The classification is derived from the foreign key structure in which BSC_SYS_DIM_GROUPS_TL is referenced by downstream tables. In a Data Vault interpretation, the table would function as the central reference point that consumers (BSC_KPI_DIM_GROUPS and BSC_SYS_DIM_LEVELS_BY_GROUP) join back to when resolving dimension group identity and translated labels.

Key Information Stored

The object is owned by the BSC schema and contains 10 documented columns in ETRM 12.1.1. The most significant are:

• DIM_GROUP_ID — The surrogate identifier for the dimension group. It is the single-column component of the primary key BSC_SYS_DIM_GROUPS_TL_PK and the primary join to all dependent tables.
• LANGUAGE — The language code identifying the locale of the translated row. Together with DIM_GROUP_ID, LANGUAGE forms the unique business-key candidate BSC_SYS_DIM_GROUPS_TL_U1, guaranteeing one translation per group per language.
• SOURCE_LANG — The language of the source record from which the translation was derived, used by the translation framework to determine which row is the original.
• NAME — The translated display name of the dimension group, presented in scorecard setup and reporting screens.
• SHORT_NAME — The abbreviated translated label used where display space is constrained, such as dashboard tiles and column headers.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — The standard Oracle EBS audit columns recording the creating and last-updating user, the associated login, and the timestamps of those operations.

The distinction between the surrogate key (DIM_GROUP_ID in the primary key) and the composite business-key candidate (DIM_GROUP_ID plus LANGUAGE) is central to query design: the surrogate key identifies the group, the pair identifies a specific translated row.

Common Use Cases and Queries

The most frequent requirement is retrieving the dimension group name in the session's language. A typical pattern joins the translation table filtered by LANGUAGE, for example:

• SELECT t.dim_group_id, t.name, t.short_name FROM bsc.bsc_sys_dim_groups_tl t WHERE t.language = USERENV('LANG') ORDER BY t.name;
• Multilingual reporting that lists every available translation: SELECT dim_group_id, language, name FROM bsc.bsc_sys_dim_groups_tl ORDER BY dim_group_id, language;
• Resolving labels for KPI assignments by joining through BSC_KPI_DIM_GROUPS on DIM_GROUP_ID to produce language-correct KPI-to-dimension listings.
• Translation-completeness auditing: comparing row counts per DIM_GROUP_ID against the set of installed languages to identify missing NAME or SHORT_NAME values.

Because translation rows are maintained by the standard EBS translation mechanism, direct DML should be avoided; updates are made through the Balanced Scorecard setup forms or the supported translation APIs, which keep SOURCE_LANG and the audit columns consistent.

Related Objects

The documented foreign key relationships and the surrounding BSC schema identify the following significant objects:

• BSC_KPI_DIM_GROUPS — References BSC_SYS_DIM_GROUPS_TL on DIM_GROUP_ID and links KPI definitions to the dimension groups against which they are evaluated.
• BSC_SYS_DIM_LEVELS_BY_GROUP — References this table on DIM_GROUP_ID and holds the dimension level members belonging to each group, supporting hierarchical drill-down.
• The base (non-translated) dimension groups table within the BSC schema, which carries the language-independent definition keyed by the same DIM_GROUP_ID.
• The standard Oracle EBS multi-language infrastructure that populates SOURCE_LANG, LANGUAGE, and the audit columns during translation.

Together these objects let developers and reporting authors resolve dimension group identity, hierarchy, KPI associations, and translated presentation labels from a single surrogate key.