Search Results attribute_source_level

Overview

The view EDW_DIM_ATTRIBUTES_MD_V is a metadata discovery view owned by the APPS schema and shipped with the Oracle E-Business Suite, falling under the BIS (Business Intelligence System) product family. It exposes the relationship between warehouse dimensions, the relational database items (attributes) that describe them, and the unique keys that govern how those attributes participate in item sets. In practice, this view is a metadata interrogation object: it does not store transactional or master data itself, but rather presents the structural definition of the analytical data model so that reporting, extraction, and integration tooling can discover which attributes belong to which dimension and at which source level they are populated.

Because it is a BIS-owned view, it is typically consumed by Oracle Business Intelligence and ETL layers, or by custom reporting that must programmatically resolve dimension-attribute mappings without hard-coding element identifiers. It is read-only, as indicated by the WITH READ ONLY clause in its definition, and therefore is safe for query-only access in reporting contexts.

Underlying Base Objects

Although the ETRM metadata documents no base objects separately, the view definition reveals that it is constructed as a UNION of two branches over a set of internal BIS/CMP metadata views. The referenced objects are:

• CMPWBDIMENSION_V — supplies the warehouse dimension element (ELEMENTID, NAME).
• CMPRELATIONALDBITEM_V — supplies the relational database item, i.e. the attribute (ELEMENTID, NAME, LONGNAME, and the owning relation).
• CMPLEVEL_V — supplies the dimension level and its prefix, correlating a level to a dimension.
• CMPWBITEMSETUSAGE_V — links an attribute to an item set (unique key).
• CMPUNIQUEKEY_V — resolves the item set to a unique key element (ELEMENTID, NAME).

The first branch returns attributes that are not used by any unique key (via a NOT EXISTS subquery). The second branch returns attributes that are associated with a unique key, populating the UK_ID and UK_NAME columns. The two branches are reconciled with TO_NUMBER(NULL) and NULL placeholders in the first branch so that the column lists align. The join on DIMLVL.PREFIX = SUBSTR(ATTR.NAME, 1, INSTR(ATTR.NAME,'_',1,1)-1) derives the level prefix from the attribute name up to the first underscore.

Key Columns

• DIM_ID / DIM_NAME — the dimension element identifier and name.
• ATTRIBUTE_ID / ATTRIBUTE_NAME / ATTRIBUTE_LONGNAME — the relational database item identity and descriptive labels.
• ATTRIBUTE_SOURCE_LEVEL — the level name from which the attribute is sourced.
• ATTRIBUTE_SOURCE_LEVEL_PREFIX — the derived prefix (portion of the attribute name before the first underscore), matched against the level prefix.
• UK_ID / UK_NAME — the unique key identifier and name; these are NULL where the attribute is not part of an item set.

Common Use Cases and Queries

Typical usage includes auditing which attributes map to a given dimension, identifying attributes that are key-bearing versus free, and validating level prefix conventions.

List all attributes for a dimension:
SELECT DIM_NAME, ATTRIBUTE_NAME, ATTRIBUTE_SOURCE_LEVEL FROM APPS.EDW_DIM_ATTRIBUTES_MD_V WHERE DIM_NAME = :dim;

Find attributes that participate in a unique key:
SELECT DIM_NAME, ATTRIBUTE_NAME, UK_NAME FROM APPS.EDW_DIM_ATTRIBUTES_MD_V WHERE UK_ID IS NOT NULL;

Identify non-key attributes:
SELECT DIM_NAME, ATTRIBUTE_NAME FROM APPS.EDW_DIM_ATTRIBUTES_MD_V WHERE UK_ID IS NULL ORDER BY DIM_NAME;