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.
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.
You can also generate one with sql
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.