Search Results bisbv_dimension_levels

Overview

BISBV_DIMENSION_LEVELS is a read-only view owned by the APPS schema in Oracle E-Business Suite releases 12.1.1 and 12.2.2. It belongs to the BIS (Applications BIS) product family, which supplies the dimensional modeling and business intelligence infrastructure used across the EBS reporting stack. The view exposes the hierarchical level definitions that belong to each dimension registered in the BIS repository, joining the base level definition table to its translation table to return language-appropriate names and descriptions.

In the EBS reporting and integration architecture, dimension levels define the granularity at which facts can be aggregated — for example, a Time dimension may expose Day, Month, Quarter, and Year levels, while an Organisation dimension may expose Department, Cost Centre, and Legal Entity levels. BISBV_DIMENSION_LEVELS is therefore the primary catalogue that BI Publisher reports, custom OLAP-style extracts, and third-party integration layers query when they need to enumerate the levels available for a given dimension. Because it is defined with the WITH READ ONLY clause, it cannot be used as a DML target, which reinforces its role as a metadata publishing object rather than a maintenance object.

Underlying Base Objects

The ETRM metadata for this view does not document an explicit list of referenced base objects; however, the published view text identifies them directly. BISBV_DIMENSION_LEVELS is defined as an inner join between two tables:

• BIS_LEVELS — the base table holding the physical level definition, including the surrogate key, the short name, the owning dimension identifier, the name of the view that materializes the level's values, and the optional filter clause.
• BIS_LEVELS_TL — the translation ("_TL") table holding the language-dependent NAME and DESCRIPTION attributes, keyed by LEVEL_ID and LANGUAGE.

The join predicate links BIS_LEVELS.LEVEL_ID to BIS_LEVELS_TL.LEVEL_ID, and the WHERE clause restricts the translation row to the session language via USERENV('LANG'). This is the standard EBS multilingual pattern: one row per level for the current runtime language, regardless of how many translations exist in the base translation table. The WITH READ ONLY suffix prevents DML through the view. Note that the column aliases exposed by the view (DIMENSION_LEVEL_ID, DIMENSION_LEVEL_SHORT_NAME, DIMENSION_LEVEL_NAME) differ from the underlying column names in BIS_LEVELS (LEVEL_ID, SHORT_NAME) and BIS_LEVELS_TL (NAME).

Key Columns

• DIMENSION_LEVEL_ID — the unique level identifier, sourced from BIS_LEVELS.LEVEL_ID. This is the primary key used to join level metadata to fact and hierarchy definitions.
• DIMENSION_LEVEL_SHORT_NAME — the internal short name of the level (BIS_LEVELS.SHORT_NAME), typically used in generated SQL and programmatic references.
• DIMENSION_LEVEL_NAME and DESCRIPTION — the translated display name and long description (BIS_LEVELS_TL.NAME and BIS_LEVELS_TL.DESCRIPTION), returned in the session language.
• DIMENSION_ID — identifies the parent dimension to which the level belongs, allowing report authors to group levels by dimension.
• LEVEL_VALUES_VIEW_NAME — the name of the database view that resolves the dimension members at this level. This is the essential column for dynamic reporting, as it tells an integration layer where to source the level's value list.
• WHERE_CLAUSE — an optional predicate fragment applied when the level's values are queried, supporting filtered level membership.
• CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — the standard EBS audit columns, unchanged from the base table.

Common Use Cases and Queries

The view is principally used to discover the levels available for a dimension before generating aggregation SQL, and to resolve the view name that supplies level values. A typical query lists all levels for a dimension:

• SELECT dimension_level_id, dimension_level_short_name, dimension_level_name, level_values_view_name FROM apps.bisbv_dimension_levels WHERE dimension_id = :p_dimension_id ORDER BY dimension_level_name;
• SELECT dimension_id, COUNT(*) FROM apps.bisbv_dimension_levels GROUP BY dimension_id; — enumerates level counts per dimension.
• SELECT dimension_level_name, where_clause FROM apps.bisbv_dimension_levels WHERE level_values_view_name = :p_view_name; — reverse lookup from a values view to its level definition.

Because the view filters on USERENV('LANG'), results follow the language of the connected session; reports intended for multilingual audiences should therefore be executed under the appropriate NLS configuration. All queries must be issued against the APPS synonym, since the object is owned by APPS and is not intended for direct access under another schema.