Results for “edw_geography_mpv”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

EDW_GEOGRAPHY_MPV is a view owned by the APPS schema in Oracle E-Business Suite, catalogued under the PO (Purchasing) product family and described in the ETRM documentation as a "Geography Mapping View." The "_MPV" suffix conventionally denotes a materialized-view-style or mapping presentation view used within the Oracle Enterprise Data Warehouse (EDW) and Daily Business Intelligence (DBI) reporting layer. Rather than storing transactional purchasing data directly, this view exposes a denormalized geography dimension used by downstream analytics, ETL routines, and integration extracts that must resolve a location or geography key to a surrogate dimension identifier.

Its role is to present a curated geography dimension record, including the dimension primary key and an associated date key, so that fact tables in the EDW can join to a consistent geography dimension. In the context of the user's search term "dim_pk_date," this view is significant because it publishes a column named DIM_PK_DATE, which serves as the date-based dimension key (the date dimension foreign key) accompanying the geography DIM_PK. This pairing supports time-aware geography reporting such as period-over-period purchasing analysis by ship-to or bill-to location.

Underlying Base Objects

The documented view text is:

SELECT LOCATION_PK, LAST_UPDATE_DATE FROM EDW_GEOG_LOCATION_LCV

The ETRM metadata records that no base tables or objects are explicitly documented as referenced. However, the view text itself shows that EDW_GEOGRAPHY_MPV is defined over EDW_GEOG_LOCATION_LCV, an EDW location list-of-values/"LCV" object. That underlying object supplies the LOCATION_PK and LAST_UPDATE_DATE attributes that the mapping view projects. Because the definition is a simple projection with no joins, filters, or aggregation, EDW_GEOGRAPHY_MPV is effectively a thin mapping layer whose purpose is to expose the geography dimension keys in a stable, standardized form for EDW consumption while shielding downstream objects from changes in the source LCV object.

Key Columns

• DIM_PK — The surrogate primary key of the geography dimension. In EDW/DBI patterns, DIM_PK is the system-generated identifier used to join fact tables to the geography dimension; it abstracts the source LOCATION_PK into a warehouse-stable key.
• DIM_PK_DATE — The date dimension primary key associated with the geography dimension record. This is the column matched by the search term "dim_pk_date." It typically carries a date or date-key value enabling time-aware joins between the geography dimension and the date dimension, supporting effective-dating and period reporting.
• LOCATION_PK — The source location primary key inherited from EDW_GEOG_LOCATION_LCV, identifying the underlying geography/location record in the operational or staging layer.
• LAST_UPDATE_DATE — The audit timestamp indicating the most recent modification of the record, used for incremental ETL and change detection.

Common Use Cases and Queries

EDW_GEOGRAPHY_MPV is typically consumed by geography dimension loads, DBI geography analyses, and integrations that require reconciliation between a geography key and a date key. A representative reconciliation query confirming that DIM_PK_DATE is populated appears below.

SELECT DIM_PK, DIM_PK_DATE, LOCATION_PK, LAST_UPDATE_DATE FROM APPS.EDW_GEOGRAPHY_MPV WHERE DIM_PK_DATE IS NOT NULL;

Incremental ETL jobs frequently filter on the audit column to capture only changed geography mappings:

SELECT DIM_PK, DIM_PK_DATE FROM APPS.EDW_GEOGRAPHY_MPV WHERE LAST_UPDATE_DATE >= :last_run_date;

A third scenario joins the view to a fact table using both the geography and date keys to produce time-sliced geography reporting:

SELECT g.DIM_PK, g.DIM_PK_DATE, f.amount FROM APPS.EDW_GEOGRAPHY_MPV g, fact_table f WHERE f.dim_pk_geo = g.DIM_PK AND f.dim_pk_date = g.DIM_PK_DATE;

Because the view is a simple pass-through projection, query performance depends almost entirely on the underlying EDW_GEOG_LOCATION_LCV object and its indexes. Analysts should treat EDW_GEOGRAPHY_MPV as the authoritative, warehouse-facing mapping of geography dimension keys, including the date dimension key "dim_pk_date," and should avoid joining directly to the LCV object to preserve abstraction and stability across EBS 12.1.1 and 12.2.2 environments.