populating date dimension

dnat · Post by **dnat** » Thu Dec 31, 2009 12:15 am

Hi,

Is there any predefined script or function or routine to populate the date dimension table. or how is it normally done?

I need the following in Date_dim

Date
Day number
Calender day number
Month Number.

Thanks

ray.wurlod · Post by **ray.wurlod** » Thu Dec 31, 2009 12:24 am

Give a bit more thought to what might be useful things to have, given your day granularity. For example:
first day of current month
last day of current month
year number
financial year
weekday name
weekday number in week
ordinal day number in calendar year
ordinal day number in financial year
season
workday/nonworkday

Use a Row Generator stage to generate dates from a start date (the Epoch property) for as many days as you require in the dimension (the Limit property). Use appropriate functions in a Transformer stage to generate the other fields based on your requirements (for example DateToMonth).

chulett · Post by **chulett** » Thu Dec 31, 2009 7:20 am

From what I've seen, 'normally' it's either loaded from a spreadsheet or there's a stored procedure of some kind that generates the data.

kduke · Post by **kduke** » Thu Dec 31, 2009 8:45 am

I always use YYYYMMDD as the key. This works really well.

dnat · Post by **dnat** » Wed Jan 06, 2010 4:48 am

Thanks for your responses..I am trying to get the information whether it is stored procedure or sql scripts, but havent got anything which can be re-used..if anyone has the info, can you please pass it on to me..

chulett · Post by **chulett** » Wed Jan 06, 2010 9:43 am

The only people that will have 'that info' are people working at your company.

kduke · Post by **kduke** » Wed Jan 06, 2010 11:20 am

We did this in the past with SQL. We generated the SQL using BASIC routines.