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?
create a time dimention table
Moderators: chulett, rschirm, roy
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 -
And some indicator columns like -
And maybe a few others like -
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
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
![Smile :)](./images/smilies/icon_smile.gif)
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
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
Code: Select all
Time_tm
Time_24hr_nm
Time_12hr_nm
Am_pm_cd
Regards,
Michael Hester
Mike Hester
mhester@petra-ps.com
mhester@petra-ps.com
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.