Dimensional Data Modeling - Date Dimension

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.

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.

The hierarchy associated with the date dimension follows the following high interpretation:

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.

• ngalemmo in DATE OR DATE KEYS IN FACT TABLES