One subject area of our DW is about tasks of several projects. These tasks have a total of about 30 dates to report on. These monthly and yearly reports only need to know about the exact dates and the duration of the tasks. What is your advice for the physical implementation? Do I store the 30 dates as such in the fact table or do I create one foreign key per date in the fact table? My question can also be summed up as when do we store a date in the fact table and when do we use a foreign key to a date dimension?


Clay Rehm’s Answer: If you don’t think you will never need more detail than a day, such as a specific time in a day, then there is no reason to put the date in a dimension.

However, you will want a Date dimension table so you can roll up the day to week, month, quarter, year, etc. And depending on how you will use the date in your reporting, you should track if the day is a holiday. If you do this, you must also consider international holidays.

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