Search Results cmpwbcubeattribute_v

Overview

EDW_FACT_ATTRIBUTES_MD_V is a metadata view in the Oracle EBS Applications BIS (Business Intelligence System) product family. It exposes the structural definition of fact objects and their associated attributes as they are modeled in the Oracle Business Intelligence (OBI) / Express multidimensional environment. The view consolidates attribute metadata for each fact across three distinct attribute categories—measures, unique keys, and foreign keys—into a single, uniformly structured result set.

The view is catalogued in the ETRM reference for Oracle EBS 12.1.1 and 12.2.2. It is documented as an Enterprise Data Warehouse (EDW) metadata view, evidenced by the "_MD_V" suffix and the "EDW_" prefix, indicating that its purpose is to support data warehouse metadata reporting rather than transactional processing. As noted in the implementation metadata, the object is not implemented as a stored database object; it is delivered as a view definition. This view is typically used by ETL architects, BI administrators, and metadata analysts who need to introspect the multidimensional model programmatically instead of navigating the interactive Express/OBI administration tools.

Underlying Base Objects

The view is defined over the following documented base objects, each drawn from the CMP (Express/OBI metadata) schema family:

• CMPWBCUBE_V — supplies the fact objects (the cube-level elements).
• CMPWBCUBEATTRIBUTE_V — supplies measure-type attributes owned by each fact.
• CMPUNIQUEKEY_V — supplies unique key definitions associated with a fact or item set.
• CMPWBITEMSETUSAGE_V — links attribute columns to item sets and defines whether a column participates as a unique key or foreign key column.
• CMPFOREIGNKEY_V — supplies foreign key definitions.
• CMPRELATIONALDBITEM_V — supplies the underlying relational database columns for unique key and foreign key attributes.
• CMPDATATYPE_V — resolves the domain of a measure to establish its attribute type.

These objects are joined across three UNION branches. The first branch handles measures, the second handles unique keys, and the third handles foreign keys, producing a single ATTRibute_TYPE-tagged result set. Outer joins (indicated by the (+) syntax) preserve facts that lack a unique key association.

Key Columns

• FACT_ID / FACT_NAME — Identifier and descriptive name of the fact (cube) object.
• ATTRIBUTE_TYPE — Classifies each row: 'MEASURE' when the attribute's domain resolves to NUMBER, otherwise NULL. The UNION branches for unique keys and foreign keys yield NULL ATTRIBUTE_TYPE and instead populate KEY_TYPE.
• ATTRIBUTE_ID / ATTRIBUTE_NAME / ATTRIBUTE_LONGNAME — Identifier, short name, and long name of the specific attribute (measure, key column, or relational column).
• KEY_TYPE — Populated as 'UK' for unique key rows and 'FK' for foreign key rows.
• KEY_ID / KEY_NAME — Identifier and name of the unique key or foreign key constraint to which the attribute belongs.

The documented column list includes FACT_ID, FACT_NAME, ATTRIBUTE_TYPE, ATTRIBUTE_ID, ATTRIBUTE_NAME, ATTRIBUTE_LONGNAME, KEY_TYPE, KEY_ID, and KEY_NAME.

Common Use Cases and Queries

This view is primarily used to enumerate measures and keys attached to facts, to audit multidimensional model consistency, and to drive metadata lineage reporting. A typical query filters by attribute classification:

SELECT FACT_NAME,
       ATTRIBUTE_NAME,
       ATTRIBUTE_TYPE
  FROM EDW_FACT_ATTRIBUTES_MD_V
 WHERE ATTRIBUTE_TYPE = 'MEASURE'
 ORDER BY FACT_NAME, ATTRIBUTE_NAME;

To inventory keys per fact:

SELECT FACT_NAME,
       KEY_TYPE,
       KEY_NAME,
       ATTRIBUTE_NAME
  FROM EDW_FACT_ATTRIBUTES_MD_V
 WHERE KEY_TYPE IN ('UK','FK');

Because the underlying metadata resides in the CMP schema and the view is read-only (WITH READ ONLY), queries are safe for metadata discovery and can be embedded in custom ETL validation scripts or EDW documentation generators.