Automating a Time Dimension Source

Oct 07 2012

As I mentioned in Automating a Date Dimension Source, an intra-day time dimension can also be sourced from an automated common table expression (CTE). Before I explain how I sourced a time dimension from a CTE, I should explain my time dimension and why I needed it.

My time dimension supported a business need down to the second for every minute in every hour of each day. Think point of sales transaction. When you are busy, your cash registers can ring up multiple sales within a minute. Your business needs to know where your sales peaks occur throughout the day. Early morning? Late afternoon? Overnight?

Just as with the date dimension, database functions like DATEPART() might help segment data over some time continuum, but leveraging such functions on reports impacts query performance, code complexity and data interpretation.

Why not handle all of those seconds, minutes and hours in a centralized time dimension where query performance is optimized and everyone uses standardized data hierarchy rules? Consider a DimTime table with time rollups.

You might wonder why I have a date dimension for days, weeks, months, etc. separate from a time dimension for seconds, minutes and hours. The short answer is practicality. Each day has 86,400 seconds (i.e., 60 seconds per minute, 60 minutes per hour, 24 hours per day). That means one 365-day year has 31,536,000 seconds. A dimension with 31+ million rows per year is just not going to perform well.

So I created a date dimension that enabled analysts to easily traverse a day-month-year hierarchy and a separate time dimension that enabled analysts to easily traverse a second-minute-hour hierarchy.

Of course, I just intended to explain how I sourced my time dimension from a CTE.

Beginning with SQL Server 2005, Common Table Expression (CTE) offered that better solution. A CTE is a query which returns a temporary result set that can reference itself. The CTE is then used as a source for a subsequent query. Rather than delve into the details of CTE syntaxes, I want to explain how a recursive temporary result set enables me to simplify my time dimension maintenance.

The following annotated query uses a recursive CTE to generate the value of each second within a 24-hour day.

-- Build a CTE for every second in a 24-hour day
WITH cteTime AS (
SELECT CAST('00:00:00' AS TIME(0)) ClockTime
UNION ALL
SELECT DATEADD(SECOND,1,ClockTime)
FROM cteTime
WHERE ClockTime < CAST('23:59:59' AS TIME(0)))

-- Using the CTE of seconds, derive descriptions, bands, etc.
SELECT
-- Common Attributes
((DATEPART(HOUR,ClockTime) * 10000) + (DATEPART(MINUTE,ClockTime) * 100) +
    DATEPART(SECOND,ClockTime)) AS TimeKey,
ClockTime,
CONVERT(VARCHAR(10),ClockTime,109) AS Time12HourDescription,
DATEPART(HOUR,ClockTime) AS HourID,
DATEPART(MINUTE,ClockTime) AS MinuteID,
CASE
    WHEN DATEPART(MINUTE,ClockTime) < 15
      THEN 1
    WHEN DATEPART(MINUTE,ClockTime) < 30
      THEN 2
    WHEN DATEPART(MINUTE,ClockTime) < 45
      THEN 3
    WHEN DATEPART(MINUTE,ClockTime) < 60
      THEN 4
END AS QuarterHourID,
-- Custom Attributes
CASE
    WHEN ClockTime BETWEEN '00:00:00' AND '05:59:59'
      THEN 'Over Night'
    WHEN ClockTime BETWEEN '06:00:00' AND '11:59:59'
      THEN 'Morning'
    WHEN ClockTime BETWEEN '12:00:00' AND '17:59:59'
      THEN 'Afternoon'
    WHEN ClockTime BETWEEN '18:00:00' AND '23:59:59'
      THEN 'Evening'
END AS PeriodName
FROM cteTime
OPTION (MAXRECURSION 0)

The top 10 rows returned from the query above appear below.

TimeKey	ClockTime	Time12HourDescription	HourID	MinuteID	QuarterHourID	PeriodName
0	0:00:00	12:00:00AM	0	0	1	Over Night
1	0:00:01	12:00:01AM	0	0	1	Over Night
2	0:00:02	12:00:02AM	0	0	1	Over Night
3	0:00:03	12:00:03AM	0	0	1	Over Night
4	0:00:04	12:00:04AM	0	0	1	Over Night
5	0:00:05	12:00:05AM	0	0	1	Over Night
6	0:00:06	12:00:06AM	0	0	1	Over Night
7	0:00:07	12:00:07AM	0	0	1	Over Night
8	0:00:08	12:00:08AM	0	0	1	Over Night
9	0:00:09	12:00:09AM	0	0	1	Over Night

The query above should be executable in any SQL Server 2005 or later version. Use that query as a PowerPivot time source and you have an instant ad hoc time dimension. Use that query as a data source in SSIS and you have a package that can maintain your enterprise data warehouse indefinitely.

Need to update your attributes with new business rules? Simply alter the query logic and derive the attributes using the new logic. Because the logic is coded, you can easily manage changes using source controllers such as Team Foundation Server (TFS).

If you are interested in how this logic can create a date dimension, just see my earlier explanation on Automating a Date Dimension Source.

Tagged: CTE, Microsoft, SQL Server, Time Dimension

About the Author

Categories

Popular Tags