Multiple time zones for fact tables

View previous topic View next topic Go down

Multiple time zones for fact tables

Post  gbaren on Wed Apr 02, 2014 2:16 pm

I was considering putting time zone in with the location but checked the KimballUniversity web site for design tips and found this:

Multiple Time Zones

Ralph Kimball wrote:To capture both universal standard time, as well as local times in multi-time zone applications, dual foreign keys should be placed in the affected fact tables that join to two role-playing date (and potentially time-of-day) dimension tables.

I've re-read this several times and realized that I don't know to what "role-playing" is referring. What does it mean in this context? Surely not two full date dimension tables...

gbaren

Posts : 15
Join date : 2014-01-30
Location : 02

View user profile http://equalsql.wordpress.com

Back to top Go down

Re: Multiple time zones for fact tables

Post  BoxesAndLines on Wed Apr 02, 2014 4:47 pm

You have to role-name the foreign key columns when you have more than one dependent relationships to another table e.g. date dimension. Begin_date_FK, End_date_FK, Purchase_date_FK...
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Multiple time zones for fact tables

Post  ngalemmo on Wed Apr 02, 2014 7:01 pm

You have one date dimension table with role based keys (multiple keys on the fact with different names) for local and UTC based dates. If you have a time of day dimension, you would do the same. When a time dimension table does not exist (very common) you store two timestamps as degenerate dimensions in the fact, one in local and the other in UTC time.

Sometimes, rather than UTC time, you may store a standard time, usually based on the timezone of the corporate HQ. However, this can be problematic as you may need to deal with daylight saving changes. Using UTC avoids this issue.

As far as the dimension table itself, sometimes designers define views for each date role. However, if you are using a BI tool with its own metadata, it is not necessary. The tool will generate proper SQL with table aliases to separate the joins and qualify the attributes.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Multiple time zones for fact tables

Post  gbaren on Thu Apr 03, 2014 8:38 am

Thank you! A very clear explanation.

gbaren

Posts : 15
Join date : 2014-01-30
Location : 02

View user profile http://equalsql.wordpress.com

Back to top Go down

Re: Multiple time zones for fact tables

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top


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