×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Question regarding handling of time
3

Question regarding handling of time

Question regarding handling of time

(OP)
Hello,

I am in the process of trying to design a simple fact table with a couple of dimensions, including a date dimension. I am wondering however how people deal with dates in terms of time zones etc. I was thinking of handling by having two date FKs in my fact table: one for the local time, which could be of any timezone, and one for the UTC time. This way, one could perform queries based either on UTC time or the local time that something was done if that is known to them.

How do you usually approach having to manage different timezones in data warehouses?

Thanks,

Greg

RE: Question regarding handling of time

One way I know of to handle this situation is to convert every date record you get from a source system to a specified time-zone (GMT for example) before storing it in the DWH. Next you can keep track of the timezone the event took place (perhaps a seperate dimension).

Hope this helps,

Hans  

RE: Question regarding handling of time

I agree with Hans63.  Convert to UTC time (Universal Time Constant), formerly known as GMT (Greenwich Mean Time). Then, if necessary, have a table which converts the time zones and another, or perhaps in the same table, which converts Daylight Savings Time.  

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

RE: Question regarding handling of time

First I would seperate Date and Time into seperate dimensions.  

I always have a Date dimension which has a leaf of date and includes various attributes about that date such as Day Of Week Day of the year and other such relevant information.  

I then have a time dimension which is has a leaf of minute with various ranges as needed by the customer such as Hour, Half hour and 15 minute.  The break out really is dependent on the customer and detail of analysis.

As Hans said you can key everything as GMT which would require that you know the offset of where the transaction occured.  You cold then expand your Time Dimension to have attributes of your various time zones.

The more I think about it the more I feel you would have to key everything to a common timezone, due to the fact each record can have a different offset.

RE: Question regarding handling of time

MDXer is right in pointing out to seperate time from date.

When keying everything to 1 timezone, you have to be aware of introduced or hidden date-differences. Espescially if you are dealing with a worldwide 24 x 7 situation.

1am GMT of december 12. is still in december 11 when looking from USA persepective. So in reporting you may have to take this into acocunt.

RE: Question regarding handling of time

If you adjust all of your transaction dates to be a base of UTC time then The situation of 1AM GMT on Dec 12 being 4pm Dec 11 PST as you would have applied the logic in your etl to key the PST transaction to the Appropriate UTC Time.

In your Geography dimension you would probably want a Time zone attribute along with a UTC Offset Attribute you could then convert UTC times into Regional Times when needed.

RE: Question regarding handling of time

(OP)
Hello,

Thanks for the replies. Yes, I will be using UTC times either way but I was wondering about the handling of converting the times, to either let that be done by the reporting engine or have the local time recorded so that as long as you're browsing for a given region you could just use that attribute instead. Separating time and date was definitely something I had spotted before but thanks for mentioning it, it's well worth it i think.

Greg

RE: Question regarding handling of time

what technologies will you be implementing?
 

RE: Question regarding handling of time

(OP)
I'm not sure I understand what you mean by that sorry. The source will always be in UTC so I will have that. And a row will be associated with a geographic region that has a timezone offset so that's how I was thinking of being able to have both UTC and local time. Was this your question?

Greg

RE: Question regarding handling of time

Well you be using the MS platform, what will the users be using to interact with the data?

RE: Question regarding handling of time

(OP)
Oh, I see... Well it's going to be MS all the way until the reporting layer. At that point it's not decided, but SSRS is a given of course. And potentially other reporting engines, especially crystal reports. But for sure SSRS and before that, it'll be all Microsoft products (ODS, ETL, DW etc.)

Would that influence my options greatly?

Greg

RE: Question regarding handling of time

if you are building Cubes in SSAS then you may be able to accomplish what you need by using dimensional translations.  This is the type of thing that has a few ways to accomplish this but you really need to dial in exact requirements to make sure that you implement the method that delivers all functionailty.

RE: Question regarding handling of time

(OP)
Ah, I see... I am not using SSAS yet, right now we're only building something intermediate. It may be used for SSAS later or just be used for further ETL work, but this brings an interesting point. Thanks for the info, I'll try to look more into it to ensure we can cover as much as possible.

Greg

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close