Time for more dates

For most data warehouses the time (date) dimension appears, or at least should appear, in every query. I can think of a few DWs where time is not always used; one example being an asset (infrastructure) maintenance reporting system for a utility company, each pipe, inspection chamber, valve, or whatever of a water company is recorded and people legitimately query by asset or asset type without reference to date.

So what goes into a date dimension? The simple answer (and the unhelpful one) is levels and attributes.
The choice of the lowest level is dictated by how often you capture fact data and the granularity you report, there is little point having hour of the day as your lowest level if you only capture sales by the week. Conversely, capturing sales by the second may not warrant such a degree of granularity in the reporting structures of a data warehouse; besides building a denormalised table to support a one second granularity (86400 rows per day) may not be the most productive use of time or database space – it may be possible to come up with some function to denormalise any give DATE data type, but that probably defeat a lot of the database functionality around query rewrite. The other levels often drop out:

Second of Day | Minute of Day | Hour of Day | Day | Fiscal week | Fiscal period | Calendar Month | Fiscal quarter | Calendar Quarter | Fiscal Year | Calendar Year

Attributes on the time dimension fall into two groups, those that are derived from date, such as April 14 2006 is a FRIDAY and those that are based on an external calendar such as April 14 2006 is not a working day (a public holiday in the UK) Typical attributes may include day_of_week, is_working_day, trading_season.

In the next part of this piece I will look at external date related facts.