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
    
This question also exists in Stack Overflow: stackoverflow.com/questions/2507289/…. –  Jon of All Trades Jul 29 at 20:34

3 Answers 3

up vote 3 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
    
@MattPalmerlee: Users can group by time zone if you give it to them. I'd typically include it in the Geography table, but if none applies you can add it as an attribute of your fact table. –  Jon of All Trades Jul 29 at 20:35

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
1  
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
1  
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

I've seen the idea of a warehouse using a combined DateTime dimension rejected, but I haven't seen a really clear reason why. Simplifying slightly, here's the fact table I'm building right now:

Transactions
(
...
CreatedDateTimeSK         INT NOT NULL,  -- Four bytes per date...
AuthorizedDateTimeSK      INT NOT NULL,
BatchSubmittedDateTimeSK  INT NOT NULL,
BatchApprovedDateTimeSK   INT NOT NULL,
SettlementDateTimeSK      INT NOT NULL,
LocalTimeZoneSK           TINYINT NOT NULL  -- ...plus one byte for the time zone
)

The DateTime fields join to a DateTime table:

DateTimes
(
DateTimeSK   INT NOT NULL PRIMARY KEY,
SQLDate      DATE NOT NULL,
SQLDateTime  DATETIME2(0) NOT NULL,
Year         SMALLINT NOT NULL,
Month        TINYINT NOT NULL,
Day          TINYINT NOT NULL,
Hour         TINYINT NOT NULL,
Minute       TINYINT NOT NULL CHECK (Minute IN (0, 30)),
...
)

This is at a resolution of half-hours, so there are 48 records per day, 350,400 in 20 years - quite manageable.

Event date/times are translated to UTC when stored, but with the LocalTimeZoneSK field and a bridge table we can easily join to get local time:

TimeZoneBridge
(
DateTimeSK       INT NOT NULL,
TimeZoneSK       TINYINT NOT NULL,
PRIMARY KEY (DateTimeSK, TimeZoneSK),
LocalDateTimeSK  INT NOT NULL
)

To get transactions created today, UTC time:

SELECT COUNT(*)
FROM Transactions AS T
  INNER JOIN DateTimes AS CD ON T.CreatedDateTimeSK = CD.DateTimeSK
WHERE CD.SQLDate = '2014-08-22'

To get transactions created today, in local time for the transaction:

SELECT COUNT(*)
FROM Transactions AS T
  INNER JOIN TimeZoneBridge AS TZB ON T.CreatedDateTimeSK = TZB.DateTimeSK AND T.TimeZoneSK = TZB.TimeZoneSK
  INNER JOIN DateTimes AS CD ON TZB.LocalDateTimeSK = CD.DateTimeSK
WHERE CD.SQLDate = '2014-08-22'

You may be tempted to simplify things by replacing the TimeZoneSK with a REAL offset (e.g., -5.0 for U.S. Central Daylight Time), but this will break down if some date/times for a fact record are in Daylight Saving Time and some are not.

If the events for a fact record can happen in different time zones, like a shipment or a flight, then you need a time zone field for each date, and you're up to five bytes per date.

share|improve this answer
    
It's a creative approach. However, as you say you'll only have 350,400 rows in your combined datetime dim table, if you start changing the grain to finer resolution, you'll quickly get into the millions of records. If you choose to have a separate date dimension than time dimension you only have 48 rows in your time dimension table and only 365 rows per year in your date dimension table (or 7300 rows in 20 years). Your fact table then simply has a column for date_key and time_key. This also makes it more flexible if you have some fact tables that only require date granularity. –  Matt Palmerlee Aug 24 at 0:53
    
A million rows in a dimension doesn't concern me - the data is only changed once a decade, and a covering index on the PK and two or three most-used fields will take up a trivial amount of server RAM. However, adding half a dozen SMALLINTs to a billion-row fact table is 12 GB plus overhead, and now you're talking real money. For dates that only need to store the date, you can of course point them to the "12:00 AM" record for the appropriate date. –  Jon of All Trades Aug 25 at 14:23

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.