US and Worldwide: +1 (866) 660-7555
Results 1 to 7 of 7

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

  1. #1
    Join Date
    Aug 2013
    Posts
    126

    Default 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
    Thank U For Your Time
    Suresh

  2. #2
    Join Date
    Nov 2008
    Posts
    777

    Default

    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...
    pdi-ce-4.4.0-stable
    Java 1.7 (64 bit)
    MySQL 5.6 (64 bit)
    Windows 7 (64 bit)

  3. #3
    Join Date
    Aug 2013
    Posts
    126

    Default

    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
    Thank U For Your Time
    Suresh

  4. #4
    Join Date
    Nov 2008
    Posts
    777

    Default

    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).
    pdi-ce-4.4.0-stable
    Java 1.7 (64 bit)
    MySQL 5.6 (64 bit)
    Windows 7 (64 bit)

  5. #5
    Join Date
    Dec 2013
    Posts
    4

    Default

    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. #6
    Join Date
    Aug 2013
    Posts
    126

    Default 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.
    Thank U For Your Time
    Suresh

  7. #7
    Join Date
    Aug 2013
    Posts
    126

    Default

    Quote Originally Posted by suresh84c View Post
    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......
    Thank U For Your Time
    Suresh

Posting Permissions

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