Dimensional Data Modeling - Date Dimension


The purpose of a date dimension is to support interpretation of the date.

Different areas of the business will have different views of the calendar. A date dimension is critical in supporting such interpretations.

Rather than attempting to calculate all non-standard calendar attribute in a query, the date dimension permits to look them up.



At minimum, a business has both:

  • a temporal interpretation
  • and a fiscal interpretation.


The attributes in the date dimension provide business meaning, and most likely, multiple business meanings of the date.

It can contain:

  • standardized labels for reporting,
  • offsets for easily identifying yesterday, last month, same month last year and so on,

All this attributes without a date dimension need to be specify :

  • by the user
  • or by an expression in the report.

Example of attributes:

  • Holidays,
  • work days,
  • fiscal periods,
  • week numbers,
  • last day of month flags.


The date dimension doesn’t have a data source and one good way to generate the calendar date dimension is to spend an afternoon with a spreadsheet and build it by hand. Ten years worth of days is less than 4000 rows.



The hierarchy associated with the date dimension follows the following high interpretation:

Level Time Organization
Strategic Long term Board
Tactical Mid term Business Unit
Operational Short term Business Process


A Surrogate key may be used as a Number on all level to:

  • handle the date order of for instance the week level
  • handle “Not Applicable” type rows
  • be able to do a simple arithmetic operation as I want to see only the last 4 weeks.

Documentation / Reference

Powered by ComboStrap