How would you model a time dimension such that data in transactional source can be viewed from different time zones?
Basically I need not only the date but also the time (minute granularity is enough).
for instance, if we have a datetime like
1st jan 2005 at 01:45 AM in GMT stored in the db,
a user from US would see it as 31 Dec 2004 at 08:45 PM in GMT-5 time zone and someone in Asia would see the same transaction at 1st Jan 2005 6:45 AM in GMT+5 timezone.
This way the aggregates would change according to where the data is viewed from, but that is what my users want...
I considered having several time dimensions (one per time zone), but I already have time playing 8 different roles in my cube, so 8*24 time dimensions seems a bit akward...
Thanks
Were you able to find out anything on this?
|||Not sure if this is even more awkward.Suppose you have original table having columns:
GMTDate (KeyColumn)
GMTMinute (calculated column from GMTDate or populated by algorithm)
GMTHour (calculated column from GMTDate or populated by algorithm)
GMTDay (...)
GMTMonth (...)
GMTYear (...)
That original table is probably populated by some time generation code you have. So we can extend that code to generate more columns or create calculated columns on DSV:
GMTPlus1Minute
GMTPlus1Hour
GMTPlus1Day
GMTPlus1Month
GMTPlus1Year
We can create this way other sets of columns. Note that we do not need columns like GMTPlus1Minute, GMTPlus2Minute ,... We can have just Minute column. But i suppose later we can not have shared Minute attribute.
When we create time dimension we would have key attribute bound to GMTDate. The fact tables would also contain GMTDate and we will use those in the relationships. Other columns would be used for attributes used in different hierarchies.
USA Time Hierarchy
"Year" level <- GMTPlus5Year attribute
"Month" level <- GMTPlus5Month attribute
"Day" level <- GMTPlus5Day attribute
"Hour" level <- GMTPlus5Hour attribute
"Minute" level <- GMTPlus5Minute attribute
It really depends what user interface you have and how efficient it can hide different things (is it easier to hide dimensions than hierarchies or it does not matter).
|||
You might be interested to read this entry on my blog, where I discuss this problem:
http://spaces.msn.com/cwebbbi/blog/cns!7B84B0F2C239489A!367.entry
|||I think the adding additional columns to you time table and treating them as translations of the time member names would a solution worth looking at. This gives you the benifit of also being able to give formats that are cultur specific. On the downside, your timezone is based on your UI culture which doesn't work for timezones within the same country.
For populating the localized time columns you could write some managed code that uses .Net to convert to the date/time to the appropriate timezone and then format in the desired culture.
