I was just tinkering with our schema and thinking about time - specifically UTC offsets and the time dimension. Our current scheme is for two dimensions; one to hold time of day and the other to hold dates. It works pretty well as we are normally querying either inter-day or intra-day. Not both. It also keeps the dimensioning code and tables under control.

As we are currently using UK time, the only issue to be aware of is the hour when the clocks go back in October. It doesn't affect queries though - we just get some data put in the wrong time-slot.

As the next version is going to be culture aware and support multiple time zones then this model fails because any query needs to take into account the possibility that the time zone offset may cause a difference in date members.

So for example, assuming the data is stored in the database in UTC, to get the facts for a particular day is:

select * 
from Facts f 
join Date d on d.DateID = f.DateID 
where d.Date = '2004-07-17' 

But with TZ offset (e.g. PST = -8)

select * 
from Facts f
join Date d on d.DateID = f.DateID
join Time d on d.TimeID = f.TimeID
where d.Year = 2004
and   d.Month = 7
and   (   
          (d.Day = 17 and t.Hour < 24 - 8)
       or (d.Day = 16 and t.Hour > 24 - 8)        
      )

Note that this can spill into months and years as well (imagine a time zone offset query around New Year's Eve). I don't think it gets any simpler with MDX.

If, on the other hand, we were to combine the date and time dimensions then the query becomes a lot simpler (here we can offset globally):

select * 
from Facts f
join DateTime d on d.DateTimeID = f.DateTimeID
where cast(floor((cast(d.Date as float) - 
    (8.0 / 24.0))) as smalldatetime) = '2004-07-17'

I think it's even easier in MDX with the Lag() function.

One problem to consider though is the explosion of size in the time dimension. The time dimension now has 525600 members per year as opposed to the old scheme with 365 (Date) + 1440 (Time) members.

Also, the SQL Server 2005 Intellicube wizard only has room for a single time dimension.