Search Results parent_lvl_name




Overview
Explore in AI Chat

Overview

APPS.EDW_LEVEL_RELATIONS_MD_V is a reporting and integration view in the Oracle E-Business Suite (EBS) Enterprise Data Warehouse (EDW) layer. It is supplied as part of the ETRM metadata set documented for EBS 12.1.1 and 12.2.2. The view exposes metadata that describes the hierarchical relationships between levels within dimension hierarchies — that is, it defines which level acts as a parent and which acts as a child in a given hierarchy, together with the warehouse table identifiers and key columns that physically implement that relationship. Because it is a view rather than a table, it is read-only and is intended to be consumed by Oracle Business Intelligence (OBIEE), Discoverer, or other analytics and ETL tools that require a normalized description of dimensional level relationships.

The object appears in the data dictionary under the APPS schema and is typically published for query access by reporting and integration users. It carries the "MD_V" suffix, indicating a metadata view, which signals that its rows describe structural metadata rather than transactional or fact data. This makes it a key reference point for any process that must understand how EDW hierarchies are assembled before generating queries against the underlying dimensional tables.

Underlying Base Objects

The ETRM metadata for this view records the full SELECT text but lists no documented base objects. Examination of the view text shows that the view is defined as a simple projection:

Consequently, the sole underlying base object is the base table EDW_LEVEL_RELATIONS_MD. The view adds no joins, filters, or transformations — its purpose is to expose the metadata under the APPS schema with a stable, name-consistent interface. Because there are no joins, the view inherits the base table's dimensional metadata content directly.

Key Columns

The view returns nineteen columns, several of which are directly relevant to the user search term parent_lvl_prefix. The principal columns are:

  • LEVEL_RELATION_ID — Surrogate identifier for the level-to-level relationship record.
  • DIM_ID / DIM_NAME — Identifier and name of the dimension that owns the hierarchy.
  • HIER_ID / HIER_NAME — Identifier and name of the hierarchy in which the parent/child relationship exists.
  • PARENT_LVL_ID / PARENT_LVL_NAME / PARENT_LVL_PREFIX — The parent level's identifier, display name, and the prefix applied to it (for example in generated column names).
  • PARENT_LVLTBL_ID / PARENT_LVLTBL_NAME — Identifier and name of the physical table implementing the parent level.
  • UK_ID / UK_NAME — The unique key identifier and name used to join the parent to the child.
  • CHILD_LVL_ID / CHILD_LVL_NAME / CHILD_LVL_PREFIX — The child level's identifier, name, and prefix.
  • CHIL_LVLTBL_ID / CHIL_LVLTBL_NAME — Identifier and name of the physical table implementing the child level (note the documented spelling "CHIL_").
  • FK_ID / FK_NAME — The foreign key identifier and name that links child rows back to the parent.

The PARENT_LVL_PREFIX column is the object most commonly sought when this view is referenced: it exposes the string prefix used to qualify column names for the parent level, enabling downstream generators to construct unambiguous column references where parent and child levels share similar attribute names.

Common Use Cases and Queries

Typical usage centers on dimensional modeling, ETL design, and OBIEE/BI repository construction. Analysts query this view to enumerate the parent-child relationships of a hierarchy before building level-based or ragged-hierarchy reports.

To find the prefix for every parent level in a hierarchy:

  • SELECT HIER_NAME, PARENT_LVL_NAME, PARENT_LVL_PREFIX, CHILD_LVL_NAME, CHILD_LVL_PREFIX FROM APPS.EDW_LEVEL_RELATIONS_MD_V WHERE HIER_NAME = :p_hier;

To trace all relationships within a dimension:

  • SELECT DIM_NAME, HIER_NAME, PARENT_LVL_NAME, CHILD_LVL_NAME, UK_NAME, FK_NAME FROM APPS.EDW_LEVEL_RELATIONS_MD_V WHERE DIM_NAME = :p_dim ORDER BY HIER_NAME, PARENT_LVL_ID;

To resolve the physical tables involved in a linkage:

  • SELECT LEVEL_RELATION_ID, PARENT_LVLTBL_NAME, CHIL_LVLTBL_NAME, FK_NAME FROM APPS.EDW_LEVEL_RELATIONS_MD_V WHERE FK_NAME IS NOT NULL;

Where the user's search term parent_lvl_prefix is concerned, the direct query is:

  • SELECT DISTINCT PARENT_LVL_PREFIX FROM APPS.EDW_LEVEL_RELATIONS_MD_V;

These queries allow BI developers and ETL designers to programmatically discover hierarchy structure and generate consistent, prefix-qualified column references without hard-coding level names.