TimeZones & date dimensions, again...

[Goodie666]

Hello,

I was just reading up about date dimensions and handling various timezones when using them when I stumbled upon the following thread: thread353-1421422

I read it over and over and my only conclusion is that handling time zones is not easy... btw, thanks for all the comments in that post, they do provide quite a lot of information .

I am considering using a date and time dimension (2 separate dimensions) for a small data mart in MSSQL 2005 but I am encountering the same kinds of issues as the ones described in the original thread. I am not using SSAS, just a dimensional backend on SQL 2005. I can easily store all my timestamps in UTC and display them properly by converting to the user's local time, but the issues really appear at query time...

Say my date dimension also contains a day of the week, and I want to query all facts for mondays over the past year, eastern time. I just have no idea how this can be done when the date FKs are pointing to UTC time zone... I would have to include facts from part of the sunday UTC and not the whole monday UTC...

To get around this issue, the only way out I could find would be to store my UTC timestamp and FK in my fact, along with my Eastern timestamp and FK as well... And if I need to support more timezones, add more column to my fact. It's either that or my date dimension have to take that into account, but I would tend to put that on a fact since it really is about it rather than the date itself.

I just wonder how people handle these types of situations? I've been trying to come up with a relatively generic pattern but without success. Storing the data in UTC is a very good thing, but when comes the time to query it from a different timezone, I feel like it gets very complicated . And that kinda makes me question the whole usefulness of a date dimension at that point (I know it's a good thing though but in that case, it creates more problems than it helps solving others).

Or maybe there's no real issue and I'm just looking for trouble

Thanks for any advice/strategy for this kind of problem,

Greg

 

[RiverGuy]

Usually, you would only care about time zones if you have a business with operations spread across different time zones. So if that is not the case, I wouldn't worry about storing them in UTC only to try and convert them back to your actual time zone for every query. If you do have operations in multiple time zones, I would first see if the simplest solution can work for you, which would be the following:

factSomeFactTable
----------------
CorporateDateKey INT/DATETIME --(could be UTC or the timezone of the corporate office)
CorporateTimeOfDayKey INT
LocalDateKey INT/DATETIME --(would be the local time where the transaction took place)
LocalTimeOfDayKey INT
....

 

[Goodie666]

Hi RiverGuy,

Indeed, while it is not a requirement for now, I do need to plan for it because in the future, I could have two offices in different timezones feeding my warehouse. The thing is that you would not look at the data for two different timezones simultaneously, you would want to see either eastern or pacific because they operate somewhat independantly. That's why the pattern you refer to is the one I was thinking about, to have the local and utc timestamps and FKs to the date/time dimensions. I think that it's fair to say that if you're trying to look at two timezones at the same time, then you have to view/query the data in UTC. I have to bounce that with management this week though, to see if that kind of assumption actually works...

I was just wondering overall how people were handling these situations because it does feel like opening a can of worm

Greg

 

[RiverGuy]

It depends on the type of business.

For an online retailer, you may have customers throughout the world purchasing 24/7, interacting with the same exact business unit. You might want to analyze what time of day certain products are likely to be sold based on the customer. Maybe more products are sold in the morning, or the evening. So you would track the local customer's time of day. It doesn't matter what the time zone is, just "what time is it."

You may also want to track your sales trend after a certain promotion starts. In this case, a single, uniform, time zone would also be needed--preferably in the company's own time zone so it's easy to relate to. If the promotion started at 12am Eastern, you want to see what it did after 11pm Central, 10pm Mountain and 9pm Pacific.

For a restaurant example where the entities operate as individual units, you may want want to see your traffic between 5 and 7pm. Rather than extracting data from your Los Angeles restaurants and your New York restaurants and converting them to UTC only to convert them back in the report, why not just send over "5:00pm" for 5'oclock sales in LA and "5:00pm" for 5'oclock sales in NYC? In the restaurant example, the UTC time or the corporate standard time are almost irrelevant. By sending over the local time, you can still look at data from different time zones simultaneously without any additional work.

 

[Goodie666]

I see... I think I was not having this reflexion regarding the point of view that looks at the data, which is what determines this requirement... It's like 2 facts could be using totally different time zone settings because they serve different purposes, one for the local stores, one for the head office in UTC for example.

This is indeed very interesting. I keep on going back to "oh my god if I have to convert an input Date Id dimension FK to another timezone Date Id FK this design is completely broken" but that's not necessarily the case.

Greg

 

[johnherman]

I would store your dates and times in UTC. If there is no need for time zones now, you can create a calculated column to store the date/time in the active time zone. In fact, that's the process you would use for multiple time zones, except that you would need the time zone as an input, and might be better off storing the info in a table rather than a calculated column. There are some weird aspects to consider. For instance, India is on the half hour. Saudi Arabia has no official time. Daylight Savings Time begins and ends at different times for different countries. In the US, Arizona and parts of Indiana do not use DST.

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