Dimensional Data Modeling - Date Dimension

1 - About

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.

3 - Structure

3.1 - Representation

At minimum, a business has both:

  • a temporal interpretation
  • and a fiscal interpretation.

3.2 - Attribute

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.

4 - Generation

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.

5 - Hierarchy

5.1 - Interpretation

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

6 - Level

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.

7 - Documentation / Reference


Data Science
Data Analysis
Statistics
Data Science
Linear Algebra Mathematics
Trigonometry

Powered by ComboStrap