Search Results edw_geography_m_pk

Overview

EDW_GEOGRAPHY_M is a denormalized geography dimension table documented under the POA (Purchasing Intelligence) product module in Oracle EBS 12.1.1 and 12.2.2. Its documented description is "Geography dimension table," and its role is to serve analytic and reporting queries in the Oracle E-Business Suite data warehouse by flattening the hierarchical address and location structures used by operational modules such as Purchasing, Order Management, and Receivables.

The ETRM metadata explicitly notes that this object is "Not implemented in this database" for the documented environment, and the product module itself is flagged as obsolete. The table is therefore best treated as a reference model rather than as a live, actively maintained physical object in a current 12.2.2 deployment.

Applying the heuristic Data Vault classification mined from the foreign key structure, EDW_GEOGRAPHY_M is classified as standalone. In Data Vault modeling terms, this suggests the object functions as a self-contained dimension rather than as a hub, link, or satellite participating in an integrated raw vault. Any Data Vault implementation derived from this table would more appropriately treat it as a reference or dimension construct outside the core hub-and-link topology.

Key Information Stored

The table is documented with 109 physical columns, organized around a nested geographic hierarchy. The most significant columns are the location-level attributes and the hierarchy keys that connect each level:

• GLOC_LOCATION_PK_KEY — the single-column surrogate primary key of EDW_GEOGRAPHY_M_PK, and the column the user searched for. It also constitutes the unique index EDW_GEOGRAPHY_M_U2.
• GLOC_LOCATION_PK — the source location identifier; combined with GLOC_LOCATION_PK_KEY it forms the composite unique index EDW_GEOGRAPHY_M_U1, making it a business-key candidate rather than the declared primary key.
• GLOC_NAME, GLOC_ADDRESS_LINE_1 through GLOC_ADDRESS_LINE_4, GLOC_LOCATION_DP — the descriptive location name, street address components, and the level designator for the location node.
• CITY_CITY_PK_KEY, CITY_NAME, CITY_CITY_DP — city-level key, name, and hierarchy designator.
• STAT_STATE_PK_KEY, STAT_NAME, STAT_STATE_DP and SREG_STATE_REGION_PK_KEY — state and state-region nodes of the hierarchy.
• COUN_COUNTRY_PK_KEY, COUN_NAME, COUN_COUNTRY_DP — country-level identifiers and descriptions.
• POST_POSTCODE_PK_KEY, POST_NAME, POST_POSTCODE_DP and PCCY_POSTCODE_CITY_PK_KEY — postal code and postcode-city levels.
• AREA1 and AREA2 columns — two additional area nodes (ARE1_AREA1_PK_KEY, ARE2_AREA2_PK_KEY) providing finer geographic segmentation.
• CREATION_DATE, LAST_UPDATE_DATE — standard audit columns.

Each hierarchy level also carries _INSTANCE and USER_ATTRIBUTE1 through USER_ATTRIBUTE5 columns, providing source-system instance context and five extensibility slots per node. The surrogate key GLOC_LOCATION_PK_KEY should be used for joins and referential integrity, while GLOC_LOCATION_PK plus GLOC_LOCATION_PK_KEY is the documented composite business key.

Common Use Cases and Queries

The primary use cases for this dimension are geographic aggregation of purchasing and spend data and hierarchical rollups from location to city, state, region, and country. A typical pattern joins the surrogate key back to the location node:

• SELECT GLOC_LOCATION_PK_KEY, GLOC_NAME, CITY_NAME, STAT_NAME, COUN_NAME FROM EDW_GEOGRAPHY_M WHERE COUN_COUNTRY_PK_KEY = :country;
• Spend or sourcing analysis grouped by geography: SELECT COUN_NAME, SUM(fact.amount) FROM fact_table fact JOIN EDW_GEOGRAPHY_M geo ON fact.GLOC_LOCATION_PK_KEY = geo.GLOC_LOCATION_PK_KEY GROUP BY COUN_NAME;
• Hierarchy validation queries using the _DP designator columns to confirm the level at which each row is stored.
• Postal-code or city-level filters for regional supplier and requisition reporting.

Because the object is marked as not implemented and the POA module is obsolete, these patterns should be validated against the actual deployed schema before use.

Related Objects

The metadata provides no external foreign key relationships from EDW_GEOGRAPHY_M, consistent with its standalone Data Vault classification. Related objects must therefore be inferred from standard EBS geography structures and typical Purchasing Intelligence star-schema designs:

• EDW_GEOGRAPHY_M_PK — the documented primary key constraint on GLOC_LOCATION_PK_KEY.
• EDW_GEOGRAPHY_M_U1 and EDW_GEOGRAPHY_M_U2 — the unique indexes enforcing the composite and single-column business keys.
• HR_LOCATIONS_ALL and HR_LOCATIONS_ALL_TL — the typical operational source of GLOC_LOCATION_PK and address attributes.
• HZ_LOCATIONS and HZ_GEOGRAPHIES — Trading Community Architecture geography structures underlying city, state, and country data.
• FND_TERRITORIES — reference source for COUN_COUNTRY_PK and country naming.
• POA fact tables such as purchasing spend and supplier performance facts — the analytic consumers joining on GLOC_LOCATION_PK_KEY.

All related-object references should be confirmed against the deployed 12.1.1 or 12.2.2 schema, given the table's documented non-implemented status.