2 Replies Latest reply: Jun 21, 2012 9:43 PM by Michael Fenton RSS

    Clock Dimension

      Hi all,

       

      I am sure this has been solved many times in the past, but I am looking for the simplest method.

       

      I have a DateTime stamp coming from my datasource and I want to split it into a Date dimension and a TimeOfDay (Clock) dimension.  The Clock dimension should have one record for every second on the day.  This means 86400 rows, from 00:00:00 to 23:59:59.

       

      How would you go about creating this Clock dimension?

       

      I am starting from here:

       

      Tmp_Times:

      LOAD

                rowno() -1 as SecondOfDay

      Autogenerate 86400;

       

       

      Edit: I am simplifying my source data slightly by stripping out milliseconds using the following function:

        MakeTime(Hour(DateRequested), Minute(DateRequested), Second(DateRequested))

        • Re: Clock Dimension
          Johannes Sunden

          Hi Michael,

           

          You could generate the time values like this:

          Tmp_Times:

          LOAD

                    time((rowno() -1)/86400,'hh:mm:ss') as SecondOfDay

          Autogenerate 86400;

            • Re: Clock Dimension

              That's much better thanks.  My full script looks like this:

               

              Tmp_Times:
              LOAD
                        Time((RowNo() - 1) / 86400, 'hh:mm:ss') as Time
              Autogenerate 86400;
              
              
              Clock:
              LOAD
                        MakeTime(Hour(Time), Minute(Time), Second(Time)) as TimeRequested,
                        Hour(Time)          as Hour,
                        Minute(Time)          as Minute,
                        Second(Time)          as Second
              Resident Tmp_Times;
              
              
              DROP Table Tmp_Times;
              
              

               

              Message was edited by: Michael Fenton I had to modify slightly as the /86400 on the third row resulted in a level of precision that did not map to the source column