Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Handling different Time Zones

4 posters

Go down

Handling different Time Zones Empty Handling different Time Zones

Post  Danik1994 Fri Oct 29, 2010 1:55 am

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.



Posts : 1
Join date : 2010-10-29

Back to top Go down

Handling different Time Zones Empty Handling different Time Zones

Post  alex.caminals Mon Nov 08, 2010 5:18 am

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,

Posts : 15
Join date : 2009-02-25
Age : 46
Location : Barcelona (Spain)

Back to top Go down

Handling different Time Zones Empty Dimentional and fact Table model based on TimeZoneInfo (.Net Function)

Post  william.hullsiek Thu Mar 31, 2011 11:19 am

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
@TimeZoneKey = DimTimeZoneInfo.TimeZoneKey
, @StandardUtcOffset = DimTimeZoneInfo.StandardUtcOffset
, @DaylightUtcOffset = DimTimeZoneInfo.DaylightUtcOffset
, @SupportsDaylightSavingTime = DimTimeZoneInfo.SupportsDaylightSavingTime
DimTimeZoneInfo.[Uid] = dbo.ufnTimeZoneSingleCriteria(@TimeZoneSingleCriteria);

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

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


Posts : 2
Join date : 2011-03-28
Location : Saint Paul, Minnesota

Back to top Go down

Handling different Time Zones Empty Re: Handling different Time Zones

Post  salaman Thu Mar 31, 2011 3:23 pm

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.


Posts : 21
Join date : 2011-03-24

Back to top Go down

Handling different Time Zones Empty Re: Handling different Time Zones

Post  Sponsored content

Sponsored content

Back to top Go down

Back to top

- Similar topics

Permissions in this forum:
You cannot reply to topics in this forum