In this lesson, you will learn how to structure geographical hierarchies within your dimension tables to enable seamless regional performance analysis.
Understanding Geographical Hierarchies
When analyzing business performance, "geography" is rarely a flat list of names. It is almost always a hierarchy: City → State/Province → Country → Region. In dimensional modeling, if you store all these levels in a single flat dimension table, you risk data redundancy and update anomalies. However, for analytical performance, this "denormalized" approach is often preferred because it avoids expensive multi-table joins when filtering by a specific territory.
Designing the Geography Dimension
To facilitate regional analysis, your dim_geography table should include surrogate keys for every level of the hierarchy. This allows you to roll up metrics (like total sales) from the city level all the way to the global level without complex recursive logic at query time.
Consider this structure:
CREATE TABLE dim_geography (
geo_key INT PRIMARY KEY,
city_name VARCHAR(100),
state_province VARCHAR(100),
country_name VARCHAR(100),
region_name VARCHAR(100),
latitude DECIMAL(9,6),
longitude DECIMAL(9,6)
);
Enabling Regional Performance Analysis
By keeping the hierarchy in one table, your fact table only needs to link to the lowest common denominator (the geo_key). When you need to compare the performance of different regions, your analytical queries remain performant because the database engine scans a single dimension table rather than joining across a tree structure.
For example, to calculate sales by region:
SELECT
g.region_name,
SUM(f.sales_amount) as total_sales
FROM fact_sales f
JOIN dim_geography g ON f.geo_key = g.geo_key
GROUP BY g.region_name;
By embedding the hierarchy directly into the dimension, you trade off a small amount of storage space for massive gains in query simplicity and execution speed. This design allows business users to "slice and dice" data across any geographical level with consistent and predictable performance, ensuring that your dashboarding tools can aggregate regional metrics in a single pass.
Key Takeaway: Embedding geographical hierarchies into a denormalized dimension table transforms complex multi-level data into a simple, high-performance structure for regional aggregation.