Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We are just starting design for a new data warehouse and we're trying to design how our date and time dimensions will work. We need to be able to support multiple timezones (probably at least GMT, IST, PST and EST). We were initially thinking that we would have one wide combined date time dimension down to maybe 15 minute granularity, that way we have one key in our fact tables and all the different date time data for all supported timezones are in one dimension table. (i.e. Date Key, GMT Date, GMT Time, IST Date, IST Time, etc...)

Kimball suggests to have a separate day dimension from the time of day dimension to prevent the table from growing too large (The data warehouse toolkit p. 240) which sounds fine however that would mean we have two keys in our fact tables for each time zone we need to support (one for the date and one for the time of day).

As I'm very inexperienced in this area I'm hoping someone out there knows the tradeoffs between the two approaches, i.e. performance vs. the management of all the different time zone keys. Maybe there are other approaches too, I've seen some people talking about having a separate row in the fact table per timezone, but that seems like a problem if you fact tables are millions of rows then you need to quadruple it to add time zones.

If we do the 15 minute grain, we'll have 131,400 (24 * 15 * 365) rows per year in our date time dimension table which doesn't sound too horrid for performance but we won't know for sure till we test some prototype queries. The other concern with having separate time zone keys in the fact table is that the query has to join the dimension table to a different column based on the desired timezone, perhaps this is something that SSAS takes care of for you, I'm not sure.

thanks for any thoughts, -Matt

share|improve this question
add comment

2 Answers

up vote 2 down vote accepted

Having the date and time separate will allow you to do aggregates by time much easily. for eg: if you want to run a query to find what time period of the day is most busy. This is much easily performed using a separate time dimension.

Also, you should just have one timekey. Decide on either GMT/ EST time - then use this in the fact table. If you need to run reports based off the other timezone, just convert it in your application or query.

share|improve this answer
    
Ok, that makes sense, the users can't group the data then based on their timezone, but that's probably something we could live without in order to simplify the design. –  Matt Palmerlee Nov 18 '11 at 22:09
add comment

Just a follow up on how we decided to implement our DataWarehouse to support multiple Time Zones and be as efficient as possible: We chose to create a table of time zones (id, name, etc...) as well as a "Time Zone bridge" table that looks like this:

time_zone_bridge
---------------
date_key_utc
time_key_utc
timezone_id
date_key_local
time_key_local

This way we can keep our normal date and time dimension tables small, all our facts link to the UTC date/time keys, then if we need to report/group by a different time zone we just have to join through the time zone bridge table and link the local date/time keys back to the date and time dimension tables. We populate our time zone bridge table using C# code invoked from SSIS since this was much less complicated than doing TZ stuff from SqlServer directly.

share|improve this answer
    
I also thinking your solution is probably make the most sense without getting into anything too comlicated. I am testing my DW using a timeZone table and TimeZoneBridge similar to yours. It also has TimeDimension and DateDimension tables. I created a clustered index on the date_key_local, time_key_local, and the timezone_id, so that translating local time to UTC time using TimeZoneBridge would be fast. –  dsum Jul 15 '12 at 16:55
    
Our primary clustered key for the bridge table is on the utc date/time columns + the timezone id (if I remember correctly), since all the fact tables time keys will be in utc, you'll be joining to the bridge through the utc keys + tz id, it might work better to have the clustered index on those. Do what makes sense for your needs though. I'm glad my answer helped someone, I think it is a good approach and from all of our testing, it is still reasonably fast, just be careful when it comes to the WHERE clause: filter out the date ranges you want as early as possible in your queries. –  Matt Palmerlee Jul 17 '12 at 1:52
    
Does this only contain whole dates? Or if you have 86000 "date/time key" values in your fact table, the bridge table will have 86000 rows * n supported time zones, and that's just for that one day? –  Aaron Bertrand Feb 13 at 6:35
    
perhaps you can add the exact table definition you have, so readers can see the primary,unique constraints. –  ypercube Feb 13 at 9:02
    
@AaronBertrand it depends on the grain (or granularity you choose) to track your data at, in our case we only needed 15 minute granularity in our fact tables so it's only 4 * 24 = 96 records per day per timezone we wanted to support, which is completely reasonable. –  Matt Palmerlee Feb 14 at 7:52
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.