Handling different Time Zones

I have SSAS 2008 with sql server 2005 database as a source. Currently, we store all dates in UTC time and use system generated Time dimension in the cube.

Up until recently the granularity of a day was just fine for our needs.

Now ( I've been doing SSAS for just a month), I've been asked to redesign DateTime dimension so it allows for different time zones, as we need to send out reports to users in different time zones. I just need to have EST, PST and UTC time zones with no DayTime Savings adjustments.

I'm not able to find a solution on the web...

Please help.

thanks

Hi Danik1994,

To start, I have to say that it is a best practice to split the DateTime dimension in two separate dimensions: Date and Time. The benefits are a lower cardinality that improves performance dramatically if we compare it to a single DateTime dimension.

With regards to multiple time zones, the solution is to have multiple aliases of the Date and Time dimensions and join to them from your fact table. In your case you will need 4 extra foreign keys which may look like a long row length extension to your fact table rows, but it's the only effective way of handling this. Using a single Date and Time dimensions pair and try to add/substract hours (also modifying the days) at run time is going to create a huge overhead when running the queries. And trying to add extra columns in the Date or Time dimensions is only going to work in the case you have a single DateTime dimension, which is going to hinder performance as well.

I strongly suggest you to go for the multiple Date and Time dimensions aliases. Let me know if this satisfies your requirements.

Best regards,

I addressed this problem by creating two tables.
1. DimTimeZoneInfo which is based on the .Net TimeZoneInfo class - this identifies all my timezones in question.
(This allows you to use TimeZoneInfo in Report Services).
2. FactTimeZoneInfo - which stores stores the timezone abbreviation and offset.

You have the Business Rule in which there can be NO or Multiple Changes to Timezone within a year. (If you want to internationalize your code).

The clustered unique index for FactTimeZoneInfo is { TimeZoneKey, utcLb, utcUb } -- this is valid for the inclusive range.

The query (such as function...)
-- Get the TimezoneInfo Properties...
Declare @TimeZoneKey SmallInt;
Declare @SupportsDaylightSavingTime Bit;
Declare @StandardUtcOffset nvarchar(6);
Declare @DaylightUtcOffset nvarchar(6);
Declare @Offset nvarchar(6);
Declare @IsStandardTime Bit
Select
@TimeZoneKey = DimTimeZoneInfo.TimeZoneKey
, @StandardUtcOffset = DimTimeZoneInfo.StandardUtcOffset
, @DaylightUtcOffset = DimTimeZoneInfo.DaylightUtcOffset
, @SupportsDaylightSavingTime = DimTimeZoneInfo.SupportsDaylightSavingTime
From
DimTimeZoneInfo With(Nolock, FORCESEEK, FASTFIRSTROW)
Where
DimTimeZoneInfo.[Uid] = dbo.ufnTimeZoneSingleCriteria(@TimeZoneSingleCriteria);

If @SupportsDaylightSavingTime Is Null or @SupportsDaylightSavingTime = 0
Set @Offset = @StandardUtcOffset
Else Begin
Select
@IsStandardTime = FactTimeZoneInfo.IsStandardTime
, @Offset = FactTimeZoneInfo.TimeZoneOffset
, @Abbreviation = FactTimeZoneInfo.TimeZoneAbbreviation
From
FactTimeZoneInfo With(Nolock, FORCESEEK, FASTFIRSTROW)
Where
FactTimeZoneInfo.TimeZoneKey = @TimeZoneKey
And @utc Between FactTimeZoneInfo.utcLb and FactTimeZoneInfo.utcUb
End;

Return(SwitchOffset(@utc, @offset)); -- Converts the UTC value to a local timezone.

As Alex suggested the best solution is to have multiple aliases.

In our fact tables we have utc_date_key, utc_time_key, local_date_key and local_time_key.

This allows users the flexibility of reporting on local or utc date and time and will cater for any other time zone you may encounter in the future. Right now, UTC, EST and PST only seem relevant but someday in the future Europe might come into the picture.