US and Worldwide: +1 (866) 660-7555

# Thread: day is 0400 to 0400 of next day in cubes

1. Senior Member
Join Date
Aug 2013
Posts
139

## day is 0400 to 0400 of next day in cubes

hello,

I had strange requirement my day should be from 0400hrs to 0359hrs of next day, but the date should be same of that day

example if we consider 11-12-13 it should be 0400hr of 11-12-13 to 0359hrs of 12-12-13 and the date should represent as 11-12-13

i build a cube to represent the day and hourly data how can i get the data according to the above requirment,

if i select day it should get date from 0400 to 0359hrs and if add hourly to that cube if should get the same 0400 to 0300 individually

Any idea would be greatly appreciated

2. Senior Member
Join Date
Nov 2008
Posts
777
This is a tough one. Perhaps you could subtract 4 hours from each DATETIME field before processing. That way your date dimension could be used as normal. For the time, you could then store the hour number in the fact table and adjust it back as necessary.

Another option for the time would be to create an actual time dimension with both the adjusted hour and the actual hour in it and use a surrogate (technical) key to link the appropriate row in the time dimension to each row in the fact table, i.e., the same way the date dimension is linked.
Last edited by darrell.nelson; 12-14-2013 at 10:30 AM. Reason: Another option...

3. Senior Member
Join Date
Aug 2013
Posts
139
thanks darnell.nelson..

i got the first one, would u mind to explain the second option in detail that would be a great Thank U For Your Time

4. Senior Member
Join Date
Nov 2008
Posts
777
Well, your normal date dimension does not have any time-of-day info, the lowest increment is one day. The second idea was to create a similar dimension for the hour of the day (and the minute of the hour if you need it).

5. Junior Member
Join Date
Dec 2013
Posts
7
Our current practice is to have two or more foreign_keys in our fact table, both of which point to the Date/Time dimension table.
- The UTC Date/Time - Categorical, not disrupted by day light savings times, time zone, etc.
- A "Local" Date/Time - With any required offsets built in (day light savings times, time zone, etc).

Both link to the same dimension table, just different points on it.

Having both also makes it possible to "see" that one event happened after another, even in the middle of a clock change due to day-light savings.
- The UTC will keep moving forward, even if the Local Time jumps back an hour.

6. Senior Member
Join Date
Aug 2013
Posts
139

## day as 0400 to 0400

Hello Every One,
Wish u a very happy and easy debugging new year..............

i have written a case to make view from original fact table to make my reporting day as 0400 to next day 0400hrs every fine work just fine but for 31st dec i lost the 4 hours of 01-01-2014

(case when (hour(dbdate) < 4) then date_format((cast(dbdate as date) - 1),'%d-%m-%Y') else date_format(cast(dbdate as date),'%d-%m-%Y') end) AS `reportingdate`

Mysql i am also worried if this case gonna work if month changes like 31-01-2014 to 01-02-2014

i used this view to make my cubes
Last edited by suresh84c; 01-01-2014 at 12:55 AM.

7. Senior Member
Join Date
Aug 2013
Posts
139
Originally Posted by suresh84c
Hello Every One,
Wish u a very happy and easy debugging new year..............

i have written a case to make view from original fact table to make my reporting day as 0400 to next day 0400hrs every fine work just fine but for 31st dec i lost the 4 hours of 01-01-2014

(case when (hour(dbdate) < 4) then date_format((cast(dbdate as date) - 1),'%d-%m-%Y') else date_format(cast(dbdate as date),'%d-%m-%Y') end) AS `reportingdate`

Mysql i am also worried if this case gonna work if month changes like 31-01-2014 to 01-02-2014

i used this view to make my cubes

(case when (hour(dbdate) < 4) then date_format((cast(dbdate as date) - interval 1 day ),'%d-%m-%Y') else date_format(cast(dbdate as date),'%d-%m-%Y') end) AS `reportingdate`
this worked for me in case any one looking for it......

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•