Combined Date Time dimension vs. Separate Day and Time dimensions and timezones

[mpalmerlee]

We are just starting a dimensional 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

[gerardnico]

Hello,

In Oracle Business Intelligence, Time zone is a parameter of the user.

No need to fill special columns in your model because this is a calculation between the time zone of the user and the time zone of the Oracle BI Server.

You don't have this functionality in your reporting system ?

Cheer
Nico

[mpalmerlee]

We have used Crystal Reports in the past to render reports for our users, but we might change reporting solutions now that we are moving to a data warehouse. My question was more about designing the date time dimension table(s). Either you need to store timezone data in your tables somehow or your reporting solution will need to convert dates/times to the proper timezone for display and filtering right? Does Oracle BI server do this translation for you?

[BoxesAndLines]

The vast majority of dates do not need time included, so carrying time on your date dimension is needless overhead. Additionally, 10 years of history will blow out your date/time dimension to 1.3M rows. A separate time dimension will always have 131K rows.

[gerardnico]

Yes. He does.

Each account can indicate its time zone:
http://gerardnico.com/wiki/_detail/dat/obiee/obiee_presentation_service_system_variable.jpg?id=dat%3Aobiee%3Apresentation_variable_system
This setting will make the adjustment for differences between the user's time zone and that of the BI Server, and will also apply the seasonal time changes.

It's much more a parameter and then a function than something that must come in the database. Otherwise, you have to create by time zone:
* one report ,
* one column, .
* ...

The best for me is to make a calculation by passing the time zone as parameter.

Cheers
Nico

[mpalmerlee]

Ok, thanks for the responses. This is how I was imagining how to design the structure best. I was confused while reading some of the Kimball books because he recommends having at least two time zones referenced in the fact table, one for GMT and one in the Local time where the event occurred. That way you can easily and efficiently do aggregated queries based on that timezone (i.e. give me the quantity and value of purchases made between 9:00 and 10:00 Local time).

[ngalemmo]

We have used Crystal Reports in the past to render reports for our users, but we might change reporting solutions now that we are moving to a data warehouse. My question was more about designing the date time dimension table(s). Either you need to store timezone data in your tables somehow or your reporting solution will need to convert dates/times to the proper timezone for display and filtering right? Does Oracle BI server do this translation for you?

The only time you place time in a dimension is when time means something special to the business, where they have business attributes related to the time of day. Otherwise just store the time as-is and forget about it. Most databases have extended timestamp support that includes time zones. These usually store time using GMT and stores the local timezone and offset information. There are usually functions to convert between zones.

As for a date/time dimension, you create such a dimension only when the intersection of date and time of day have significant meaning to the business. This is fairly unusual.

[mpalmerlee]

Thanks for that, our application has pretty crucial time of day requirements for reporting because it is a high-traffic internet application and our users want to be able to see trends by time of day (i.e. how much traffic between noon and 1:00 over a week), that is why we were going to do a time of day dimension. We'll still store the exact datetime of each event in our fact table but were thinking of also referencing a time dimension in order to do this sort of analysis.

[ngalemmo]

Thanks for that, our application has pretty crucial time of day requirements for reporting because it is a high-traffic internet application and our users want to be able to see trends by time of day (i.e. how much traffic between noon and 1:00 over a week), that is why we were going to do a time of day dimension. We'll still store the exact datetime of each event in our fact table but were thinking of also referencing a time dimension in order to do this sort of analysis.

If the time has no attributes other than the time, there isn't any need for a dimension table. Storing the time in GMT should be sufficient to do the types of reporting you need. You could also store a small integer value which is the number of minutes past midnight to make it easy to calculate time intervals over the day.

[BoxesAndLines]

Sounds like a time band dimension will provide the type of analysis you are looking for.

[mpalmerlee]

Sounds like a time band dimension will provide the type of analysis you are looking for.

By Time band dimension you are thinking something like this?

IntervalName (i.e. year, quarter, month, hour, quarterhour, etc...)
StartTime
EndTime


Or perhaps you would have start time and length/duration instead of end time?

thanks,
-Matt

[BoxesAndLines]

The first one. Then you can build a nice hierarchy on top of it. Early morning, late afternoon, early evening, etc.

[mpalmerlee]

I agree, problem with start and end times is that you would want them to be inclusive on the start date and exclusive on the end date so you just have to be careful to stay away from the 'BETWEEN' clause and always use >= startdate and < enddate to find the right band to place things in, not a huge deal if everyone sticks to the rules.

-Matt

[Sponsored content]