Geography Dimension with diffirent grain in fact tables

skynl1 Mon Mar 12, 2012 5:35 am

I am trying to design a dimensional data model for a travel company. I have created a geography dimension at the post code level. I have a customer dimension that has outrigger to this geography dimension. But I have a fact table for bookings, which has attribute travel destination, which is at a region level, so should I snowflake the geography dimension into postcode, city, district etc
The business want to ask a questioon on which customers in Holland travelled to destination UK.

Vishy Mon Mar 12, 2012 8:52 am

Fact Table -- Customer_SK,OriginRegion_SK,DesitatinRegion_SK,DateTime_SK

Create CUSTOMER,REGION and TIME dimension.

skynl1 Mon Mar 12, 2012 9:39 am

Vishy wrote:Fact Table -- Customer_SK,OriginRegion_SK,DesitatinRegion_SK,DateTime_SK

Create CUSTOMER,REGION and TIME dimension.

This not what the users want, the fact table has the travel destination in the booking fact table, which is at the regional level, but the customer dimension is at the post code level, so the post code relates to where the customer lives. So the grain in the fact table is regional level and the customer dimension is at the postcode grain. So I assume now I have to create two dimenion one at the regional level, the other at the post code level, so we have the ability to roll up

bciampa Mon Mar 12, 2012 12:01 pm

Skynl1,

What is the regional level as compared to the post code level? Is regional a higher level than post code (i.e., several post codes fall within one region)?

Thanks,
Brian

Jeff Smith Mon Mar 12, 2012 2:39 pm

3 options:

1) create geo dimension from Post Code rolling it up to the Region. Create Surrogate Key for the Region. Create View of Region level and higher data from geo dimension (Select distinct Region, Region Surrogate Key, etc)

2) Create seperate geo Dimension and Region dimension. Denormalize the the region dimension into the geo dimension.

3) Create seperate geo dimensions and leave the seperate. If you don't need to roll up Post Code level data up to the region, then there's no reason to have Pst Code roll up to Region.

#1 and #2 are basically the same design. If the Post dimension is really long, then go with option 2. If it's small, then go with option 1. You should be able to build surrogates keys for the post code and region in one dimension build, which is the reason for option 1.

Denormalizing the Region dimension to the Post Code dimension is preferable to a snow flake IMHO.

Vishy Mon Mar 12, 2012 2:42 pm

Can't you put region related attributes in customer itself, postcode changes not necessarily means that region will chane but when region changes post code more likely to change. region is at very high level why do you want to outrigger it ?

so region will be there as separate dim as well as in customer dim with postcode details.

skynl1 Tue Mar 13, 2012 5:45 pm

Jeff Smith wrote:3 options:
Jeff

Thanks I was going for the 2nd option. Which enables the me to use the diemension in different ways in out data warehouse, as we have different gains in facts tables.

1) create geo dimension from Post Code rolling it up to the Region. Create Surrogate Key for the Region. Create View of Region level and higher data from geo dimension (Select distinct Region, Region Surrogate Key, etc)

2) Create seperate geo Dimension and Region dimension. Denormalize the the region dimension into the geo dimension.

3) Create seperate geo dimensions and leave the seperate. If you don't need to roll up Post Code level data up to the region, then there's no reason to have Pst Code roll up to Region.

#1 and #2 are basically the same design. If the Post dimension is really long, then go with option 2. If it's small, then go with option 1. You should be able to build surrogates keys for the post code and region in one dimension build, which is the reason for option 1.

Denormalizing the Region dimension to the Post Code dimension is preferable to a snow flake IMHO.

Sponsored content

Geography Dimension with diffirent grain in fact tables

Geography Dimension with diffirent grain in fact tables

Re: Geography Dimension with diffirent grain in fact tables

Re: Geography Dimension with diffirent grain in fact tables

Re: Geography Dimension with diffirent grain in fact tables

Re: Geography Dimension with diffirent grain in fact tables

Re: Geography Dimension with diffirent grain in fact tables

Re: Geography Dimension with diffirent grain in fact tables

Re: Geography Dimension with diffirent grain in fact tables