Search Results level_long_name

Overview

EDW_SEC_LVL_INFO_V is a metadata view shipped with the Oracle E-Business Suite (EBS) Business Intelligence System (BIS) product family. Its purpose is to expose descriptive information about the security and dimensional levels defined within the EBS data warehouse metadata layer. The view joins together information from the enterprise data warehouse level metadata with column-level metadata, producing a result set that describes each level by its identifier, name, and long name, while also deriving the naming convention used for the corresponding column in the star schema.

In Oracle EBS 12.1.1 and 12.2.2, this view is part of the internal metadata infrastructure that underpins the EBI (Enterprise Business Intelligence) and Daily Business Intelligence (DBI) reporting layers. It is not a transactional or operational view; rather, it is a catalog-style view that helps reporting engines, ETL processes, and dimensional modeling tools discover how dimensional levels are named and represented. Because it derives a star-schema column name from the level prefix, it plays a supporting role in generating and interpreting star schema queries, allowing reporting components to translate logical level identifiers into the physical column names used in the warehouse.

Underlying Base Objects

The documented view definition is:

• EDW_LEVELS_MD_V — aliased as LVL, this view supplies level metadata including LEVEL_ID, LEVEL_NAME, LEVEL_LONG_NAME, and the LEVEL_PREFIX used to construct the star column name.
• EDW_ALL_COLUMNS_MD_V — aliased as COL, this view supplies column-level metadata and is filtered to the row where COLUMN_NAME = 'NAME'.

The two views are joined on LVL.LEVEL_ID = COL.ENTITY_ID. This join associates each dimensional level with the specific "NAME" column entity that represents it in the warehouse metadata. According to the ETRM documentation, no base tables are documented for this view, and its implementation status is recorded as "Not implemented in this database." The _V suffix and the references to _MD_V objects indicate the view itself is constructed over other metadata views rather than directly over base tables. As a result, its availability depends on whether the EBS data warehouse metadata components (part of BIS/DBI) have been installed and configured in a given environment. The owner is not documented in the supplied metadata.

Key Columns

The view exposes five columns that describe each security/dimensional level:

• DIM_ID — the identifier of the dimension to which the level belongs. It anchors the level within the overall dimensional model.
• LEVEL_ID — the unique identifier for the level. It is the join key between the level metadata and the column metadata.
• LEVEL_NAME — the internal or logical name of the level, typically used programmatically by reporting components.
• LEVEL_LONG_NAME — the descriptive long name of the level. This is the column most commonly surfaced to users, report developers, and administrators who need a human-readable label for a dimensional level.
• STAR_LEVEL_NAME_COL_NAME — a derived expression, LEVEL_PREFIX||'_NAME'. It produces the physical column name used in the star schema for the level's name attribute, enabling consistent referencing of warehouse columns.

The presence of both LEVEL_NAME and LEVEL_LONG_NAME reflects the metadata layer's separation between technical identifiers and descriptive labels.

Common Use Cases and Queries

Typical use cases involve metadata discovery, validation, and documentation of the warehouse dimensional model. Report developers and ETL specialists query this view to enumerate available levels, to map logical level names to star schema column names, or to confirm the dimension to which a level belongs.

To retrieve the long names and derived star column names for all levels:

• SELECT dim_id, level_id, level_name, level_long_name, star_level_name_col_name FROM edw_sec_lvl_info_v ORDER BY dim_id, level_id;

To search for a specific level by its long name (matching the user's search term):

• SELECT level_id, level_name, level_long_name FROM edw_sec_lvl_info_v WHERE UPPER(level_long_name) LIKE '%LEVEL_LONG_NAME%';

To identify the star schema column associated with a given level:

• SELECT level_long_name, star_level_name_col_name FROM edw_sec_lvl_info_v WHERE level_id = :p_level_id;

As noted, if the object is reported as "Not implemented in this database," these queries will fail unless the BIS/DBI metadata components have been deployed. In such environments, the equivalent level metadata must be sourced directly from the underlying EDW_LEVELS_MD_V and EDW_ALL_COLUMNS_MD_V views.