Time Dimension

The dimension of time is such a core and critical concept in dimensional design and analytics that sometimes we forget some of the subtleties and nuances involved. Since practically every large table in the data warehouse contains a reference to the time dimension, any time or calendar change may have substantial repercussions. Here are the things to consider:

Time Grain

Most business applications require daily grain. There are often requirements that involve time-of-day grain, but before you decide to build a time dimension at the hour or minute level consider the problem and its implications carefully.

Identity

Should we use a surrogate identifier or the date itself? Proponents of a surrogate ID see benefit to being able to load facts for undecipherable or invalid dates and resolving the actual date later. VDM uses a real date for all its advantages, from avoiding the need for lookups to using date arithmetic in organizing and manipulating facts. The argument about preserving facts with invalid dates we consider weak, both in terms of integrity and practical utility

Calendar Structure

There are many options in structuring the levels of a calendar. Gregorian and certain fiscal/tax calendars follow a non-hierarchical structure, whereby weeks don't neatly fit into months or years. The 4-4-5 structure preserve the hierarchical relationship between weeks and periods and is better for financial reporting and analytics. VDM uses an algorithm to generate time dimensions based on the quarter structure (4-4-5, 5-4-4 etc) that populates natural (Gregorian) or fiscal periods and special (shifted) variants appropriate for some retail applications using the following simple seed information:

• The week-ending day (typically Saturday or Sunday)
• A method to identify which years are 53-week long by one of these methods:
  • By explicit listing of 53-week years
  • Last week-ending of the month
  • Week-ending nearest the end of the month
• Last month of the fiscal year

Here is a link about calendars in general, another about fiscal calendars, and one on 4-4-5 Calendars

Year-to-Year Correspondence

what day from last year corresponds to today? What week from last year corresponds to this week? Which period and quarter correspond to this period or quarter? If last year had 53 weeks then the answer is not as simple. Here is a little more in-depth discussion on the pros and cons.