Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Time Dimension with Day Light Savings

2 posters

Go down

Time Dimension with Day Light Savings Empty Time Dimension with Day Light Savings

Post  logic4fun Thu May 13, 2010 1:59 pm

I know time dimension has been adressed here in many threads but what i didnt find is dimension which takes care of the DST changes.

I have a time dimension table that has 86400 rows which represent each second of the day. But my question is how do i handle the DST day which has 25 hours or more seconds than 86400 ?

Thanks for your time and looking at this

Prasad

logic4fun

Posts : 2
Join date : 2010-05-13

Back to top Go down

Time Dimension with Day Light Savings Empty Re: Time Dimension with Day Light Savings

Post  ngalemmo Thu May 13, 2010 2:38 pm

It begs the question... what do YOU want to do about it?

If you are recording time as local time, then, it is what it is. A day in the fall when the clocks go back do not have 25 hours, it has one hour twice (2:00:00 am - 2:59:59 am) and in the spring there is nothing at that time. It's an anomoly that happens twice a year and most of the time, it doesn't matter.

In situations where it does matter, don't use local time. Use UTC time to record the event against the dimension and convert to local time when doing reports. Store local time as a timestamp (i.e. degenerate dimension) on the fact.
ngalemmo
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

http://aginity.com

Back to top Go down

Time Dimension with Day Light Savings Empty Re: Time Dimension with Day Light Savings

Post  logic4fun Thu May 13, 2010 2:56 pm

Thanks for the response.
We do record the time in UTC and each individual record gets rolled up to an HOUR level to perform Hour level summaries. This worked fine so far until we decided to create a time dimension to get nice attributes like Work Hours, Off Hours etc.

So now what i have is I will have to roll up by UTC but cant create a dimension table that I can use that as my TIMEKEY. Because if i use UTC as the Key for my Time dimension then my timedimension would be really huge table.

So is it a good idea to keep the UTC in tact and create an HOUR key (1 - 24) and join it to time dimension or any other route ?

logic4fun

Posts : 2
Join date : 2010-05-13

Back to top Go down

Time Dimension with Day Light Savings Empty Re: Time Dimension with Day Light Savings

Post  ngalemmo Thu May 13, 2010 3:55 pm

Yes, you derive whatever portion of the timestamp you need to get the key to reference the dimension. If you use time of day from UTC to build a reference to your existing time of day dimension, I do not see how it would make the dimension any larger.

Now, if you store both local and UTC dimensional references, for both date and time of day, you would actually be able to report a 25 hour day (local time) by grouping on local date, UTC date, UTC time (hour of day) and local time (hour of day).
ngalemmo
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

http://aginity.com

Back to top Go down

Time Dimension with Day Light Savings Empty Re: Time Dimension with Day Light Savings

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum