Search Results cmprelation_v




Overview
Explore in AI Chat

Overview

The EDW_LEVEL_TABLE_ATTS_MD_V view is a metadata-oriented database object owned by the APPS schema within Oracle E-Business Suite. It belongs to the BIS (Business Intelligence System) product family and forms part of the enterprise data warehouse layer used by Oracle's analytical and dimensional modeling components. The view resides in Oracle EBS releases 12.1.1 and 12.2.2 and is documented with a status of VALID.

Functionally, the view consolidates metadata describing the relationship between analytical dimensions, their associated level tables, and the individual attribute columns that populate those level tables. It exposes a unified projection of dimension identifiers and names, level-table identifiers and names, level prefixes, and the identity of attribute columns drawn from relational and dimensional data items. This makes the view a convenient access point for tools and reports that must understand how dimension level tables are structured before querying their physical contents.

Underlying Base Objects

Although the documented base object list is empty, the view text in the ETRM metadata reveals its true dependencies. The definition is a three-way UNION query constructed over four CMP-prefixed views that represent the core dimensional metadata of the BIS/EDW layer:

  • CMPWBDIMENSION_V — supplies the dimension element identifier and name (DIM.ELEMENTID, DIM.NAME).
  • CMPLEVEL_V — supplies level information, including LVL.NAME, LVL.PREFIX, and LVL.LONGNAME, joined to the dimension through DIM.ELEMENTID = LVL.DIMENSION.
  • CMPRELATION_V — identifies the level table relation, filtered by LVLTBL.CLASSNAME = 'CMPWBTABLE', and joined via the concatenation rule LVL.NAME || '_LTC' = LVLTBL.NAME.
  • CMPRELATIONALDBITEM_V — supplies the individual column element identifier, name, and long name, joined through LVLTBL.ELEMENTID = COL.OWNINGRELATION.

The second and third branches of the UNION additionally join CMPUNIQUEKEY_V, CMPFOREIGNKEY_V, and CMPWBITEMSETUSAGE_V to classify columns as unique keys or foreign keys. The main SELECT excludes columns that appear in CMPWBITEMSETUSAGE_V, ensuring that key columns are not duplicated in the plain attribute set.

Key Columns

The documented column list includes the following:

  • DIM_ID — the element identifier of the owning dimension.
  • DIM_NAME — the logical name of the dimension.
  • LEVEL_TABLE_ID — the element identifier of the associated level table relation.
  • LEVEL_TABLE_NAME — the physical or logical name of the level table.
  • LEVEL_PREFIX — the prefix used to qualify level table columns, typically derived from LVL.PREFIX.
  • LEVEL_TABLE_ — the leading portion of the column name, exposed as the attribute or column element identifier and related naming columns.

The three UNION branches populate two additional positional columns: a literal marker that is NULL for plain attributes, 'UK' for unique key columns, and 'FK' for foreign key columns; and an associated key element identifier and name. These augment the core attribute metadata with role information.

Common Use Cases and Queries

This view is typically used during EDW metadata analysis, data lineage inspection, and documentation of dimensional models. A representative query lists the attributes available for a given level table:

  • SELECT dim_name, level_table_name, level_prefix, level_table_id FROM apps.edw_level_table_atts_md_v WHERE dim_name = :dimension_name;
  • SELECT DISTINCT dim_name, level_table_name FROM apps.edw_level_table_atts_md_v ORDER BY 1, 2;
  • SELECT level_table_name, level_table_id FROM apps.edw_level_table_atts_md_v WHERE level_prefix = :prefix;

Note that the user's search term cmprelationaldbitem_v corresponds to the CMPRELATIONALDBITEM_V underlying view referenced directly in the definition, confirming that the two objects are closely related components of the same metadata model.