Saturday, December 6, 2008

How to create a time dimension in SQL Server

In this post I’ll help you creating a Time Dimesnion as it is very much required if you are working on Sql Server Analysis Services. Until now you need to write a complex Sql procedure running into loops for creating a large Time Dimension. But with Sql Server 2005 and Common Table Expressions (CTE) it is very easy to create your own Time Dimension. A time Dimension is also called as Calendar Table by many.
When I started working on SSAS 2005 I also searched for a good Time Dimension, but now I have created my own Time Dimension with following Script.

Following script creates a table named TimeDiemension and populates it from 2000-01-01 to 2020-12-31. You can easily put your required dates.

Even though following query may look little big but believe me, it's nothing but a simple SELECT statement with CTE.
------- TimeDimension -------

WITH Mangal as
(
SELECT Cast ('2000-01-01' as DateTime) Date --Start Date
UNION ALL
SELECT Date + 1
FROM Mangal
WHERE Date + 1 < = '2020-12-31' --End date

)

SELECT Row_Number() OVER (ORDER BY Date) as DateId
,Date
,YEAR (date) as Year
,DatePart ( qq, date) as Quarter
,MONTH (date) as Month_Number_Year
,DateDiff(mm,DateAdd(qq,DateDiff(qq,0,date),0),date)+1 as Month_Number_Of_Quarter
,DatePart (wk, Date) as Week_Number_Of_Year
,datediff(wk,dateadd(qq,datediff(qq,0,date),0),date)+1 as Week_Number_Of_Quarter
,datediff(wk,dateadd(mm,datediff(mm,0,date),0),date)+1 as Week_Number_Of_Month
,DatePart (dy, date) as Day_Number_Of_Year
,datediff(dd,dateadd(qq,datediff(qq,0,date),0),date)+1 as Day_Number_Of_Quarter
,DAY (date) as Day_Number_Of_Month
,DatePart (dw, date) as Day_Number_Of_Week
,DateName (mm, date) as Month_Name
,LEFT ( DateName (mm, date), 3) Month_Name_Abbreviation
,DateName (dw, date) as Day_Name
,LEFT (DateName (dw, date), 3) as Day_Name_Abbreviation
,CONVERT(VarChar(10),date,112) as YYYYMMDD
,CONVERT(VarChar(10),date,111) as [YYYY/MM/DD]
,CONVERT(VarChar(11),date,100) as [mon dd yyyy]
,CONVERT(VarChar(11),date,120) as [yyyy-mm-dd]

INTO TimeDimension -- Name of the Table

FROM Mangal

OPTION (MAXRECURSION 0)

---- Script Ends Here ----
Now your TimeDimension is ready. Do a simple
SELECT * FROM TimeDimension
for a check.
Hopefully you will find this script helpful. Any questions and suggestions are welcome.
- Mangal Pardeshi.







10 comments:

  1. Thanks this is good. I have a question though, this is designed keeping at mind that Day is at the mininmun level . If i need to modify it to show at Second level, how can i do it

    kapil

    ReplyDelete
  2. Hi Kapil,
    for seconds you can do -
    WITH Mangal AS
    (
    SELECT Cast ('2009-01-01' as DateTime) Date --Start Date
    UNION ALL
    SELECT DateAdd(ss, 1, Date)
    FROM Mangal
    WHERE DateAdd(ss, 1, Date) < = '2010-01-01' --End date
    )


    But remember the above query, is good when number records are less. When you take seconds as lowest level, number of records can shoots up very high, and a recurssive cte can give bad performamce. With help of Numbers tables you will get performance. If want some example using a NUmbers table, let me know, I can mail you the solution.

    ReplyDelete
  3. Mangal Buddy you are a genius ! It did resolve my problem Thank you so much for the above code !

    Just one question what if I want the Fiscal Calendar as well in this dimension ?

    Thank you
    Hemant

    ReplyDelete
  4. Thank you. It really helped me.
    But I have a question. How, if I want to add a semester in this dimension?

    Stephanie

    ReplyDelete
  5. Thank you. It really helped me.
    But I have a question. How, if I want to add a semester in this dimension?

    Stephanie

    ReplyDelete
  6. have you an example for oracle
    h.sassa@yahoo.fr

    ReplyDelete
  7. thanks mangal (humus chips salad ? :-)), it really helpd us.

    ReplyDelete
  8. hai i am new to sql from the above query how can i set primary key for DateID in select statement

    ReplyDelete
  9. Take a look of the code here...Added Semester, Semi month and some more columns -
    CREATE PROCEDURE [dbo].[Os_Populate_Date_Dim]
    (
    @FIRST_DATE DATETIME,
    @LAST_DATE DATETIME
    )
    AS
    BEGIN
    WITH TE_Os_Dim_Date as
    (
    SELECT Cast (@FIRST_DATE as DateTime) Date --Start Date
    UNION ALL
    SELECT Date + 1 FROM TE_Os_Dim_Date
    WHERE Date + 1 < = @LAST_DATE --End date
    )
    INSERT INTO [dbo].[Os_Dim_Date] (
    [DateKey]
    ,[AlternateDateKey]
    ,[CalenderDate]
    ,[DayOfWeek]
    ,[ShortDayOfWeek]
    ,[DayNumOfYear]
    ,[DayNumOfQuarter]
    ,[DayNumOfMonth]
    ,[DayNumOfWeek]
    ,[WeekName]
    ,[WeekNumOfYear]
    ,[WeekNumOfQuarter]
    ,[WeekNumOfMonth]
    ,[MonthName]
    ,[MonthShortName]
    ,[MonthNumOfYear]
    ,[MonthNumOfQuarter]
    ,[QuarterName]
    ,[QuarterShortName]
    ,[Quarter]
    ,[YearName]
    ,[Year]
    ,[SemiMonth]
    ,[Semester]
    )
    SELECT
    CAST(CONVERT(NVARCHAR(30),DATE, 112) AS INT) AS DateKey
    ,LEFT(CAST(CONVERT(NVARCHAR(30),DATE, 120) AS VARCHAR), 10) AS AlternateDateKey
    ,DATE AS CalenderDate

    ,DateName (dw, date) as DayOfWeek
    ,LEFT (DateName (dw, date), 3) as ShortDayOfWeek
    ,DATEPART (DY, DATE) AS DayNumOfYear
    ,DATEDIFF(DD,DATEADD(QQ,DATEDIFF(QQ,0,DATE),0),DATE)+1 AS DayNumOfQuarter
    ,DAY (DATE) AS DayNumOfMonth
    ,DATEPART (DW, DATE) AS DayNumOfWeek

    ,convert(varchar(4),YEAR(Date))+'-Week-'+
    CAST(DatePart (wk, Date) AS VARCHAR) AS WeekName
    ,DatePart (wk, Date) as WeekNumOfYear
    ,datediff(wk,dateadd(qq,datediff(qq,0,date),0),date)+1 as WeekNumOfQuarter
    ,datediff(wk,dateadd(mm,datediff(mm,0,date),0),date)+1 as WeekNumOfMonth

    ,DateName (mm, date) as MonthName
    ,LEFT ( DateName (mm, date), 3) MonthShortName
    ,MONTH (date) as MonthNumOfYear
    ,DateDiff(mm,DateAdd(qq,DateDiff(qq,0,date),0),date)+1 as MonthNumOfQuarter

    ,datename(year,Date)+' Q'+ datename(quarter,Date) AS QuarterName
    ,'Q'+datename(quarter,Date) AS QuarterShortName
    ,DatePart ( qq, date) as Quarter

    ,datename(year,Date) AS YearName
    ,YEAR (date) as Year

    ,'SemiMonthP' + CASE WHEN Day(Date) <= 15 then '1' ELSE '2' END + ' ' + DateName (mm, date) + ' ' + datename(year,Date) AS SemiMonth
    ,CASE WHEN MONTH(Date) <= 6 then 1 ELSE 2 END AS Semester
    FROM TE_Os_Dim_Date

    OPTION (MAXRECURSION 0)
    END
    GO

    ReplyDelete
  10. can be use this ssis sql 2008?

    ReplyDelete