# Handling Time Zones

The obvious first step towards solving this problem is to hold the data in your fact table at one common time zone, probably GMT. However doing the time zone translation isn’t as simple as finding how many hours + or – each time zone is with regard to GMT, of course. The big complicating factor is Daylight Saving Time, and as Wikipedia shows it’s a pretty big complicating factor – in my opinion you wouldn’t want to write code to model these rules, you’d be much better off having a table which for every hour of every day in every time zone you were interested in would give the equivalent hour and day in GMT. So if you were interested in 20 time zones and had 5 years of data in your fact table, you would have approximately 5*365*24*20=876000 rows in this table. Populating this table would be a pain though… I wonder if there’s a web service out there which can do this conversion for you, operated by people who keep on top of all the changes surrounding Daylight Saving Time such as the recent ones Wikipedia mentions in Indiana?
How you can then use this table in your AS2005 cube design is the really interesting question. My first thought was that even though this isn’t a many-to-many relationship (unless you wanted the duplicated hours that occur when DST moves backwards to be modelled as one member rather than two, which doesn’t seem sensible) you could model it as such – the table descibed in the previous paragraph would become your intermediate measure group, which joins to the fact table via a GMT Time dimension; this time dimension could then be reused (as a role-playing dimension) as your Time in Time Zone dimension, joined to just the intermediate measure group along with a separate Time Zone dimension. You could then hide your GMT Time dimension and your users would just have to select which ever time zone they wanted to analyse by on the Time Zone dimension and the Time in Time Zone dimension would behave exactly as described in my first paragraph.
However there are some drawbacks to this approach. First and foremost is that you couldn’t make use of any aggregations above the hour attribute, because the many-to-many relationship is resolved at this level. This would mean that if you wanted to run queries at the Month or Year level they might not perform as fast as you’d like. Also, the larger the table you’ve built your intermediate measure group from, the slower it will be to resolve the many-to-many relationship, although partitioning this measure group by Time Zone and possibly Year to will mitigate this effect.
The alternative is to model all this as a single regular dimension. You’d have a GMT Hour attribute, an Hour in Time Zone attribute (with a one-to-many relationship between the two) and a Time Zone attribute; the key thing to note here is that every hour in a time zone becomes a single member, so the overall dimension size is much larger than in the many-to-many approach. Since AS2005 can (so I’m told) handle dimensions of many million members even in 32-bit the size issue wouldn’t be the showstopper it would have been in AS2000, although it would presumably have some impact on overall performance. The fact that you could now use aggregations above the Hour level would, on the other hand, be a big factor in improving performance which I would guess would more than make up for the negative impact of dimension size. Judicious hiding of attributes and the use of user hierarchies (for example Time Zone->Year->Month->Day->Hour) would mean that the complexity of the dimension could be hidden from the user; the only real drawback I can think of is that because every Hour, Month, Year etc would be a separate member on the dimension, it would be difficult for users to create reports in an ad-hoc tool which selected a couple of months on a visible axis and then allowed them to slice by time zone – something that wouldn’t be an issue with the many-to-many approach.

One other approach I thought about was using a stored procedure called from an MDX Script assignment to perform the time zone translation – but as I said earlier, trying to handle all the wrinkles of DST changes in code would be a real nightmare, and calling a stored procedure multiple times to get values for each Hour probably wouldn’t perform well either.
I’d be interested to hear other people’s thoughts on how to solve this problem; even better, it would be good to hear from anyone who’s had to actually implement a solution. So post some comments! It would be useful to know whether the regular dimension approach does in fact perform better than the many-to-many approach, for instance…
UPDATE (March 2015)  – almost ten years after writing this post, I’ve been asked to revise it. With rather more experience of SSAS than I had back then, I can say that my proposed solution for a single dimension is just wrong and wouldn’t work, and using a stored proc wouldn’t be feasible either. The only workable solution would be the many-to-many approach.

### 7 responses

• Hi Mitch, you’re right, this post was flat-out wrong in many respects and I’ve updated it appropriately. However the problem it was attempting to address is not the one that is described in your link – it is a different one (as the post itself states) where the user needs to be able to see data in any time zone they choose, regardless of the original time zone where the data was generated.

1. Rob says:

Chris – is there a way to do this in Dax? Here is my scenario: I have GMT weather data, that I’m converting to Eastern time. dateTime-4/24 works during DST, but I need dateTime-5/24 to handle standar time. Any ideas? I’ve flagged DST start and end in my date table, but they only show on the start date and end date. If I could flag the “inbetween” days, I’d be set.

• If you’re only converting to a single time zone, you shouldn’t be doing this in DAX – you should be doing it in your ETL before it gets to the model.

2. Henrique Lima says:

Hello Chris.

I’m struggling to find a solution for the timezone conversion.

I work at a company that has many branches around the globe.
I’m creating a SSAS cube to convert the date and time to each brach timezone. But users should also be able to select any different timezone if they want to.

So I have a Date, Time and Timezone dimensions.
The Fact table has the Date_id_UTC, Time_id_UTC (so the fact has the date and time in UTC time zone), and Timezone_id that is the branch local timezone.
So I created a FactLessTzConversion table to do the many-to-many relationship. As you may have imagined, this fact table is huge. Especially because the Time dimension has minutes, since we have some timezones like (UTC+05:30) Chennai, so we need the minutes as well.

But Im unable to get this cube right. The Date and Time are changing according to the selected timezone, but the aggregation is not considered. It just shows the total sum of the measures.

Would you be able to shed any light to my issue, please?

Thank you!

• It’s hard to say. Your best bet is to post this on a forum with a lot of detail about your mode.

3. Henrique Lima says: