Geographic Hierarchies in a OLAP Cube / Data Warehouse

Question

1. Are the following geographic hierarchies correct?

2. Do any US/Canadian area codes cross state/province lines?

3. Do any US/Canadian counties cross state/province lines?

4. Are there countries with counties but no principal country divisions (states/provinces)?

Zip / Postal Codes

US Style

Continent > Country > State/Province > Postal/Zip

UK Style

Continent > Country > Postal Code

Towns / Cities

US Style

Continent > Country > State/Province > City

UK Style

Continent > Country > City

Area Codes

US Style

Continent > Country > State/Province > Area Code

UK Style

Continent > Country > Area Code

Counties

US Style

Continent > Country > State/Province > County

UK Style

Continent > Country > County

Answer 1

To answer at least some of this:

2. Area codes do cross state boundaries in rare cases. There is a tiny bit of the Oregon 541 area code that is shared with California:

https://en.wikipedia.org/wiki/Area_codes_541_and_458

3. Zip codes do very rarely cross state boundaries, or have really odd zip code assignments compared to their geographical assignments. One example being 65733 in Arkansas and Missouri, where a few remote places are served by the 65733 Missouri Zip code:

https://en.wikipedia.org/wiki/ZIP_code#Secondary_regional_prefixes_.28123xx.29_and_local_ZIP_codes_.2812345.29

4. New Zealand used to have only counties, when they abolished provinces in the 1800's, and now a lot of them have merged into districts. Denmark also changed from counties to regions as well, without any provinces. Norway and Hungary are two more examples of a hop from country to county. There's a lot of fascinating pages on Wikipedia regarding counties and other administrative divisions:

https://en.wikipedia.org/wiki/County

1. There are certainly odd cases that would be hard to fit into the hierarchies you've outlined above. China, for example, has prefectural level cities that are below a province and above a county in their administrative division hierarchy. I'm not sure how much effort you would want to go through to make these generic by country, though.

https://en.wikipedia.org/wiki/Administrative_divisions_of_China

https://en.wikipedia.org/wiki/Table_of_administrative_divisions_by_country

https://en.wikipedia.org/wiki/Administrative_division

Hope that helps answer your question. =)

Answer 2

What I believe you are really looking for is ragged hierarchies

A ragged hierarchy is a user-defined hierarchy that has an uneven number of levels. Common examples include an organizational chart where a high-level manager has both departmental managers and non-managers as direct reports, or geographic hierarchies composed of Country-Region-City, where some cities lack a parent State or Province, such as Washington D.C., Vatican City, or New Delhi.

There isn't much to say about that with the lack of detail in your question except what the docs say but that would allow you to have a single dimension/hierarchy supporting all combinations in some sort of way.