create a time dimention table

[acool]

Hello everyone,

I am going to build a time dimention table that covers all dates from 1990 to 2030. The table has a time key, year, month, and day. how do I do it?

[mhester]

ACOOL,

Most projects I have been on have two (2) tables to represent date and time (DIM_CAL and DIM_TIME). Both serve different purposes. You could certainly create one table that has both date and time, although that is not what I have witnessed as a general practice.

You can build these tables using either a DataStage job, sql or whatever else you want. These tables are populated once in each DB and then are not usually touched.

The methods for accomplishing this are varied and there can usually be many ways to accomplish this task. I believe (could be wrong) that Kimball addresses this in his books and may even have a sample on the CD in the back of the book (Ken Bland would know if this is correct )

Our DIM_TIME table currently has 86404 records or 1 for each second of the day and some for invalid times when encountered. To be a truly useful table you may/may not want to include the following columns -

Number columns -

Code: Select all

    Time_sid
    Sec_in_day_num
    Sec_in_hr_num
    Sec_in_half_hr_num
    Sec_in_qtr_hr_num
    Sec_in_min_num
    Min_sid
    Min_in_qtr_hr_num
    Min_in_half_hr_num
    Min_in_hr_num
    Min_in_day_num
    Qtr_hr_sid
    Qtr_hr_in_half_hr_num
    Qtr_hr_in_hr_num
    Qtr_hr_in_day_num
    Half_hr_sid
    Half_hr_in_hr_num
    Half_hr_in_day_num
    Hr_sid
    Hr_in_day_num
    Hr_in_half_day_num

And some indicator columns like -

Code: Select all

    Min_beg_ind
    Min_end_ind
    Qtr_hr_beg_ind
    Qtr_hr_end_ind
    Half_hr_beg_ind
    Half_hr_end_ind
    Hr_beg_ind
    Hr_end_ind
    Day_beg_ind
    Day_end_ind

And maybe a few others like -

Code: Select all

    Time_tm
    Time_24hr_nm
    Time_12hr_nm
    Am_pm_cd

I hope this helps you get started, but I cannot/will not send you a job, code or whatever to do this for you.

Regards,

Michael Hester

[ray.wurlod]

You've elected to have a very limited time dimension table with day as its granularity.
All you need to do is to generate a file with the base dates (in external or internal format), then run this through a Transformer stage to generate the key and the hierarchy (week, month, quarter, calendar year, financial year, whatever).
You could write a before-job subroutine to generate the source file of dates.