I am looking for a time dimension table structure. I have a date dimension and all I am looking for is standard time dimension for my star schema.


Chuck Kelley’s Answer: I don’t think that there is a standard time dimension. It all depends on your requirements. At the lowest level, you should have a date_time dimension that has year, quarter, month, week, day, hour, minute, second, 1/10 of a second. Then you need to add appropriate stuff for your user requirements. They include things like Is it a holiday, fiscal vs. calendar, accounting periods, sorting requirements, etc.

I generally write a SQL procedure/script that creates the date/time dimension for some period of time – Maybe two years back and 10 years forward. Then after nine years, you may have to run it again to create another 10 years worth of data.

There are many things that can be part of a date/time dimension, but it should be driven by user requirements.

Les Barbusinski’s Answer: I know what your dilemma is. You’ve tried creating a dimensional hierarchy with Hour, Minute and Second elements, but balked when you realized that a) an Hour is useless without being attached to a Date, b) the Hour and Date depends on the Time Zone, c) the dimension should map local Times to GMT (a.k.a. Universal) time, and d) there are 86,400 seconds in a day (i.e., your Time dimension will be H-U-G-E).

As Ralph Kimball points out on page 240 of The Data Warehouse Toolkit and page 245 of The Data Warehouse Lifecycle Toolkit, you really don’t need a Time dimension unless you’re tracking time intervals for the purpose of aggregating metrics to those intervals (e.g., measuring production totals by each eight-hour shift). Otherwise, treat time-of-day values as metrics in a Fact table rather than as a Dimension. For comparison purposes, you may want to store two versions of a timestamp in your Fact tables: Local time and GMT time. Hope this helps.

Register or login for access to this item and much more

All Information Management content is archived after seven days.

Community members receive:
  • All recent and archived articles
  • Conference offers and updates
  • A full menu of enewsletter options
  • Web seminars, white papers, ebooks

Don't have an account? Register for Free Unlimited Access