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

View previous topic View next topic Go down

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

Post  norrisp90 on Tue Sep 11, 2012 4:33 am

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

norrisp90

Posts : 1
Join date : 2012-09-06

View user profile

Back to top Go down

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

Post  ngalemmo on Tue Sep 11, 2012 4:44 am

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

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

Post  yuldashev on Wed Sep 12, 2012 12:23 am

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
avatar
yuldashev

Posts : 13
Join date : 2012-08-14
Location : Ottawa, Canada

View user profile

Back to top Go down

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

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

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