Search Results cmpitemhierarchy_v




Overview
Explore in AI Chat

Overview

EDW_HIERARCHIES_MD_V is a read-only database view shipped within the Oracle Enterprise Business Suite (EBS) environment under the BIS (Business Intelligence System) product family. In the context of Oracle EBS 12.1.1 and 12.2.2, this view exposes metadata describing the relationship between dimensions and item hierarchies used by the Enterprise Data Warehouse (EDW) and related analytical/BI reporting layers. Its role is to flatten the association between a given dimension (as published by the CMPWBDIMENSION_V view) and the corresponding item hierarchy (as published by CMPITEMHIERARCHY_V), presenting both the surrogate identifiers and the descriptive names in a single result set.

The name suffix "_MD_V" denotes a metadata view, indicating that the object is intended to describe structural definitions rather than transactional data. It is therefore most commonly consumed by ETL routines, BI repository configuration, and reporting tools that need to resolve how item hierarchies map to their owning dimensions before extracting or joining fact data. Note that in the documented ETRM metadata the view is recorded as "Not implemented in this database," meaning the object definition is catalogued but the view may not be physically present in every environment, depending on installed products and patches.

Underlying Base Objects

EDW_HIERARCHIES_MD_V is defined with a WITH READ ONLY clause and is constructed from an inner join of two other views rather than base tables. The documented view text is:

  • CMPWBDIMENSION_V DIM — the dimension metadata view, aliased as DIM.
  • CMPITEMHIERARCHY_V HIER — the item hierarchy metadata view, aliased as HIER.

The join predicate is HIER.OWNDIMENSION = DIM.ELEMENTID, which links each item hierarchy to the dimension that owns it. Because both sources are themselves views, the ETRM metadata documents no referenced base tables directly; the underlying physical tables sit beneath the CMPWBDIMENSION_V and CMPITEMHIERARCHY_V definitions. This layered structure is consistent with the OWB/Enterprise Data Warehouse metadata model used by BIS components.

Key Columns

The view projects six columns, drawn from the two joined sources:

  • DIM_ID — the element identifier of the owning dimension (DIM.ELEMENTID).
  • DIM_NAME — the display name of that dimension (DIM.NAME).
  • HIER_ID — the element identifier of the item hierarchy (HIER.ELEMENTID).
  • HIER_NAME — the name of the item hierarchy (HIER.NAME).
  • HIER_PREFIX — the prefix assigned to the hierarchy (HIER.PREFIX), often used when generating derived level or member naming conventions.
  • HIER_LONG_NAME — the long descriptive name of the hierarchy (HIER.LONGNAME), useful for reporting labels.

Common Use Cases and Queries

Typical usage includes resolving which dimensions own which hierarchies prior to an EDW load, validating hierarchy-to-dimension mappings during implementation, and driving pick lists in BI authoring tools. A representative query lists all hierarchies and their dimensions:

SELECT dim_id, dim_name, hier_id, hier_name, hier_prefix, hier_long_name FROM edw_hierarchies_md_v ORDER BY dim_name, hier_name;

To locate a specific hierarchy such as the item hierarchy referenced by the search term "cmpitemhierarchy_v":

SELECT dim_name, hier_name, hier_long_name FROM edw_hierarchies_md_v WHERE UPPER(hier_name) LIKE '%ITEM%';

Because the view is read-only and metadata-oriented, it should be treated as a reference source for extraction logic and integration mapping rather than as a target for DML operations. Administrators should first confirm whether the view exists in the target instance, since ETRM records indicate it may not be implemented in all databases.