Special Higher-Level Rows in Dimension

Let’s say I have a dimension to represent sales territories. Each territory roles up to a district; each district roles up to a region. In a fully denormalized dimension, I would have something like this:

TerritoryKey	Territory	District	Region
1	Downtown Chicago	Chicago	Upper Midwest
2	North Shore	Chicago	Upper Midwest
3	NW Suburbs	Chicago	Upper Midwest
4	Racine-Kenosha	SE Wisconsin	Upper Midwest
5	NW Arkansas	Oklahoma-Arkansas	South Central

So far, so good. However, in addition to having facts at the territory level, I also have facts arriving at the district and region levels. Now I could create shrunken dimensions for District and Region, but as discussed in this thread that causes problems with my BI tools. They don’t understand that Region and District are conformed subsets of Territory and therefore they don’t allow the users to do ad hoc queries comparing aggregated measures from differing grain fact tables.

I would like to solicit comments on a design using a single dimension table with special rows to represent the shrunken dimensions. Something like this:

TerritoryKey	RowType	Territory	District	Region
1	Territory	Downtown Chicago	Chicago	Upper Midwest
2	Territory	North Shore	Chicago	Upper Midwest
3	Territory	NW Suburbs	Chicago	Upper Midwest
4	Territory	Racine-Kenosha	SE Wisconsin	Upper Midwest
5	Territory	NW Arkansas	Oklahoma-Arkansas	South Central
6	District	NA	Chicago	Upper Midwest
7	District	NA	SE Wisconsin	Upper Midwest
8	District	NA	Oklahoma-Arkansas	South Central
9	Region	NA	NA	Upper Midwest
10	Region	NA	NA	South Central

This approach seems to address the issues I was facing... I can now have facts at the district or region level that point to their own unique row in the dimension, and because I still have single dimension table the BI tools should allow users to compare across grains (ex: compare a forecast at the region level with actual sales facts at the territory level (aggregated by region, of course.)) Not snowflaking into 3 dimension tables should keep the queries fast.

The RowType column is there to facilitate ETL and also for use when querying the dimension directly; it is not needed when doing star-schema joins with a fact table.

The only (very minor) thing I don't like is using a FK called “TerritoryKey” in a fact table that relates to a region or district!

Any thoughts?

It's ok. It's a flattened hierarchy and making accomodations for tools is ok. After all, ease of use is what its all about.

As you mentioned in 'that thread', it gets more complicated if you are taking about a customer or product dimension.

I don't think I want to extend this pattern all the way to my customer (or product) dimension, but it will at least let me keep things down to a single outrigger and not snowflake all over the place (a blizzard schema!)

Agreed.