Time dimension design for cross time zone and custom calendar warehouse, 1min granularity

Hi,

I'm designing a warehouse to bring together many different sources, at varying granularities and from differing time zones. All mainly time series of production or price information.

We are more then likely going to have to set the time dim granularity to 1min level to allow the kinds of analysis the business users need.

My current design has a combined date & time dimensions, at 1 minute granularity, and 124 attributes, so wide and very long (20 to 30 years of history). I want to know if this can be simplified for performance and design reasons. Any suggestions?

My problem is that the time of day has special meaning to the business, we have custom calanders with dates that run 06:00 to 06:00 (not midnight to midnight), ones that run on ISO week date system with days divided into 30 min segments which have 46 segments on the short day when daylight savings ends and 50 when it starts and other features across three different time zones. And all the facts need to be conformed to be able to analyse between facts down to the 1 min level.

Any advice on how to tackle this time dimension design?

Thanks

The question is, why do the date and time dimensions need to be combined? Normally you implement two independent dimensions, one for date, the other for time of day. You only combine the two if there is a specific relationship between date and time that you need to represent. But often this is handled by an event stucture to record the event, rather than trying to use a combined dimension to do the same thing.

Hi norrisp90,

I agree with ngalemmo. Usually, the Time dimension is separated from the Date dimension to avoid a huge number of the records in the Date dimension. Even with 1 second granularity and 30 years worth of days, you are going to have only 86,400 (24*60*60) records in the Time dimension and 10,950 (365*30) records in the Date dimension. In the fact table there will be 2 foreign keys - Date FK and Time FK.
Regarding the date and time in multiple time zones, it's recommended to have 4 foreign keys - Universal Date FK, Universal Time FK, Local Date FK and Local Time FK.

Thanks,
Alisher
www.streebo.com