Solving the Country Dimension Chaos in a Data Warehouse: Smart Design Tips You Can Use

Posted on by Ahmed Abdel Fattah

If you’ve ever worked on a data warehouse with multiple subject areas, you’ve likely run into a seemingly small issue that turns into a huge problem: inconsistent dimension values — especially for something as basic as countries, Gender and others.

Recently, I faced this exact challenge and wanted to share how I tackled it, along with a few architecture design options I evaluated (and one I highly recommend). The challenge is on Data Warehouse deployed on one Oracle 19c Database, but the concept is the same with other database engines also.

🎯 The Challenge: Same Country, Different Codes

Imagine a scenario where you have three subject areas in your DW, each with its own FACT table like this:

  • SA1_FACT: uses country_id from SA1_COUNTRY
  • SA2_FACT: uses country_id from SA2_COUNTRY
  • SA3_FACT: uses country_id from SA3_COUNTRY

Each of these country tables contains similar but not standardized data. For example:

Subject AreaCodeCountry Name
SA110America
SA2100United States of America
SA370US

Same country, three different codes and names. So when business users ask,
💬 “Give me a report showing sales for the UK across all subject areas”,
we can’t easily unify or filter — because the concept of “UK” is fragmented.

🧠 Why We Can’t Just Standardize?

Simple answer: historical data and client restrictions.
Each subject area has its own data lineage and rewriting the country codes globally isn’t feasible without corrupting historical integrity. Also, Business Users are used to the current codes in their daily work.

💡 Exploring the Design Options

Let’s talk about the solutions I considered:

✅ Option 1: Global COUNTRY Table + Mapping Table per Subject Area

  • A central DW_COUNTRY table.
  • A separate mapping table per subject area (e.g., SA1_COUNTRY_MAPPING, SA2_COUNTRY_MAPPING, etc.)
  • Pros: Clean, scalable.
  • Cons: Too many mapping tables, joins get verbose.

✅ Option 2: All Mappings Inside the Global Table

Structure like:

DW_COUNTRY (
  GLOBAL_CODE,
  GLOBAL_NAME,
  SA1_CODE, SA1_NAME,
  SA2_CODE, SA2_NAME,
  ...
)
  • Pros: Only one table
  • Cons: Not normalized, not scalable, breaks if a subject area changes or gets added.

✅ Option 3: Embed Global ID Inside Each Local Country Table

  • Add a GLOBAL_ID column to SA1_COUNTRY, SA2_COUNTRY, etc.
  • Pros: Keeps mappings close to data
  • Cons: Requires modifying subject area schema (which is often off-limits)

✅ Option 4 (🏆 Chosen): Central Global Table + Single Mapping Table for All Areas

This was the winner. Here’s the logic:

  1. A centralized DW_COUNTRY table with unified country definitions
  2. One COUNTRY_CODE_MAPPING table, like this: SUBJECT_AREA SUBJECT_COUNTRY_CODE GLOBAL_COUNTRY_ID SA1 10 1 SA2 100 1 SA3 70 1
  3. A VIEW that joins all fact tables with this mapping — perfect for Power BI, Oracle APEX, and more.

🛠 Bonus: What About Fact Table Keys?

During this design, I also revisited a common modeling question:

Should fact tables have a surrogate primary key?

✅ Here’s the TL;DR:

QuestionRecommendation
Add surrogate PK to fact table?❌ Not unless your tools need it
Unique constraint on all key columns?✅ Yes, if it enforces grain
Foreign keys to dimensions?✅ Logically yes, physically optional

📊 The Final Output: Unified Reporting

Using this model, we created a VW_COUNTRY_FACTS_UNIFIED view that lets our BI tools report consistently on any country, regardless of its local code in each subject area.

Power BI users can now say:
“Show me the trend for the United Kingdom”,
…and the system knows that’s Code 30 in SA1, Code 300 in SA2, and Code 90 in SA3 — all tied to one global identity.

🧭 Conclusion

If your data warehouse spans multiple domains, avoid fighting the urge to force everything into a single schema. Instead, bridge dimensions smartly using a global mapping strategy.

💡 Want a copy of the PL/SQL and view logic we used for this? Check it out below:
— =============================================

— PL/SQL Framework for Option 1 (Bridge Mapping)

— =============================================

— 1. GLOBAL COUNTRY DIMENSION

CREATE TABLE DW_COUNTRY (

    GLOBAL_COUNTRY_ID   NUMBER PRIMARY KEY,

    UNIFIED_NAME        VARCHAR2(100) NOT NULL,

    ISO_CODE            VARCHAR2(10),

    REGION_ID           NUMBER,

    CREATED_DATE        DATE DEFAULT SYSDATE

);

— 2. SUBJECT AREA BRIDGE TABLES

CREATE TABLE SA1_COUNTRY_BRIDGE (

    SA1_COUNTRY_CODE   NUMBER PRIMARY KEY,

    GLOBAL_COUNTRY_ID  NUMBER NOT NULL,

    FOREIGN KEY (GLOBAL_COUNTRY_ID) REFERENCES DW_COUNTRY(GLOBAL_COUNTRY_ID)

);

CREATE TABLE SA2_COUNTRY_BRIDGE (

    SA2_COUNTRY_CODE   NUMBER PRIMARY KEY,

    GLOBAL_COUNTRY_ID  NUMBER NOT NULL,

    FOREIGN KEY (GLOBAL_COUNTRY_ID) REFERENCES DW_COUNTRY(GLOBAL_COUNTRY_ID)

);

CREATE TABLE SA3_COUNTRY_BRIDGE (

    SA3_COUNTRY_CODE   NUMBER PRIMARY KEY,

    GLOBAL_COUNTRY_ID  NUMBER NOT NULL,

    FOREIGN KEY (GLOBAL_COUNTRY_ID) REFERENCES DW_COUNTRY(GLOBAL_COUNTRY_ID)

);

— 3. MAPPING AUDIT TABLE

CREATE TABLE COUNTRY_MAPPING_AUDIT (

    SUBJECT_AREA     VARCHAR2(10),

    ORIGINAL_CODE    NUMBER,

    ORIGINAL_NAME    VARCHAR2(100),

    GLOBAL_COUNTRY_ID NUMBER,

    MAPPED_DATE      DATE DEFAULT SYSDATE,

    MAPPED_BY        VARCHAR2(50)

);

— 4. PROCEDURE TO INSERT NEW COUNTRY INTO DW_COUNTRY

CREATE OR REPLACE PROCEDURE INSERT_COUNTRY(

    p_name IN VARCHAR2,

    p_iso_code IN VARCHAR2,

    p_region_id IN NUMBER,

    p_global_id OUT NUMBER

) AS

BEGIN

    SELECT NVL(MAX(GLOBAL_COUNTRY_ID), 0) + 1 INTO p_global_id FROM DW_COUNTRY;

    INSERT INTO DW_COUNTRY(GLOBAL_COUNTRY_ID, UNIFIED_NAME, ISO_CODE, REGION_ID)

    VALUES (p_global_id, p_name, p_iso_code, p_region_id);

END;

/

— 5. PROCEDURE TO INSERT INTO A BRIDGE TABLE

CREATE OR REPLACE PROCEDURE INSERT_SA1_MAPPING(

    p_sa1_code IN NUMBER,

    p_sa1_name IN VARCHAR2,

    p_global_country_id IN NUMBER,

    p_user IN VARCHAR2

) AS

BEGIN

    INSERT INTO SA1_COUNTRY_BRIDGE (SA1_COUNTRY_CODE, GLOBAL_COUNTRY_ID)

    VALUES (p_sa1_code, p_global_country_id);

    INSERT INTO COUNTRY_MAPPING_AUDIT

    (SUBJECT_AREA, ORIGINAL_CODE, ORIGINAL_NAME, GLOBAL_COUNTRY_ID, MAPPED_BY)

    VALUES (‘SA1’, p_sa1_code, p_sa1_name, p_global_country_id, p_user);

END;

/

— 6. GLOBAL UNION VIEW FOR REPORTING

CREATE OR REPLACE VIEW VW_GLOBAL_FACT_COUNTRY AS

SELECT ‘SA1’ AS SUBJECT_AREA, b.GLOBAL_COUNTRY_ID, f.INDICATOR_ID, f.DATE_DIM_ID, f.VALUE

FROM SA1_FACT f

JOIN SA1_COUNTRY_BRIDGE b ON f.COUNTRY_ID = b.SA1_COUNTRY_CODE

UNION ALL

SELECT ‘SA2’ AS SUBJECT_AREA, b.GLOBAL_COUNTRY_ID, f.INDICATOR_ID, f.DATE_DIM_ID, f.VALUE

FROM SA2_FACT f

JOIN SA2_COUNTRY_BRIDGE b ON f.COUNTRY_ID = b.SA2_COUNTRY_CODE

UNION ALL

SELECT 'SA3' AS SUBJECT_AREA, b.GLOBAL_COUNTRY_ID, f.INDICATOR_ID, f.DATE_DIM_ID, f.VALUE
FROM SA3_FACT f
JOIN SA3_COUNTRY_BRIDGE b ON f.COUNTRY_ID = b.SA3_COUNTRY_CODE;

— Done.

Would you like help designing a similar pattern for other shared dimensions like GENDER or INDICATORS?
Drop a comment or message — happy to help!

Leave a comment