0

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.

3
  • This is a question that no doubt will have a bunch of different answers, based on peoples' personal preferences and opinions. For me, I'd pick up on the fact that you say "nor do you want to create tons of dimensions" ..... but what constitutes "tons of dimensions" - to me it sounds like you're possibly just adding one more level in some kind of town/region/country hierarchy. I wouldn't classify that as ending up with "tons of dimensions"
    – Craig
    Commented Aug 16, 2023 at 0:18
  • I agree -- I mean most cube-based/ Business intelligence architecture assumes a many:1 relationship between subordinate dimensions. City rolls up to State. State rolls up to Country. This is fact a hard-coded requirement for OLAP cubes. Cubes must fit neatly into other cubes. Now I'm not using OLAP, but conceptually many reporting tools kind of rely on this mental model. I'm trying to figure out a design practice where a subordinate relationship between dimensions is not Many:1. Northern Ireland is part of two Greater Entities. This kind of 'rollup' is not too common in Business Intelligence
    – user45867
    Commented Aug 16, 2023 at 15:41
  • So I'm saying one "neat little fit into traditional thinking" method is to simply have binary "Is UK" "Is Irish Isle" "Is Greater China" dimensions but that's horrible mess, both for scalability as well as data science applications. I guess the question is a little more niche than expected. And yes I think many "elegant code principles" and "avoid anti pattern" stuff --- is more art than "this is the exact answer" -- but there needs to be a message board for that as well
    – user45867
    Commented Aug 16, 2023 at 15:43

1 Answer 1

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.