Architecture for "Overlapping Dimensions"?

Question

Take a SQL RDBMS data warehouse -- typical facts and dimensions type layout.

Say you want Orders x Country. Maybe a date field, an orders field, a country field.

And then if you want a report/ software that slices by Continent? Easy country x continent dimensional table, right?

But what's the proper architecture for overlapping dimensions?

For instance maybe you want "Greater Country" that includes the UK, British Isles, Island of Ireland -- ... these are larger groups that contain overlapping smaller pieces.

Northern Ireland for instance is a component of both the UK and Island of Ireland.

What's the best architecture so that if an end-user select "UK" or "Island of Ireland" -- that Northern Ireland pops up? Without duplication?

I guess there are several ways to do this --- I don't actually care about the UK ha it's just an example --

You have a small component Dimension A. You have a larger grouping Dimension B but A is not unique to a single B.

Every "cube" based reporting system is anti-thetical to this, but there are use cases. Nor do you want to create tons of dimensions.

Is the best method simply a dimensions table that shows full membership -- aka Northern Ireland - UK, Northern Ireland - Isle of Ireland .... then do a join, return distinct? Doesn't seem efficient but maybe that's the best way.

Answer 1

In a data warehouse application, the answer would be to include the greater country field in your addresses, countries, or geography dimension, alongside continent, country, and everything else thematically linked. This is a violation of normalization, but that's totally OK in an OLAP dimension.

It might look something like this:

 Synthetic Key | Continent | Geo-Region     | Currency | Country
 ============= | ========= | ============== | ======== | ==============
 1             | Europe    | Western Europe | EUR      | France
 2             | Europe    | British Isles  | EUR      | Ireland
 3             | Europe    | Western Europe | CHF      | Switzerland
 4             | Europe    | British Isles  | GBP      | United Kingdom

A user can group by Continent, Geo-Region, Currency, or Country, and get a different result each time. You don't need a DISTINCT, because your query will be using GROUP BY.

If your geo dimension is at a lower grain, you might have Northern Ireland as its own record, so you can put it in an Ireland geo-region but still within the United Kingdom country. You might put England and Scotland into different Sales Regions, or drill down to counties, cities, post codes, etc.