2

In Chapter 2 Retail Sales of Ralph Kimball's The Data Warehouse Toolkit, the author describes a date dimension with three fields which appear totally alien to me: 

Date Key (PK)
Date
Full Date Description
Day of Week
**Day Number in Epoch**
**Week Number in Epoch**
**Month Number in Epoch**
Day Number in Calendar Month
Day Number in Calendar Year
...

I understand that an Epoch is the "instance in time chosen as a particular era" and that the current standard epoch for J2000.0 starts on January 1, 2000. But I don't see how these numbers would be helpful in a date dimension or a cube used for getting information about retail sales.

  1. Are numbers in Epoch typically used in Date Dimensions?
  2. What Epoch would be used for a modern day business?
  3. How would a user slice data based on Epoch and why?
| improve this question | |
2

Absolute numbers for months and quarters allow them to sort correctly when used in a query tool. A 'month of year' value is only useful for sorting within a year, but an absolute month value that is in order will sort a list of months correctly across years. This is very helpful to support (for example) a report over a rolling 12 month period.

Keys for week, month or quarter (the year itself is inherently ordinal and suffices for this on years) also allow attributes such as day of month to be keyed to a logical key. Although the dimension table itself may be flat, many tools (OLAP servers, for example) will still impose a logical hierarchy within the data and require a key that is unique at its level to do this.

| improve this answer | |
2

Epoch is also a synonym for a date or time. Occasionally used in database models as an alternative to the ubiquitous 'timestamp' as a column name, the two are essentially interchangeable.

Edit: Looking at your list with fresh eyes (and without getting the book from the cupboard it's hiding in) I expect an epoch in this instance would be a period such as a financial quarter. For example, if a the quarter ran from 1st April to 30th June, 'Day number in Epoch' for 1st May would be 32.

| improve this answer | |
  • Interesting. In the example in the book, the last column in the date dimension is a SQL Date Stamp. So if Epoch means the same thing, I'm not sure why both would be included. But if Epoch is a timestamp, then what would "Day Number in Epoch" represent? – 8kb Feb 17 '12 at 1:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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