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.
Saturday, December 6, 2008
Subscribe to:
Post Comments (Atom)
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
ReplyDeletekapil
Hi Kapil,
ReplyDeletefor 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.
Mangal Buddy you are a genius ! It did resolve my problem Thank you so much for the above code !
ReplyDeleteJust one question what if I want the Fiscal Calendar as well in this dimension ?
Thank you
Hemant
Thank you. It really helped me.
ReplyDeleteBut I have a question. How, if I want to add a semester in this dimension?
Stephanie
Thank you. It really helped me.
ReplyDeleteBut I have a question. How, if I want to add a semester in this dimension?
Stephanie
have you an example for oracle
ReplyDeleteh.sassa@yahoo.fr
thanks mangal (humus chips salad ? :-)), it really helpd us.
ReplyDeletehai i am new to sql from the above query how can i set primary key for DateID in select statement
ReplyDeleteTake a look of the code here...Added Semester, Semi month and some more columns -
ReplyDeleteCREATE 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
can be use this ssis sql 2008?
ReplyDelete