Datawarehouse Design: Combined Date Time dimension vs. Separate Day and Time dimensions and timezones

Question

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

Answer 1

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.

Answer 2

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.

Answer 3

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.