Stored Procedure to Populate Date Dimension Data

In this post I created a stored procedure to insert data to Date Dimension tables.
I have used four tables to have the Date structure. They are DimYear, DimQuarter, DimMonth and DimDay.

Script used to generate Dimension tables is given below.

CREATE TABLE dbo.DimYear
(
YearKey smallint IDENTITY(1,1) NOT NULL,
[Year] smallint NOT NULL,
CONSTRAINT PK_DimYear PRIMARY KEY (YearKey)

)
GO


CREATE TABLE dbo.DimQuarter
(
QuarterKey smallint IDENTITY(1,1) NOT NULL,
YearKey smallint NOT NULL,
[Quarter] smallint NOT NULL,
QuarterName varchar(20) NOT NULL,
CONSTRAINT PK_DimQuarter PRIMARY KEY (QuarterKey),
CONSTRAINT FK_DimQuarter_DimYear FOREIGN KEY (YearKey)
REFERENCES DimYear (YearKey)

)
GO


CREATE TABLE dbo.DimMonth
(
MonthKey smallint IDENTITY(1,1) NOT NULL,
QuarterKey smallint NOT NULL,
MonthNumber smallint NOT NULL,
[MonthName] varchar(20)NOT NULL,
[Month] varchar(20) NOT NULL,
CONSTRAINT PK_DimMonth PRIMARY KEY (MonthKey),
CONSTRAINT FK_DimMonth_DimQuarter FOREIGN KEY (QuarterKey)
REFERENCES DimQuarter (QuarterKey)

)
GO


CREATE TABLE dbo.DimDay
(
DayKey smallint IDENTITY(1,1) NOT NULL,
MonthKey smallint NOT NULL,
[Day] smallint NOT NULL,
[DayName] varchar(20) NOT NULL,
WeekNoForMonth smallint NOT NULL,
WeekNoForYear smallint NOT NULL,
CONSTRAINT PK_DimDay PRIMARY KEY (DayKey),
CONSTRAINT FK_DimDay_DimMonth FOREIGN KEY (MonthKey)
REFERENCES DimMonth (MonthKey)
)
GO



A Stored Procedure was used to fill those dimension tables. Below I put the script of the Stored Procedure



IF OBJECT_ID('dbo.PopulateDateDimension') IS NOT NULL
DROP PROC dbo.PopulateDateDimension
GO

CREATE PROCEDURE dbo.PopulateDateDimension @Year smallint
AS
BEGIN

DECLARE @StartMonth tinyint --set the current month value
DECLARE @EndMonth tinyint --set the last month for a quarter
DECLARE @YearKey smallint --YearKey value in the DimYear table
DECLARE @Quarter smallint --Quarter value
DECLARE @LastQuarter smallint --Last Quarter value and it is equal to 4
DECLARE @QuarterKey smallint --QuarterKey value in the DimQuarter table
DECLARE @StartDay tinyint --current prossesing date
DECLARE @EndDay tinyint --last date for the month
DECLARE @MonthKey smallint --MonthKey value in the DimMonth table
DECLARE @MonthName varchar --Month Name in the YYYY-Month format
DECLARE @WeekNoForMonth smallint --Value of the processing week for the month
DECLARE @WeekNoForYear smallint --Value of the processing week for the year
DECLARE @WeekDayName varchar(20) --Weekday string (Sunday, Monday,...)

SET @StartMonth = 1
SET @EndMonth = 3
SET @Quarter = 1
SET @LastQuarter=4
SET @StartDay=1
SET @EndDay=31


BEGIN TRY
BEGIN TRANSACTION

IF NOT EXISTS (SELECT * FROM dbo.DimYear WHERE [Year] = @Year) --check whether the processing year is already filled or not
BEGIN

SET @WeekNoForYear=1 -- Starts the week value for the year

INSERT INTO dbo.DimYear (Year) VALUES (@Year)
SELECT @YearKey = SCOPE_IDENTITY() --Get the last inserted YearKey


IF NOT EXISTS(SELECT * FROM dbo.DimQuarter WHERE [Quarter] = @Quarter AND YearKey = @YearKey) --Check whether the quarter value for the given year is exist or not
BEGIN
WHILE @Quarter<=@LastQuarter --check the all the quarter values are inserted or not
BEGIN
INSERT INTO dbo.DimQuarter
(YearKey, Quarter, QuarterName)
VALUES
(@YearKey,@Quarter, CONVERT(varchar(4), @Year) + ' - ' + 'Q'+CONVERT(varchar(4),@Quarter))
SELECT @QuarterKey = SCOPE_IDENTITY() --Get the last inserter QuarterKey



WHILE @StartMonth <= @EndMonth --check all the months are inserted or not
BEGIN



IF NOT EXISTS(SELECT * FROM dbo.DimMonth WHERE MonthNumber = @StartMonth AND QuarterKey = @QuarterKey) --check the processing Month record is inserted or not
INSERT INTO dbo.DimMonth
(QuarterKey,[Month], MonthName, MonthNumber)
VALUES
(@QuarterKey, DATENAME(mm, CONVERT(datetime, CONVERT(varchar(2), @StartMonth) + '/1/2000', 101)),CONVERT(varchar(4), @Year) + ' - ' + DATENAME(mm, CONVERT(datetime, CONVERT(varchar(2), @StartMonth) + '/1/2000', 101)), @StartMonth)

SELECT @MonthKey=SCOPE_IDENTITY() --Get the last inserted MonthKey

SET @StartDay=1

--Set the number of days for the months

IF (@StartMonth=1 OR @StartMonth=3 OR @StartMonth=5 OR @StartMonth=7 OR @StartMonth=8 OR @StartMonth=10 OR @StartMonth=12)
SET @EndDay=31
ELSE IF (@StartMonth=4 OR @StartMonth=6 OR @StartMonth=9 OR @StartMonth=11)
SET @EndDay=30
ELSE
SET @EndDay=28

SET @WeekNoForMonth=1 --set the week no for the month
WHILE @StartDay<=@EndDay --Check all the days are inserted for the Month
BEGIN
IF NOT EXISTS (SELECT * FROM dbo.DimDay WHERE Day=@StartDay AND MonthKey=@StartMonth) --Check whether the processing Day is inserted or not


SET @WeekDayName=CONVERT(varchar(20),DATENAME(WEEKDAY,CONVERT(datetime,CONVERT(varchar(4),@Year)+'-'+CONVERT(varchar(4),@StartMonth)+'-'+CONVERT(varchar(4),@StartDay)))) --Get the processing Day string (Sunday, Monday,...)


INSERT INTO dbo.DimDay
(MonthKey,Day,DayName,WeekNoForMonth, WeekNoForYear)
VALUES
(@MonthKey,@StartDay,CONVERT(varchar(4), @Year)+'/'+CONVERT(varchar(2),@StartMonth)+'/'+CONVERT(varchar(2),@StartDay),@WeekNoForMonth,@WeekNoForYear)

SET @StartDay=@StartDay+1 --Increment the processing day value
IF (@WeekDayName='Sunday') --Check whether the week is completed or not (For this one consider the week is starting from Monday)
BEGIN
SET @WeekNoForMonth=@WeekNoForMonth+1 --If the week is completed increment both the WeekNo values
SET @WeekNoForYear=@WeekNoForYear+1
END


END


SET @StartMonth = @StartMonth + 1 --Increment the Month Value

END
SET @EndMonth=@EndMonth+3 --Increment the end month value for a Quarter
SET @Quarter=@Quarter+1 --Increment the Quarter value
END
END
END
COMMIT TRANSACTION
END TRY
BEGIN CATCH

DECLARE @ErrMsg varchar(1000)
IF @@TRANCOUNT > 0
BEGIN

SELECT @ErrMsg = ERROR_MESSAGE()
ROLLBACK TRANSACTION
PRINT @ErrMsg
END
END CATCH
END