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:
rowno() -1 as SecondOfDay
Edit: I am simplifying my source data slightly by stripping out milliseconds using the following function: