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

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  

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

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.

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.

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.

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

what technologies will you be implementing?
 

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

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

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

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.

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