How to build Time dimension table
Moderators: chulett, rschirm, roy
How to build Time dimension table
Can anyone please tell that is there any kind of Time generator in DataStage to create time dimesion table. For example is there any place in DataStage where I can defined start and end dates and the frequency by which system can generate time dimension for me.
Help will be highly appreciated.
Thankyou very much
Help will be highly appreciated.
Thankyou very much
Re: How to build Time dimension table
The best tool to generate time dimension is Excel.
... or, use a loop and then Iconv / Oconv.
... or, use a loop and then Iconv / Oconv.
Perwezakh wrote:Can anyone please tell that is there any kind of Time generator in DataStage to create time dimesion table. For example is there any place in DataStage where I can defined start and end dates and the frequency by which system can generate time dimension for me.
Help will be highly appreciated.
Thankyou very much
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
You can use a Transformer stage as a row generator. Create a stage variable initialized to the internal form of the starting date (the initialization expression can use Iconv() and the starting date can be a job parameter).
Increment the stage variable by 1.
Output link constraint is that you are less than (the internal form of) your end date.
Go crazy on the output column derivations to generate whatever forms of time dimension you want, for example:
Increment the stage variable by 1.
Output link constraint is that you are less than (the internal form of) your end date.
Go crazy on the output column derivations to generate whatever forms of time dimension you want, for example:
- Weekday name: Oconv(svTheDate, "DWA")
SimpleQuarter: Oconv(svTheDate, "DQ")
FullQuarter: QUARTER.TAG(svTheDate)
ChineseHoroscopeYear: Oconv(svTheDate, "DYA")
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.
Here is how we did a TIME_DIM. You could easily change the "MTHs" into "D4-" to do a DAY_DIM table. The concept is to do all this in job control and write to a sequencial file.
Code: Select all
* =================================================================
* Job: ExtractTimeDimStg
* =================================================================
* Description:
* Generic job to build a time dimension for each second in a day.
* =================================================================
* Created by: Kim Duke June 6, 2004
* =================================================================
JobName = "ExtractTimeDimStg"
SeqFileName = FilePath : "Staging/TimeDimStg.txt"
openseq SeqFileName to SeqFilePtr else
* =================================================================
* create table if it does not exist
* =================================================================
If Status() <> 0
Then
Call DSLogFatal("Error: Unable to open " : SeqFileName, JobName)
goto TheEnd
End
end
* =================================================================
* loop through each minute in day starting with 0 minutes from midnight
* =================================================================
SecsInDay = 60 * 60 * 24
* kim test SecsInDay = 70
* midnight = iconv('12:00pm', 'MTH')
midnight = 0
for i=0 to SecsInDay - 1
newTime = midnight + (i)
TimeWoSecs = oconv(newTime, 'MTH')
TimeWSecs = oconv(newTime, 'MTHS')
OffsetSeconds = i
Time24 = oconv(newTime, 'MTS')
HourOut = field(Time24, ':', 1)
MinutesOut = field(Time24, ':', 2)
SecondsOut = field(Time24, ':', 3)
AmPmInd = right(TimeWoSecs, 2)
TimeRec = i + 1
TimeRec := ',' : TimeWoSecs
TimeRec := ',' : TimeWSecs
TimeRec := ',' : HourOut
TimeRec := ',' : MinutesOut
TimeRec := ',' : SecondsOut
TimeRec := ',' : OffsetSeconds
TimeRec := ',' : AmPmInd
TimeRec := ',' : Time24
writeseq TimeRec on SeqFilePtr else
Call DSLogFatal("Error: Unable to write to " : SeqFileName, JobName)
goto TheEnd
end
next i
Call DSLogInfo(SecsInDay:" rows written to ":SeqFileName, JobName : "JobControl")
* =================================================================
* exit program here
* =================================================================
TheEnd:
Mamu Kim
-
- Participant
- Posts: 36
- Joined: Thu Sep 01, 2005 5:44 am
- Location: Canada
Time Dim
Hi,kduke wrote:Here is how we did a TIME_DIM. You could easily change the "MTHs" into "D4-" to do a DAY_DIM table. The concept is to do all this in job control and write to a sequencial file.
Code: Select all
* ================================================================= * Job: ExtractTimeDimStg * ================================================================= * Description: * Generic job to build a time dimension for each second in a day. * ================================================================= * Created by: Kim Duke June 6, 2004 * ================================================================= JobName = "ExtractTimeDimStg" SeqFileName = FilePath : "Staging/TimeDimStg.txt" openseq SeqFileName to SeqFilePtr else * ================================================================= * create table if it does not exist * ================================================================= If Status() <> 0 Then Call DSLogFatal("Error: Unable to open " : SeqFileName, JobName) goto TheEnd End end * ================================================================= * loop through each minute in day starting with 0 minutes from midnight * ================================================================= SecsInDay = 60 * 60 * 24 * kim test SecsInDay = 70 * midnight = iconv('12:00pm', 'MTH') midnight = 0 for i=0 to SecsInDay - 1 newTime = midnight + (i) TimeWoSecs = oconv(newTime, 'MTH') TimeWSecs = oconv(newTime, 'MTHS') OffsetSeconds = i Time24 = oconv(newTime, 'MTS') HourOut = field(Time24, ':', 1) MinutesOut = field(Time24, ':', 2) SecondsOut = field(Time24, ':', 3) AmPmInd = right(TimeWoSecs, 2) TimeRec = i + 1 TimeRec := ',' : TimeWoSecs TimeRec := ',' : TimeWSecs TimeRec := ',' : HourOut TimeRec := ',' : MinutesOut TimeRec := ',' : SecondsOut TimeRec := ',' : OffsetSeconds TimeRec := ',' : AmPmInd TimeRec := ',' : Time24 writeseq TimeRec on SeqFilePtr else Call DSLogFatal("Error: Unable to write to " : SeqFileName, JobName) goto TheEnd end next i Call DSLogInfo(SecsInDay:" rows written to ":SeqFileName, JobName : "JobControl") * ================================================================= * exit program here * ================================================================= TheEnd:
Please tell me what to do if i have to empty the sequential file first and then load the data?
Thanks,
Rony
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Either delete the file first (so that OpenSeq takes its Else clause) or include a WeofSeq statement in the Then clause of OpenSeq and change the logic so that the loop is executed in either case.
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.
-
- Participant
- Posts: 36
- Joined: Thu Sep 01, 2005 5:44 am
- Location: Canada
Yes this code overwrites the file every time we run it. But the problem comes when in the first run if you have 100 records and in the 2nd run if you change the range and the no:of records are only say 10. Then after the 10 records the remaining records will be of the first run (i.e 11 -100). Hence it will be a mix of both which we don't want to happen.kduke wrote:This code overwrites the text file every time you run it. Should not be a problem.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Add a THEN clause to the OPENSEQ statement and execute WEOFSEQ therein.
Move the main loop outside the scope of the OPENSEQ statement as a result (or duplicate it within the scope of the THEN clause).
Move the main loop outside the scope of the OPENSEQ statement as a result (or duplicate it within the scope of the THEN clause).
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.
-
- Participant
- Posts: 36
- Joined: Thu Sep 01, 2005 5:44 am
- Location: Canada