Date Dimension

I have been working on a Data Warehouse for one of our clients and it involved among other things the creation of a date dimension table. The corporation is using a 5-4-4 fiscal calendar with a 52-53 week fiscal year.  The calendar divides a year into 4 quarters. Each quarter has 13 weeks which are grouped into one 5-week "month" and two 4-weeks "months". The grouping of 13 weeks may be set up as 5-4-4 weeks, 4-4-5, or 4-5-4 weeks.

Its major advantage of the 5-4-4 fiscal calendar over a regular calendar is that the end date of the period is always the same day of the week which is useful for shift or manufacturing planning. The disadvantage of this calendar is that it has 364 days (7 days * 52 weeks) and requires a leap year which can be every 5 or 6 years, depending on the choice of the organization.

This calendar is used by companies that desire that their fiscal year always end on the same day of the week which can be any day of the week, based on their choice.

In building this calendar, I started out with the work of the Date Dimension Kit by Don McMunn which is licensed under a Creative Commons Attribution 3.0 License.
You can find his work at www.ipcdesigns.com.

The specific requirements of our client necessitated a change in style and in fiscal year design (Don McMunn's work is based on the US Federal Calendar with a fiscal year from October through September).

Since most of the work is really tedious, I thought to provide a modified starter kit here as well and my thanks are going out to Don for his initial work. Before you are going to use it, I would suggest turning the identity based PKs for the date table into integer PKs based on the integer representation of the date (i.e. 20150201 for February 1, 2015). There are pros and cons to either approach such as now you can't just add 3days by adding 3 to the identity.

 The dimension table kit consists of 4 files (currently) which can be found under:

Enjoy!