Elon's Blog

Stuff that may be of use to someone…

SQL to manually create a Time Dimension Table

Posted by Elon B. on March 17, 2010

Sometimes, you want to create your own Time Dimension Table for use with your SSAS Cube.

Well here is the T-SQL, I use to create the bare-bones of a table. You can define your own start and end date, plus it can be enhanced to add further columns for things such as Fiscal Year, Fiscal Qtr etc, as you see fit, by simple editing of the code.

The code will create a new SQL Table called DIM.TIME 

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DIM.TIME]') AND type in (N'U'))

DROP TABLE [dbo].[DIM.TIME]

GO

 

set datefirst 1

declare

 @start_date datetime

 ,@end_date datetime

 ,@loop_day datetime

 ,@diff int

 ,@loop int

 

Select

 @start_date ='2007-01-01' -- Adjust as per needed

 ,@end_date = '2010-12-31'

 ,@loop = 0

 

select

 @diff = datediff(dd,@start_date,@end_date)

 

CREATE TABLE [DIM.TIME] (

 [Time_ID] [int] identity(1,1) primary key,

 [Cal_Date] [datetime] NULL ,

 [The_Date] [int] NULL ,

 [The_Day] [varchar] (20),

 [The_Month_No] [int] NULL ,

 [The_Year] [int] NULL ,

 [The_Quarter] [varchar] (2),

 [Quarter] [varchar] (10),

 [The_Month_Name] [varchar] (10),

 [The_Month_ShortName] [varchar] (3),

 [Month] [varchar] (10),

 [Date] [varchar] (14),

 

) ON [PRIMARY]

 

while @loop <= @diff

begin

 select @loop_day = dateadd(dd,@loop,@start_date)

 insert into [DIM.TIME]

 select

 

 @loop_day       --  [Cal_Date]

 

 ,datepart(dd,@loop_day)       -- [The_Date]

 

 ,case datepart(dw,@loop_day)

     when 1 then 'Monday'

     when 2 then 'Tuesday'

     when 3 then 'Wednesday'

     when 4 then 'Thursday'

     when 5 then 'Friday'

     when 6 then 'Saturday'

     when 7 then 'Sunday'

  end                           -- [The_Day]

 

 ,datepart(mm,@loop_day)  --    [The_Month_No]

 ,datepart(yy,@loop_day)    --  [The_Year]

 

 ,'Q'+convert(varchar,datepart(qq,@loop_day))   -- [The_Quarter]

 

 ,'Q'+convert(varchar,datepart(qq,@loop_day)) +

           ' ' + convert(varchar,datepart(yy,@loop_day)) -- [Quarter] (Qtr & Year)

 

 ,case datepart(month,@loop_day)

     when 1 then 'January'

     when 2 then 'February'

     when 3 then 'March'

     when 4 then 'April'

     when 5 then 'May'

     when 6 then 'June'

     when 7 then 'July'

     when 8 then 'August'

     when 9 then 'September'

     when 10 then 'October'

     when 11 then 'November'

     when 12 then 'December'

  end                             -- [The_Month_Name]

 

 ,case datepart(month,@loop_day)

       when 1 then 'Jan'

       when 2 then 'Feb'

       when 3 then 'Mar'

       when 4 then 'Apr'

       when 5 then 'May'

       when 6 then 'Jun'

       when 7 then 'Jul'

       when 8 then 'Aug'

       when 9 then 'Sep'

       when 10 then 'Oct'

       when 11 then 'Nov'

       when 12 then 'Dec'

  end                        -- [The_Month_ShortName]

 

 ,case datepart(month,@loop_day)

       when 1 then 'Jan'+ '  ' + convert(varchar,datepart(yy,@loop_day))

       when 2 then 'Feb'+ '  ' + convert(varchar,datepart(yy,@loop_day))

       when 3 then 'Mar'+ '  ' + convert(varchar,datepart(yy,@loop_day))

       when 4 then 'Apr'+ '  ' + convert(varchar,datepart(yy,@loop_day))

       when 5 then 'May'+ '  ' + convert(varchar,datepart(yy,@loop_day))

       when 6 then 'Jun'+ '  ' + convert(varchar,datepart(yy,@loop_day))

       when 7 then 'Jul'+ '  ' + convert(varchar,datepart(yy,@loop_day))

       when 8 then 'Aug'+ '  ' + convert(varchar,datepart(yy,@loop_day))

       when 9 then 'Sep'+ '  ' + convert(varchar,datepart(yy,@loop_day))

       when 10 then 'Oct'+ '  ' + convert(varchar,datepart(yy,@loop_day))

       when 11 then 'Nov'+ '  ' + convert(varchar,datepart(yy,@loop_day))

       when 12 then 'Dec'+ '  ' + convert(varchar,datepart(yy,@loop_day))

 end                    -- [Month]   ( Month & Year)

 

,convert(varchar,datepart(dd,@loop_day)) + ' ' +

case datepart(month,@loop_day)

       when 1 then 'Jan'+ '  ' + convert(varchar,datepart(yy,@loop_day))

       when 2 then 'Feb'+ '  ' + convert(varchar,datepart(yy,@loop_day))

       when 3 then 'Mar'+ '  ' + convert(varchar,datepart(yy,@loop_day))

       when 4 then 'Apr'+ '  ' + convert(varchar,datepart(yy,@loop_day))

       when 5 then 'May'+ '  ' + convert(varchar,datepart(yy,@loop_day))

       when 6 then 'Jun'+ '  ' + convert(varchar,datepart(yy,@loop_day))

       when 7 then 'Jul'+ '  ' + convert(varchar,datepart(yy,@loop_day))

       when 8 then 'Aug'+ '  ' + convert(varchar,datepart(yy,@loop_day))

       when 9 then 'Sep'+ '  ' + convert(varchar,datepart(yy,@loop_day))

       when 10 then 'Oct'+ '  ' + convert(varchar,datepart(yy,@loop_day))

       when 11 then 'Nov'+ '  ' + convert(varchar,datepart(yy,@loop_day))

       when 12 then 'Dec'+ '  ' + convert(varchar,datepart(yy,@loop_day))

end                      -- [Date]  (DD MMM YYY)

 

 select

 @loop = @loop + 1 

end

The Output table looks as follows. (click on it to view full image)

DimTime Table

Hope it helps.

Advertisements

Please Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: