2 Replies Latest reply: Apr 12, 2017 1:56 PM by moez ghattas RSS

    time dimension/table script

    moez ghattas

      Hi i'm looking for a script to generate time dimension/table  for the year 2013-2018: year, month, week, day, hour can anyone help me ?

        • Re: time dimension/table script
          omar bensalem

          Suppose you have a table in your model containing a timestam field (DD/MM/YYYY hh;mm:ss)

          Let's suppose this field is called Timestamp:

          Fact:

          load

          A,

          B,...

          Timestamp

          from

          source;

           

          What you do is create a date and time field from the timestamp field:

           

          Fact:

          load

          A,

          B,...

          Timestamp,

          DATE(Timestamp) as date,

          Time(Timestamp) as time

          from

          source;

           

          With that being added, we create 2 new tables:

          1) Master calendar:

           

           

               

          Temp: 

          Load 

          min(date) as minDate, 

          max(date) as maxDate 

          Resident Fact

               

          Let varMinDate = Num(Peek('minDate', 0, 'Temp')); 

          Let varMaxDate = Num(Peek('maxDate', 0, 'Temp')); 

          DROP Table Temp; 

               

          TempCalendar: 

          LOAD 

          $(varMinDate) + Iterno()-1 As Num, 

          Date($(varMinDate) + IterNo() - 1) as TempDate 

          AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 

               

          MasterCalendar: 

          Load 

            TempDate AS date

            week(TempDate) As Week, 

            Year(TempDate) As Year, 

            Month(TempDate) As Month, 

            Day(TempDate) As Day, 

             

            Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear, 

            WeekDay(TempDate) as WeekDay 

          Resident TempCalendar 

          Order By TempDate ASC; 

          Drop Table TempCalendar; 

           

          2) we create a time table:

           

          Let vMinDate=floor(TimeStamp#('00:00:00', 'hh:mm:ss'));

          Let vMaxDate=floor(TimeStamp#('23:59:59', 'hh:mm:ss'));

           

           

          D_Time_TMP: 

          LOAD TimeStamp($(vMinDate) + (RecNo()/60/60/24) + (IterNo() -1),'hh:mm:ss') AS AddedTimeStamp

          AUTOGENERATE 86400 WHILE Num($(vMinDate) + IterNo() -1) <= Num($(vMaxDate));  

           

          D_Time_TMP2:

          LOAD RowNo() as [%Time SEQ]

           

            ,time([AddedTimeStamp]) as time

            ,Hour([AddedTimeStamp]) as Hour

            ,Minute([AddedTimeStamp]) as Minute

            ,Time(Class(Timestamp#([AddedTimeStamp],'hh:mm:ss'),MakeTime(0,15)),'hh:mm:ss') as QHour

          Resident D_Time_TMP;

           

           

           

           

          D_Time:

          Load [%Time SEQ]

           

            ,time

            ,Hour

            ,Minute

            ,QHour

            ,AutoNumber( [QHour],'%Quarter of Hour SEQ ID' )  as [%Quarter of Hour SEQ ID]

          Resident D_Time_TMP2;

           

           

           

          DROP Table D_Time_TMP,D_Time_TMP2;