Email Password Remember me?  Lost your password?

• Download source code - 4 KB

Introduction

Sometimes, you need to create a report on a time level (Hour, Minutes, and Seconds). For a relational report, you can use the DATEPART() function to extract time information out of a DateTime column. However, doing this is difficult for an OLAP report. This article explains how to create a Time Dimension table and use it inside SSAS.

Background

To understand this article, you need to understand the basics of dimensional modeling and Microsoft SQL Server Analysis Services.

Using the code

The code (zip file) is composed of four scripts:

1. DimTime.sql – CREATE TABLE statement creates the DimTime table.
2. Populate DimTime.sql – Script populates the DimTime table with time info. Each record represents a second.
3. Dim Time.dim – Optional SSAS 2005 dim file you can add to your SSAS database.
4. Populate Fact.sql – Optional sample script shows how to link your fact table to the DimTime dimension.

The Populate DimTime.sql script looks like this:

delete from DimTime

declare @DimTimeKey int, @Date datetime, @AM char(2), 
    @hour24 tinyint, @hour tinyint, @minute tinyint, @second int
set @DimTimeKey = 0

while @DimTimeKey < (60*60*24)
begin
    set @DimTimeKey = @DimTimeKey + 1
    set @Date = DATEADD(second,@DimTimeKey,convert(datetime, '1/1/2007'))
    set @AM = right(convert(varchar,@Date,109),2)
    set @hour24 = DATEPART(hour, @Date)
    set @hour = case when @AM = 'PM' then @hour24 - 12 else @hour24 end
    set @minute = DATEPART(minute, @Date)
    set @second = DATEPART(second, @Date)

    insert into dbo.DimTime(DimTimeKey,Time,Time24,HourName, _
                MinuteName,MinuteKey,Hour,Hour24,Minute,Second,AM)
    select    @DimTimeKey, 
            Time = right('0' + convert(varchar,@hour),2) 
                + ':' + right('0' + convert(varchar,@minute),2)  
                + ':' + right('0' + convert(varchar,@second),2) + ' ' + @AM,
            Time24 = convert(varchar,@Date,108), 
            HourName = right('0' + convert(varchar,@hour),2) + ' ' + @AM,
            MinuteName = right('0' + convert(varchar,@hour),2) 
                + ':' + right('0' + convert(varchar,@minute),2)+ ' ' + @AM,
            MinuteKey = (@hour24*60) + @minute,
            @hour, @hour24, @minute, @second, @AM
end

You must Sign In to use this message board.

Per page 102550

FirstPrevNext

Useful binaryDigit@@ 7:49 24 May '08   This is indeed useful. Couldn't a time table be used in a relational data store, like a calender table, instead of the datepart function? I try to avoid those functions on large tables since every row in the search condition has to be ckecked. Sign In·View Thread·PermaLink 5.00/5

Last Visit: 17:05 23 Nov '09     Last Update: 17:05 23 Nov '09 1