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