July 2, 2010 at 4:30 pm
I am building a finance cube for our finance dept, the data from the source has a UTC datetime field. But in the cube, we would like to do monthly revenue report in UTC, which is easy.
But they also want to aggregate the same revenue data, but analyze it by other timezone, like pacific time, or germany time, or something.
I am not sure if there's any way to do it besides storing those different time physically in a separate column.. so that it's aggregating it right through the date time hierarchy
and of course, then you get to day light time saving, and it's going to get messy.. on top of the extra data volumn incurred by just storing all those separately
is there a more graceful way to handle it?
thanks
July 6, 2010 at 10:04 am
bump?
July 8, 2010 at 6:55 am
The other choice would be to only store offsets for various timezones. The trick is going to be striking the correct balance between storage and processing. It is somewhat similar to supporting currency conversion.
You are correct that time changes/daylight savings time will cause additional challenges both in the computation and for the display to the user. Don't forget that some areas have differences that are not whole hours.
I've been brooding on this problem for a day and I think I have a good compromise -- splitting the work between ETL and cube processing. This problem caught my interest since I've done a couple of projects with timezone considerations.
At a high level, the minimum information needed is a table which holds all the transition date-times for a timezone, the new UTC offset, and a label (this is for display purposes). In order to translate a time look up the greatest transition date less than or equal to the date to display, add the offset (could be negative), and retrieve the label. This could easily be written into a SQL function, but could be dicey in MDX.
The trick is to bulk this transition table up so that there is an entry for every timezone at every transition point. In other words, if the US changes clocks at 2:00 am on April 3 and October 10 and Germany changes clocks at 3:00 on April 10 and September 23, then the table would need entries for every timezone on all 4 date-times. The offsets for the US would be identical on the April 3 and September 23 entries.
The union of transitions becomes a dimension in the Cube and each transition would be given a key.
You will also want a dimension for each timezone -- meaning region of the globe. This is what the user will select for display.
Finally, you will need a Fact/bridge table which holds the TransitionKey, TimezoneKey, Offset (use minutes), and Label (the last two could be put into a table and replaced with a key).
For any table with a UTC field, add one additional field to hold the TransitionKey which applies.
This configuration allows translation to any timezone by linking up all the keys. The formatting could be done in the Cube or in the report. These support tables will need to be maintained much like a Date dimension.
The reason for adding a label is to remove confusion during a time change. Consider the "fall back" case when California changes from UTC -7 to UTC -8
UTC California
10/10 7am 10/10 12am PDT
10/10 8am 10/10 1am PDT
10/10 9am 10/10 1am PST
10/10 10am 10/10 2am PST
Without the label there will be two 1am lines which can cause confusion.
Please let me know if this makes sense or if you have further questions. Also how this works out if you move forward.
July 20, 2010 at 12:32 pm
Thanks for the reply!!! somehow I never got an alert, so I didn't see the reply until now!
I am still working through the problem. So do you mean I basically store each of the different datetime value in the different timezone separately as different column?
the fact table is a transaction fact, so when i have a transaction date say 7/20/2010 18:00 PDT
Then I will also store 7/21/2010 01:00 UTC in the same row in a different column?
And then also 7/21/2010 02:00 GMT+1 in yet another column?
And they all relate to dimDate, as a separate dimension that's available to the user ?
But if i have 4-5 different datetime per trasaction, then i may have like 20 or so different dimensions in 4 timezones??
is that right?
thanks
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply