Create a store procedure in your DW database (datawarehouse).
Code Snippet
CREATE
PROCEDURE [dbo].[usp_Create_dim_Date_Table]
(
@StartDate smalldatetime = ’20080101′,
@EndDate smalldatetime = ’20081231′
)
AS
BEGIN
SET NOCOUNT ON;
IF
EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N’[dbo].[dim_Date]‘) AND type in(N’U’))
DROP
TABLE [dbo].[dim_Date]
CREATE
TABLE dbo.dim_Date(
[MonthKey] [int] NOT NULL,
[MonthName] [nvarchar](15) NOT NULL,
[YearKey] [smallint] NOT NULL,
[YearName] [nvarchar](4) NOT NULL,
[MonthOfYearKey] [tinyint] NOT NULL,
[MonthOfYearName] [nvarchar](15) NOT NULL,
CONSTRAINT [PK_dim_Date] PRIMARY KEY CLUSTERED
(
[MonthKey] ASC
)
)
set
language English – or any other
set
datefirst 1 – 7 for Sunday, 1 for Monday
set
dateformat dmy – set differently if necessary
declare
@CurrentDate smalldatetime
declare
@TimeID as int
set
@CurrentDate = @StartDate
set
@TimeID = 1
while
( @CurrentDate <= @EndDate )
begin
insert into [dbo].[dim_Date]
(
[MonthKey] ,
[MonthName] ,
[YearKey] ,
[YearName] ,
[MonthOfYearKey] ,
[MonthOfYearName]
)
values
(
– month
datepart(year, @CurrentDate ) * 100 + datepart(month, @CurrentDate ),
convert(varchar(4), datepart(year, @CurrentDate )) + ‘ ‘ +
‘M ‘ + convert(char(2), datepart(month, @CurrentDate )),
– year
datepart(year, @CurrentDate ),
convert(varchar(4), datepart(year, @CurrentDate )),
– month of year
datepart(month, @CurrentDate ),
datename(month, @CurrentDate )
)
set @TimeID = @TimeID + 1
set @CurrentDate = dateadd( month, 1, @CurrentDate )
end
END
Tweak some options (see comments).
Execute it with your parameters (span of dates that your data lies between or greater. Or, put two dates for span directly into stored procedure as parameters’ defaults in case you prefer that way (I made defaults for year 2008).
