Clock Dimension

[#1]

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

[Anonymous]

Hi Michael,

You could generate the time values like this:

Tmp_Times:

LOAD

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

Autogenerate 86400;

[#3 · Accepted solution]

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