Search Results edw_mtl_uom_m




Overview
Explore in AI Chat

Overview

EDW_MTL_UOM_M is a dimension table owned by the OPI (Operations Intelligence) schema in Oracle EBS 12.1.1 and 12.2.2. It represents the Unit Of Measure dimension within the Operations Intelligence data warehouse, providing a conformed, denormalized view of unit-of-measure definitions used across inventory, purchasing, order management, and manufacturing subject areas. The table is a materialized dimension built from Operational Data Store (ODS) and base EBS tables, enabling cross-functional reporting on quantities, conversions, and transactional measures expressed in consistent UOM terms.

The heuristic Data Vault classification for this object is standalone, meaning the mined foreign-key structure exposes no upstream parent references. In Data Vault modeling terms, this suggests the table behaves as an independent dimension (or a standalone hub) rather than a link or satellite. Analysts designing lineage or data vault layers should treat EDW_MTL_UOM_M as a self-contained reference set whose business keys (UOM codes and names) are sourced directly from the operational UOM definitions.

Key Information Stored

The table carries 19 documented physical columns. The most significant are:

  • UOM_EDW_UOM_PK_KEY — the surrogate primary key of the dimension, forming the EDW_MTL_UOM_M_PK constraint. This is the value that fact tables and other dimensions carry as a foreign reference.
  • UOM_EDW_UOM_PK — the original operational unit-of-measure primary key from the source system; a business-key candidate for lineage and reconciliation.
  • UOM_NAME and UOM_UNIT_MEASURE_NAME — the human-readable identifier and the base unit name (for example, Each, Dozen, Kilogram).
  • UOM_DESCRIPTION — the long description of the unit of measure.
  • UOM_EDW_BASE_UOM — the base unit of measure to which this unit converts; essential for normalization and quantity comparison.
  • UOM_CONVERSION_RATE — the numeric factor applied to convert this UOM into its base UOM.
  • UOM_GLOBAL_FLAG — indicates whether the unit of measure is globally defined versus organization-specific.
  • UOM_EDW_UOM_DP — the source data provider or source-system identifier, supporting lineage tracking.
  • ALL_ALL_PK_KEY and ALL_ALL_PK — the surrogate and operational keys of the associated inventory organization context.
  • ALL_NAME — the organization name tied to the UOM record.
  • UOM_USER_ATTRIBUTE1 through UOM_USER_ATTRIBUTE5 — the five descriptive flexfield columns used for client-specific extensions.
  • CREATION_DATE and LAST_UPDATE_DATE — standard audit columns supporting incremental extraction and change tracking.

The surrogate key is UOM_EDW_UOM_PK_KEY; business-key candidates include UOM_EDW_UOM_PK and the combination of UOM_NAME with the organization context (ALL_ALL_PK_KEY).

Common Use Cases and Queries

Typical reporting joins this dimension to fact tables using the surrogate key. A representative query joining a transaction fact to the UOM dimension looks like:

  • SELECT f.transaction_id, f.quantity, u.UOM_NAME, u.UOM_CONVERSION_RATE FROM <fact_table> f JOIN OPI.EDW_MTL_UOM_M u ON f.UOM_EDW_UOM_PK_KEY = u.UOM_EDW_UOM_PK_KEY;
  • Conversion normalization: SELECT quantity * u.UOM_CONVERSION_RATE FROM <fact> f JOIN OPI.EDW_MTL_UOM_M u ON f.UOM_EDW_UOM_PK_KEY = u.UOM_EDW_UOM_PK_KEY;
  • Lineage reconciliation: SELECT UOM_EDW_UOM_PK, UOM_EDW_UOM_PK_KEY, UOM_EDW_UOM_DP FROM OPI.EDW_MTL_UOM_M;
  • Organization-scoped listings: SELECT ALL_NAME, UOM_NAME, UOM_GLOBAL_FLAG FROM OPI.EDW_MTL_UOM_M ORDER BY ALL_NAME, UOM_NAME;

Common scenarios include inventory quantity analysis, procurement spend measured in comparable units, and cross-organization reporting where the global flag distinguishes shared versus local UOM definitions.

Related Objects

Because EDW_MTL_UOM_M is classified as standalone, it does not carry documented foreign keys to parent objects. It is referenced by downstream OPI fact tables and conformed dimensions that resolve quantities through its surrogate key. The most significant related objects include:

  • OPI fact tables joining on UOM_EDW_UOM_PK_KEY for quantity measures.
  • The ODS source table supplying records via UOM_EDW_UOM_PK and UOM_EDW_UOM_DP.
  • Organization dimension objects joining on ALL_ALL_PK_KEY.
  • Inventory and purchasing subject-area marts that reference UOM_NAME for labeling.
  • ETL lineage views that consume CREATION_DATE and LAST_UPDATE_DATE for incremental loads.