Click here to Skip to main content
15,867,686 members
Articles / Database Development / SQL Server

Creating Time Dimension in Microsoft Analysis Services

Rate me:
Please Sign up or sign in to vote.
4.75/5 (12 votes)
6 May 2008CPOL 92.3K   1.8K   20   8
Unlike Date Dimension (with Year, Month, Date), Time Dimension (with Hour, Minutes, and Seconds) is not automatically created by SSAS (Microsoft SQL Server Analysis Services). This article explains how to create and use Time Dimension in Microsoft SQL Server Analysis Services.

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.sqlCREATE 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:

SQL
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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Web Developer
United States United States
Igor is a business intelligence consultant working in Tampa, Florida. He has a BS in Finance from University of South Carolina and Masters in Information Management System from University of South Florida. He also has following professional certifications: MCSD, MCDBA, MCAD.

Comments and Discussions

 
PraiseThanks for scripts Pin
Muhammad Adam30-May-22 5:54
Muhammad Adam30-May-22 5:54 
SuggestionThank you! Pin
Ricardo Rezende17-Oct-14 3:10
Ricardo Rezende17-Oct-14 3:10 
QuestionHow do I use the .DIM file? Pin
Chris.Crowe12-Feb-13 14:38
Chris.Crowe12-Feb-13 14:38 
GeneralSSAS Date Dimension Pin
Sam Kane22-Mar-11 6:06
Sam Kane22-Mar-11 6:06 
GeneralMy vote of 4 Pin
Houssem K.13-Mar-11 0:28
professionalHoussem K.13-Mar-11 0:28 
GeneralAdditional info Pin
Ken Ferrell26-Jan-11 6:43
Ken Ferrell26-Jan-11 6:43 
Nice article. For folks reading this, a Time dimension does not need to be at the minute level of granularity. It is based on your business requirements. I have developed solutions using hour, half hour and 15 minute levels of granularity. That way, if the customer's vision of "Afternoon" is 11:30 AM - 3:45 PM you can support it with the 15 minute of granularity, while reducing the number of fact table rows and improve cube performance. Only down side is to reduce the granularity level on a production system requires a one time conversion and reload of the time dimension identifier in any fact tables that use it.
Generalthanks Pin
bplummer20-Nov-10 5:12
bplummer20-Nov-10 5:12 
GeneralUseful Pin
binaryDigit@@24-May-08 6:49
binaryDigit@@24-May-08 6:49 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.