Multiple time zones for fact tables
3 posters
Page 1 of 1
Multiple time zones for fact tables
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
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...
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...
Re: Multiple time zones for fact tables
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- Posts : 1212
Join date : 2009-02-03
Location : USA
Re: Multiple time zones for fact tables
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.
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.
Similar topics
» Queries multiple fact tables at the same time
» Modelling question - Multiple fact tables with one to many relationships and querying from many fact tables
» Time zones question
» Single fact table vs multiple fact tables - what is the right way in a dimensional model?
» Multiple Fact Tables vs. Consolidated Fact Table
» Modelling question - Multiple fact tables with one to many relationships and querying from many fact tables
» Time zones question
» Single fact table vs multiple fact tables - what is the right way in a dimensional model?
» Multiple Fact Tables vs. Consolidated Fact Table
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum