Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Multiple time zones for fact tables

3 posters

Go down

Multiple time zones for fact tables Empty Multiple time zones for fact tables

Post  gbaren 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

http://equalsql.wordpress.com

Back to top Go down

Multiple time zones for fact tables Empty Re: Multiple time zones for fact tables

Post  BoxesAndLines 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...
BoxesAndLines
BoxesAndLines

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

Back to top Go down

Multiple time zones for fact tables Empty Re: Multiple time zones for fact tables

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

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

http://aginity.com

Back to top Go down

Multiple time zones for fact tables Empty Re: Multiple time zones for fact tables

Post  gbaren Thu Apr 03, 2014 8:38 am

Thank you! A very clear explanation.

gbaren

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

http://equalsql.wordpress.com

Back to top Go down

Multiple time zones for fact tables Empty Re: Multiple time zones for fact tables

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

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