DSXchange

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

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).

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.

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

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

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

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