January 19, 2011 at 9:38 am
Hello everyone 🙂
I am in the process of trying to setup up a small datawarehouse that will hold records of maintenance tasks that run on some of our systems in order to try and trend the duration over time. However I am having difficullty with setting up a time dimension (not worked with the grain of a second before). I was wondering if anyone can point me in the direction of some reading material on how to handle this particular dimension, I can find plenty on the date dimension.
My main issue is just how to handle the first second/minute/hour of the day, do you use values that start at 0 or 1? So I would start off with the values of 0/0/0 or 1/0/0 or 1/1/1?
Any pointers from those with more knowledge are much appreciated, if you need me to post more information before someone can answer just let me know and I'll see what I can do.
Thanks in advance
🙂
March 17, 2011 at 9:21 am
There are many different ways to create a time dimension and it all really depends on what granularity you are going for. Here is a post on SSC that has scripts for date and time dimensions:
http://www.sqlservercentral.com/scripts/Data+Warehousing/65762/
March 18, 2011 at 11:12 am
Hi,
I would suggest saving it as a datetime, as this will give you more flexibility for analysis. Possibly with a separate date-only dimension for querying/partitioning. For example, in our telecomms data warehouse, we have both CALL_DATE (e.g. '2011-03-18 00:00:00') and CALL_DATE_TIME (e.g. '2011-03-18 17:12:32'). If you decide to have the time portion separate, I think that integer number of seconds past midnight is more easily analysable than HMS format.
In SQL Server, the day runs from e.g. '2011-03-18 00:00:00' to '2011-03-18 23:59:59', so it is zero-based, if you like. Try this:
select cast (40618 as datetime);
select cast (40618 + (1.0 / 86400) as datetime);
HTH
June 10, 2011 at 1:52 pm
JMartin-392745 (1/19/2011)
Hello everyone 🙂I am in the process of trying to setup up a small datawarehouse that will hold records of maintenance tasks that run on some of our systems in order to try and trend the duration over time. However I am having difficullty with setting up a time dimension (not worked with the grain of a second before). I was wondering if anyone can point me in the direction of some reading material on how to handle this particular dimension, I can find plenty on the date dimension.
My main issue is just how to handle the first second/minute/hour of the day, do you use values that start at 0 or 1? So I would start off with the values of 0/0/0 or 1/0/0 or 1/1/1?
Any pointers from those with more knowledge are much appreciated, if you need me to post more information before someone can answer just let me know and I'll see what I can do.
Thanks in advance
🙂
I'm not an expert, but I've always been a fan of the following...
DimCalendarDate
Key: Date_ID
Type: INT
Values: 20110101 - ????1231 (= Jan 1/2011)
DimCalendarTime
Key: Time_ID
Type: INT
Values: 000000 through 86399 (= 00:00:00 through 23:59:59)
This allows you to add attributes to your time-dimension such as AM/PM or shift1/2/3 etc, hour/min/sec, etc etc
Data Enthusiast | @SQLbyoBI | www.opifexsolutions.com
June 10, 2011 at 2:13 pm
I have always used separate time and date dimensions for flexibility. I usually keep an integer representation of the date value and the string values plus many other attribute columns. As others have noted, keeping separate date and time dimensions allows you to add extra attributes to each dimension. Some additional date attributes may be:
date strings (06/21/2011, 6/21/11, June 21, 2011, ...)
date parts like month, year, week of year, week of month, quarter of year
fiscal periods (based on company fiscal calendar)
holiday flag (if business is closed on holiday. This can be useful when determining staffing or productivity measurements)
some time attributes could be:
time value (00:00:01)
separate columns for time parts - hour, minute, second
time interval_30 (00:30, 01:00, 01:30, ...)
hour interval (01:00, 02:00, ...)
shift info (based on business needs like morning, evening and overnight)
times in AM/PM format 01:16:01 AM, 02:54:32 AM, 01:32:07 PM
This allows you to easily display your measures in many different date/time formats without formatting much on the front end. Your fact tables just store the related time and date key values.
June 10, 2011 at 5:20 pm
I always create time as a separate dimension also. A current manufacture I'm doing this for has the following time dimension. Of course, it is based on their requirements with 20 second, 1 minute, 5 minute, and 1 hour intervals, but a 1 second interval could easily be added:
DIM_TIME
DimTimeId bigint
TimeBand time(7)
TimeBand20Sec varchar(50)
TimeBand5Min varchar(50)
TimeBand1Min varchar(50)
TimeH int
TimeHName varchar(50)
TimeHMilitary varchar(50) NOT NULL
The TimeBand values go like this for each row:
00:00:00.0000000
00:00:01.0000000
00:00:02.0000000
00:00:03.0000000
00:00:04.0000000
00:00:05.0000000
.
.
23:59:57.0000000
23:59:58.0000000
23:59:59.0000000
LinkedIn - http://www.linkedin.com/in/carlosbossy
Blog - http://www.carlosbossy.com
Follow me - @carlosbossy
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply