Dimensional Data Modeling - Date Dimension

Star Schema


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.

Example Date Dimension



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.

You can also generate one with sql



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

Discover More
Star Schema
Dimensional Data Modeling - Dimension (Perspective)

A dimension is a part of a dimensional schema and provide the basis for analyzing data through slicing and dicing A dimension is just a set of descriptif attribute that are generally grouped: in a...
Data System Architecture
Dimensional Data Operation - Drill Down / Up

Drilling down or up is a data navigation term along hierarchy levels of data ranging: from the most summarized (up) to the most detailed (down) Going up a level in the hierarchy is called drilling...
Event Conceptual Model
Event (Timed Measure|Action)

An event is a timed observed physical reality described by: space (location) participant. The observations describing the event are defined by the nature or physics of the observable, the observation...

Share this page:
Follow us:
Task Runner