How to build Time dimension table

[Perwezakh]

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

[mdan]

The best tool to generate time dimension is Excel.
... or, use a loop and then Iconv / Oconv.

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

[ray.wurlod]

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:

• Weekday name: Oconv(svTheDate, "DWA")
  
  SimpleQuarter: Oconv(svTheDate, "DQ")
  
  FullQuarter: QUARTER.TAG(svTheDate)
  
  ChineseHoroscopeYear: Oconv(svTheDate, "DYA")

[kduke]

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:

[rony_daniel]

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:

Hi,
Please tell me what to do if i have to empty the sequential file first and then load the data?

Thanks,
Rony

[ray.wurlod]

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.

[kduke]

This code overwrites the text file every time you run it. Should not be a problem.

[rony_daniel]

This code overwrites the text file every time you run it. Should not be a problem.

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]

I think you need to clear and then insert.

[ray.wurlod]

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

[rony_daniel]

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

Thanks Ray. It worked.

[kduke]

Ray is correct. I left off WEOFSEQ.