Special Higher-Level Rows in Dimension

View previous topic View next topic Go down

Special Higher-Level Rows in Dimension

Post  VHF on Thu Aug 11, 2011 11:42 am

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:

TerritoryKeyTerritoryDistrictRegion
1Downtown ChicagoChicagoUpper Midwest
2North ShoreChicagoUpper Midwest
3NW SuburbsChicagoUpper Midwest
4Racine-KenoshaSE WisconsinUpper Midwest
5NW ArkansasOklahoma-ArkansasSouth 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:

TerritoryKeyRowTypeTerritoryDistrictRegion
1TerritoryDowntown ChicagoChicagoUpper Midwest
2TerritoryNorth ShoreChicagoUpper Midwest
3TerritoryNW SuburbsChicagoUpper Midwest
4TerritoryRacine-KenoshaSE WisconsinUpper Midwest
5TerritoryNW ArkansasOklahoma-ArkansasSouth Central
6DistrictNAChicagoUpper Midwest
7DistrictNASE WisconsinUpper Midwest
8DistrictNAOklahoma-ArkansasSouth Central
9RegionNANAUpper Midwest
10RegionNANASouth 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?

VHF

Posts : 236
Join date : 2009-04-28
Location : Wisconsin, US

View user profile

Back to top Go down

Re: Special Higher-Level Rows in Dimension

Post  ngalemmo on Thu Aug 11, 2011 1:19 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Special Higher-Level Rows in Dimension

Post  VHF on Thu Aug 11, 2011 2:38 pm

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!)

VHF

Posts : 236
Join date : 2009-04-28
Location : Wisconsin, US

View user profile

Back to top Go down

Re: Special Higher-Level Rows in Dimension

Post  ngalemmo on Thu Aug 11, 2011 4:49 pm

Agreed.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Special Higher-Level Rows in Dimension

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum