Search Results cmpuniquekey_v




Overview
Explore in AI Chat

Overview

EDW_LEVEL_ATTS_MD_V is a read-only Oracle EBS database view shipped within the BIS (Business Intelligence System) product family, the same component that supplies the Enterprise Data Warehouse (EDW) collection layer. The view exposes level-level attribute metadata drawn from the Oracle Demantra / Advanced Planning Command Center (CMP) data model, which is surfaced in EBS 12.1.1 and 12.2.2 through the Applications BIS schema. Its purpose is to present, for each combination of dimension, level, and relational database item (column), the associated unique-key metadata where one exists — that is, to declare which level attributes participate in a unique key constraint of the underlying relational table.

The object is documented as "Not implemented in this database" in the ETRM excerpt, meaning the view is defined in the product's installation metadata but is created only when the relevant BIS/EDW schema components are provisioned. The view definition carries the WITH READ ONLY clause, confirming it is intended strictly for query access rather than DML.

Underlying Base Objects

Although the ETRM metadata records no referenced base objects, the view text identifies its source objects, all of which are CMP-family views:

  • CMPWBDIMENSION_V — supplies the dimension (DIM) elements and names.
  • CMPLEVEL_V — supplies the level (LVL) elements, each linked to its owning dimension.
  • CMPRELATIONALDBITEM_V — supplies the user column (UCOL) attributes owned by each level's relation.
  • CMPUNIQUEKEY_V — supplies unique key (UK) definitions owned by the same relation.
  • CMPWBITEMSETUSAGE_V — maps each unique key to its member attribute columns.

The view is a UNION of two branches. The first branch returns level attributes that are not part of any unique key, using a NOT EXISTS correlated subquery against CMPUNIQUEKEY_V and CMPWBITEMSETUSAGE_V; it emits NULLs in the UK_ID and UK_NAME positions. The second branch returns level attributes that are members of a unique key, joining the same objects on LVL.ELEMENTID = UK.OWNINGRELATION and UKEYCOL.ATTRIBUTE = UCOL.ELEMENTID to populate the unique-key identifiers.

Key Columns

  • DIM_ID / DIM_NAME — identifier and name of the owning dimension.
  • LVL_ID / LVL_NAME — identifier and name of the level within that dimension.
  • LVL_COL_ID / LVL_COL_NAME / LVL_COL_LONG_NAME — the relational database item exposed at the level, together with its short and long display names.
  • UK_ID / UK_NAME — identifier and name of the unique key to which the attribute belongs. These are NULL for attributes that do not participate in a unique key.

Common Use Cases and Queries

The view is typically consumed when validating or documenting the unique-key structure of a Demantra/CMP dimension, or when analyzing which level attributes are eligible as record identifiers for EDW extraction. A typical query lists all keyed attributes for a dimension:

  • SELECT dim_name, lvl_name, lvl_col_name, uk_name FROM edw_level_atts_md_v WHERE uk_id IS NOT NULL ORDER BY dim_name, lvl_name;
  • SELECT lvl_name, lvl_col_name FROM edw_level_atts_md_v WHERE uk_id IS NULL; — returns unkeyed attributes.
  • SELECT DISTINCT dim_name, lvl_name, uk_name FROM edw_level_atts_md_v ORDER BY 1,2,3; — summarises unique keys per level.

Because the view is read-only and its name is prefixed EDW_, it should be treated as a metadata reporting object — safe for extraction and BI consumption, but never targeted by DML or interface loading programs.