Date Dimension Creation

I've read several of the Kimball Group Books and each time I read about a Date Dimension it is recommended that it be built in a Spreadsheet. Is this still the accepted method?

I'm generating mine in SQL Script and I wondered if most were still using spreadsheets and exporting the data to their database product of choice.

I searched for references to it in the forum but came up empty.

Here's the thing... a date dimension is the type of thing you create once. And maybe you need to add some rows every 5 or 10 years. Do it the simplest way you can. If you happen to be adept at Excel, it's really easy to throw something together. If you prefer some other means (stored procedure, perhaps) fine. Choose a method that makes sense... there is no point in overengineering something like this.

Thank you Sir.

The SQL logic to determine holidays, etc, can be challenging. I would hate to have to code it from scratch. How you do it matters little.

I agree that it is complex. However, I took the code from this stored procedure and heavily modified it to include our special fiscal periods and "work weeks". We are on a 4-4-5 period structure.

It includes all of his regular calendar attributes, which are exceptionally robust, and adds new fiscal fields which are derived from the OLTP source.

On my test server, the whole process requires less than 30 seconds to complete.

That would make the decision easy for me. :^)

I built the base table in Excel and then loaded it into Access that attaches to a REFERENCE database that keeps all of my reference information in SQL. I use Access to keep it updated.

I recommend keeping the components of your date dimension in 3nf. The Date Dimension can contain so many different elements - Names of Months, abbreviation of Month, Names of Days, Abbreviation of Days, Year and Month as Jan 2010 or as 201001. It can contain the quarter, holidays, a Week Day Indicator, Business Day, promotional period, Bad Weather Days (for when a facility is closed because of snow).... Just a myriad of information. My date dimension has the date in different forms to match the various source systems.

it is complex,though reading books helps a lot.

I wrote a simple VB .NET utility to generate mine. I was able to build the logic to generate attributes for a 13-period fiscal calendar (including handling the occasional 53-week year) into the program. It spits out a text file of SQL INSERT statements which I execute to populate the date dimension.