Dimensional Data Modeling - Date Dimension
Table of Contents
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.
Articles Related
Structure
Representation
At minimum, a business has both:
- a temporal interpretation
- and a fiscal interpretation.
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.
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.
Hierarchy
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 |
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.