Date Dimension Creation

View previous topic View next topic Go down

Date Dimension Creation

Post  DavidStein on Tue Sep 07, 2010 9:34 am

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.

DavidStein

Posts : 24
Join date : 2010-04-01

View user profile

Back to top Go down

Re: Date Dimension Creation

Post  ngalemmo on Tue Sep 07, 2010 1:27 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Date Dimension Creation

Post  DavidStein on Tue Sep 07, 2010 4:31 pm

Thank you Sir.

DavidStein

Posts : 24
Join date : 2010-04-01

View user profile

Back to top Go down

Re: Date Dimension Creation

Post  BoxesAndLines on Tue Sep 07, 2010 8:02 pm

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.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Date Dimension Creation

Post  DavidStein on Wed Sep 08, 2010 9:15 am

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.

DavidStein

Posts : 24
Join date : 2010-04-01

View user profile

Back to top Go down

Re: Date Dimension Creation

Post  BoxesAndLines on Wed Sep 08, 2010 10:56 am

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

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Date Dimension Creation

Post  Jeff Smith on Wed Sep 08, 2010 1:34 pm

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.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Date Dimension Creation

Post  reenfoo on Wed Sep 08, 2010 10:47 pm

it is complex,though reading books helps a lot.


reenfoo

Posts : 1
Join date : 2010-09-08

View user profile

Back to top Go down

Re: Date Dimension Creation

Post  VHF on Wed Sep 15, 2010 12:08 pm

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.

VHF

Posts : 236
Join date : 2009-04-28
Location : Wisconsin, US

View user profile

Back to top Go down

Re: Date Dimension Creation

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum