Dimensional Modeling - Date Dimension

> (OLAP|Analytic) > Dimensional Modeling - Dimensional Schemas

1 - About

The purpose of a date dimension is to support interpretation of the date. The attributes in the date dimension provide business meaning, and most likely, multiple business meanings of the date.

At minimum, a business has both:

  • a temporal interpretation
  • and a fiscal interpretation.

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.

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 determine these non-standard calendar calculation in a query, we should look them up in a date dimension table.

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.

3 - Surrogate key as a Number on all level


  • 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.

4 - Hierarchy

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

5 - Documentation / Reference