Search Results edw_hierarchy_level_md_v

Overview

The view APPS.EDW_HIERARCHY_LEVEL_MD_V is a reporting object owned by the APPS schema and associated with the BIS (Business Intelligence System) product family in Oracle E-Business Suite. It is documented in the ETRM repository with the status VALID and is intended to expose hierarchy level metadata for the Enterprise Data Warehouse (EDW) and associated analytical reporting layers. The "MD" suffix conventionally denotes metadata, indicating that the view surfaces descriptive metadata about dimension, hierarchy, and level structures rather than transactional data. In EBS 12.1.1 and 12.2.2, such views typically support the Oracle Business Intelligence (OBIEE) or Daily Business Intelligence (DBI) products that consume EBS hierarchy definitions to build dimensional models.

Underlying Base Objects

The view is defined over four underlying views (not base tables) belonging to the CMP (Common Dimension/Hierarchy) family, joined through the ELEMENTID key that links all CMP hierarchy metadata objects:

• CMPWBDIMENSION_V DIM — the warehouse dimension definition; provides DIM.ELEMENTID and DIM.NAME.
• CMPITEMHIERARCHY_V HIER — the item hierarchy definition; joined to the dimension via DIM.ELEMENTID = HIER.OWNDIMENSION.
• CMPLEVELRELATIONSHIP_V LVLREL — the relationship between parent and child levels in a hierarchy; joined via HIER.ELEMENTID = LVLREL.HIERARCHY.
• CMPLEVEL_V CLVL — the level definition; joined via LVLREL.CHILDLEVEL = CLVL.ELEMENTID.

The view is created WITH READ ONLY, meaning no DML is permitted through it and it is strictly intended for query consumption. Because the underlying objects are themselves views in the CMP schema, the metadata chain is fully resolved before reaching physical tables.

Key Columns

The view exposes eight columns that together describe a level's position within a dimension hierarchy:

• DIM_ID / DIM_NAME — identifier and display name of the owning dimension.
• HIER_ID / HIER_NAME — identifier and display name of the hierarchy within that dimension.
• LVL_ID / LVL_NAME — identifier and display name of the level.
• LVL_PREFIX — a prefix label used in generating level-qualified names, typically for concatenation in reporting or ETL transformations.
• PARENT_LVL_ID — the identifier of the parent level, establishing the hierarchical lineage.

Together these columns allow a consumer to reconstruct the full dimension-to-level path, which is the standard input for building OLAP cubes or analytic subject areas.

Common Use Cases and Queries

Typical uses include dimensional modeling for DBI/OBIEE, populating hierarchy metadata loaders, and auditing the structure of CMP-defined hierarchies. A representative query lists all levels for a named hierarchy:

SELECT dim_name, hier_name, lvl_name, lvl_prefix, parent_lvl_id
  FROM apps.edw_hierarchy_level_md_v
 WHERE hier_name = '&hierarchy_name'
 ORDER BY parent_lvl_id, lvl_id;

To enumerate all dimensions and their hierarchies:

SELECT DISTINCT dim_id, dim_name, hier_id, hier_name
  FROM apps.edw_hierarchy_level_md_v
 ORDER BY dim_name, hier_name;

Because the view is read-only and joins four CMP views, performance depends on the underlying CMP objects and appropriate indexing on ELEMENTID. Consumers should restrict queries with a dimension or hierarchy predicate to avoid full scans across the metadata set.