Multiple TimeZones and SSAS 2005

Question

Multiple TimeZones and SSAS 2005

m_kuyper

SSC Enthusiast

Points: 162
More actions
March 8, 2006 at 1:01 pm

#168004

As my company is going international, I'm looking to expand my 2005
cube's datetime dimension to incorporate different time zones. Has
anyone successfully implemented attributes in a single datetime dim
that will span different timezones (along with useful aggs)? Or, has
any one done this succinctly using mutliple dims? I'm concerned about
dimension member/data/cube size explosion in tackling this problem and
appreciate any insight. Similar to what Microsoft did for currency
conversion, I hope that at some point they will put in special AS
functionality to handle this.
thanks!

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

AndrewMurphy SSCertifiable Points: 5586 More actions · Answer 1

Why not convert all your time-based data to one base-date/time.

Surely going international doesn't mean you need to store multi-time zones...(if this was the case most large USA companies would have hit this problem already). Will you be cross-referencing data by time-zone? Can data appear under a different axis depending on the time-zone of the viewer?

I think you're going to end-up chasing your tail going down this route.

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 2

I tend to agree with Andrew, I would have thought you'd "centralize" on a single time (say GMT or one of the US zones).

One thing to be aware of though is when you add Time intelligence to the cube. Where calculations used to be client side (ie performed in PTS) these are now calculated on the server, so a user in (say) Australia hitting a server on the East coast of the US could be shown 'today' as what would be yesterday to them. E.g. where the server is in Miami, at 5pm on Thursday 9th it's alreadt 8am on Friday 10th in Aus. So if you have say Week-to-date time intelligence in there, it will be wrong for those outside the timezone of the server.

This was mentioned to Thierry at TechEd but not sure if there's a fix (or workaround) for it as yet.

Steve.

m_kuyper SSC Enthusiast Points: 162 More actions · Answer 3

Thanks for the responses. To give more information, the cube aggregates call detail records that are at an hour grain in the underlying fact table (the datetime dimension hierarchy level spec = hour>date>week>month>quarter>year). We have clients who want to view their call data by their own corporate headquarters’ TimeZone but also by each of their branch office’s TimeZone. The way I see it, in order to build aggregations up the datetime dim’s hierarchy, I would have to either break out the one datetime dimension into many for each TimeZone or keep the on TimeZone dim and add attributes reflecting each TimeZone.

After I first posted, I did come across a blog of a noted AS guru who discusses the challenge at length: http://spaces.msn.com/cwebbbi/blog/cns!7B84B0F2C239489A!367.entry

Amongst other things, he also brings up the Global Daylight Savings issue. I am going to try some of his ideas. Has anyone else experimented with his approaches (I wish there were trackbacks to this blog, which there are not) or any others?

AndrewMurphy SSCertifiable Points: 5586 More actions · Answer 4

The client 'needs' sound fine in theory.....but work through some sample data with them and then see what do they really want. I suspect the reconciliation department that will eventually arise to reconcile the 2 versions of the same report will make enron seem like "joe's fish and chip shop"!

Sometimes the best solution to these requests/needs is to kill them with kindness!