Handling time in a date/time field 1

[jem122974]

I am trying to figure out how to take some tables and turn them into star schemas. My simplest example is a table that is keyed by a date/time field (containing both a date and a time) and then several quantity fields. These quantities are recorded every few minutes.

Kimball says on page 245 of "The Data Warehouse Lifecycle Toolkit" to split the date/time field up into two separate fields, one for date and one for time. I did this and was able to join my resulting fact table to my date dimension (day level).

Now my question is what do I do with the time field? My users are going to want to report these quantities by hour or maybe half hour. Do I also need a time dimension down the second? Kimball says to leave it in the fact table as an integer, but that won't help my users. Any advice?

 

[MDXer]

I always seperate my dates and times into seperate dimensions. I have written a script that creates a time dimension that is

Hour
--Half Hour
---QTR Hour
----5 Minute
-----Minute

I have the time broke out a number of ways such as actual time in both 12 and 24 hour formats 3:00 PM 15:00.

This helps with usability. If you create time based levels below the day in a date dimension then you have 24 Hours, 1440 minutes or 86400 members at the leaf depending howdeep you decide to go. It also makes it harder to report elements like Sales between 3pm and 9pm across all days. by having these elements seperate you reduce the number of dimension elements and make it easier for users to navigate. Also my experience is that users think of dates and times as completely different elements.

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[Hans63]

Well put Paul. My solution as well.

 

[jem122974]

Thanks for the feedback. That is the direction I'll head...

 

[jem122974]

Paul, just to clarify, what goes into the half hour field for example? Do you put "10:30" in there for every record between "10:01" and "10:30"? Or do you put a 1 for first half hour and a 2 for second half hour?

This is the structure I was thinking of:

Seconds from midnight (int) -- KEY
12 hour time (string)
24 hour time (string)
12 hour (int)
24 hour (int)
minute (int)
second (int)
am/pm (string)

So example record would be:
48610
"1:30:10 PM"
"13:30:10"
1
13
30
10
"PM"

So how does your half hour, quarter hour and five minute fields work?

Thanks,
Jon

 

[MDXer]

as I said I have a number of other attributes to display, to the user that breaks out the time in any way I can forsee them asking for.

As for how the half hour is displayed:

10:00 - 10:59
- 10:00 - 10:29
10:30 - 10:59
-- 10:30 - 10:44
10:45 - 10:59
--- 10:45 - 10:49
10:50 - 10:54
10:55 - 10:59

My data is served up using SSAS so I can enable attribute hierarchies allowing the user to interact with

Hour 1
Hour 2
|
|
Hour 24

This type of break out was helpful in my last project where the business day wasn't midnight to 11:59 but typically 3am to 2:59 am.

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[jem122974]

Interesting... Sorry I am so slow, but in the database what would the half hour field contain for 10:25? Would it contain an integer 1 for the first half hour? Or would it contain a string "10:00"? Or would it contain 36,000 for the number of seconds since midnight?

Thanks,
Jon

 

[MDXer]

The HalfHourDecription field would contain 10:00 - 10:29 since this table is populated by a standard script and populates the same I make the HalfHourID field the integer value for what number half hour it is in this case it is 22.

I haven't yet encountered a project where I have had to go down to the second. Maybe high volume sales places like QVC or something like that where an accurate sales trend is crucial but I can't find the value in going that deep.

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[jem122974]

Thanks Paul, that makes sense...