Date dimension plays an important role in your data warehouse designing, it provides the ability to study behavior and trend of your data over a period of time.
You can study your data by grouping them using various fields of date dimension.
For example:
If I want to analyze my data of total sales by each month of the year, or show total sales by each quarter of the year, or show me on which days total sales takes place more in the entire year or month.
After implementing the complete solution in data warehouse, the relationship of date dimension gives you all this facility to do slice and dice of your data.
So as an initial step, you need to design your date dimension, time dimension and populate them with range of values.
For designing of time dimension, you can refer to my other tip posted on CodeProject, “Design and Populate Time Dimension with 24 Hour plus Values”.
This date dimension will have values of date stored as per various date formats used across the world, like “dd-MM-yyyy” is used in Europe, UK, India, etc. while “MM-dd-yyyy” format is used in US.
DECLARE @StartDate DATETIME = '01/01/2013' DECLARE @EndDate DATETIME = '01/01/2015'
DECLARE
@DayOfWeekInMonth INT,
@DayOfWeekInYear INT,
@DayOfQuarter INT,
@WeekOfMonth INT,
@CurrentYear INT,
@CurrentMonth INT,
@CurrentQuarter INT
DECLARE @DayOfWeek TABLE (DOW INT, MonthCount INT, QuarterCount INT, YearCount INT)
INSERT INTO @DayOfWeek VALUES (1, 0, 0, 0)
INSERT INTO @DayOfWeek VALUES (2, 0, 0, 0)
INSERT INTO @DayOfWeek VALUES (3, 0, 0, 0)
INSERT INTO @DayOfWeek VALUES (4, 0, 0, 0)
INSERT INTO @DayOfWeek VALUES (5, 0, 0, 0)
INSERT INTO @DayOfWeek VALUES (6, 0, 0, 0)
INSERT INTO @DayOfWeek VALUES (7, 0, 0, 0)
DECLARE @CurrentDate AS DATETIME = @StartDate
SET @CurrentMonth = DATEPART(MM, @CurrentDate)
SET @CurrentYear = DATEPART(YY, @CurrentDate)
SET @CurrentQuarter = DATEPART(QQ, @CurrentDate)
WHILE @CurrentDate < @EndDate
BEGIN
IF @CurrentMonth != DATEPART(MM, @CurrentDate)
BEGIN
UPDATE @DayOfWeek
SET MonthCount = 0
SET @CurrentMonth = DATEPART(MM, @CurrentDate)
END
IF @CurrentQuarter != DATEPART(QQ, @CurrentDate)
BEGIN
UPDATE @DayOfWeek
SET QuarterCount = 0
SET @CurrentQuarter = DATEPART(QQ, @CurrentDate)
END
IF @CurrentYear != DATEPART(YY, @CurrentDate)
BEGIN
UPDATE @DayOfWeek
SET YearCount = 0
SET @CurrentYear = DATEPART(YY, @CurrentDate)
END
UPDATE @DayOfWeek
SET
MonthCount = MonthCount + 1,
QuarterCount = QuarterCount + 1,
YearCount = YearCount + 1
WHERE DOW = DATEPART(DW, @CurrentDate)
SELECT
@DayOfWeekInMonth = MonthCount,
@DayOfQuarter = QuarterCount,
@DayOfWeekInYear = YearCount
FROM @DayOfWeek
WHERE DOW = DATEPART(DW, @CurrentDate)
INSERT INTO [dbo].[DimDate]
SELECT
CONVERT (char(8),@CurrentDate,112) as DateKey,
@CurrentDate AS Date,
CONVERT (char(10),@CurrentDate,103) as FullDateUK,
CONVERT (char(10),@CurrentDate,101) as FullDateUSA,
DATEPART(DD, @CurrentDate) AS DayOfMonth,
CASE
WHEN DATEPART(DD,@CurrentDate) IN (11,12,13) _
THEN CAST(DATEPART(DD,@CurrentDate) AS VARCHAR) + 'th'
WHEN RIGHT(DATEPART(DD,@CurrentDate),1) = 1 _
THEN CAST(DATEPART(DD,@CurrentDate) AS VARCHAR) + 'st'
WHEN RIGHT(DATEPART(DD,@CurrentDate),1) = 2 _
THEN CAST(DATEPART(DD,@CurrentDate) AS VARCHAR) + 'nd'
WHEN RIGHT(DATEPART(DD,@CurrentDate),1) = 3 _
THEN CAST(DATEPART(DD,@CurrentDate) AS VARCHAR) + 'rd'
ELSE CAST(DATEPART(DD,@CurrentDate) AS VARCHAR) + 'th'
END AS DaySuffix,
DATENAME(DW, @CurrentDate) AS DayName,
DATEPART(DW, @CurrentDate) AS DayOfWeekUSA,
CASE DATEPART(DW, @CurrentDate)
WHEN 1 THEN 7
WHEN 2 THEN 1
WHEN 3 THEN 2
WHEN 4 THEN 3
WHEN 5 THEN 4
WHEN 6 THEN 5
WHEN 7 THEN 6
END
AS DayOfWeekUK,
@DayOfWeekInMonth AS DayOfWeekInMonth,
@DayOfWeekInYear AS DayOfWeekInYear,
@DayOfQuarter AS DayOfQuarter,
DATEPART(DY, @CurrentDate) AS DayOfYear,
DATEPART(WW, @CurrentDate) + 1 - DATEPART(WW, CONVERT(VARCHAR, _
DATEPART(MM, @CurrentDate)) + '/1/' + CONVERT(VARCHAR, _
DATEPART(YY, @CurrentDate))) AS WeekOfMonth,
(DATEDIFF(DD, DATEADD(QQ, DATEDIFF(QQ, 0, @CurrentDate), 0), _
@CurrentDate) / 7) + 1 AS WeekOfQuarter,
DATEPART(WW, @CurrentDate) AS WeekOfYear,
DATEPART(MM, @CurrentDate) AS Month,
DATENAME(MM, @CurrentDate) AS MonthName,
CASE
WHEN DATEPART(MM, @CurrentDate) IN (1, 4, 7, 10) THEN 1
WHEN DATEPART(MM, @CurrentDate) IN (2, 5, 8, 11) THEN 2
WHEN DATEPART(MM, @CurrentDate) IN (3, 6, 9, 12) THEN 3
END AS MonthOfQuarter,
DATEPART(QQ, @CurrentDate) AS Quarter,
CASE DATEPART(QQ, @CurrentDate)
WHEN 1 THEN 'First'
WHEN 2 THEN 'Second'
WHEN 3 THEN 'Third'
WHEN 4 THEN 'Fourth'
END AS QuarterName,
DATEPART(YEAR, @CurrentDate) AS Year,
'CY ' + CONVERT(VARCHAR, DATEPART(YEAR, @CurrentDate)) AS YearName,
LEFT(DATENAME(MM, @CurrentDate), 3) + '-' + CONVERT(VARCHAR, _
DATEPART(YY, @CurrentDate)) AS MonthYear,
RIGHT('0' + CONVERT(VARCHAR, DATEPART(MM, @CurrentDate)),2) + _
CONVERT(VARCHAR, DATEPART(YY, @CurrentDate)) AS MMYYYY,
CONVERT(DATETIME, CONVERT(DATE, DATEADD(DD, - (DATEPART(DD, _
@CurrentDate) - 1), @CurrentDate))) AS FirstDayOfMonth,
CONVERT(DATETIME, CONVERT(DATE, DATEADD(DD, - (DATEPART(DD, _
(DATEADD(MM, 1, @CurrentDate)))), DATEADD(MM, 1, _
@CurrentDate)))) AS LastDayOfMonth,
DATEADD(QQ, DATEDIFF(QQ, 0, @CurrentDate), 0) AS FirstDayOfQuarter,
DATEADD(QQ, DATEDIFF(QQ, -1, @CurrentDate), -1) AS LastDayOfQuarter,
CONVERT(DATETIME, '01/01/' + CONVERT(VARCHAR, DATEPART(YY, _
@CurrentDate))) AS FirstDayOfYear,
CONVERT(DATETIME, '12/31/' + CONVERT(VARCHAR, DATEPART(YY, _
@CurrentDate))) AS LastDayOfYear,
NULL AS IsHolidayUSA,
CASE DATEPART(DW, @CurrentDate)
WHEN 1 THEN 0
WHEN 2 THEN 1
WHEN 3 THEN 1
WHEN 4 THEN 1
WHEN 5 THEN 1
WHEN 6 THEN 1
WHEN 7 THEN 0
END AS IsWeekday,
NULL AS HolidayUSA, Null, Null
SET @CurrentDate = DATEADD(DD, 1, @CurrentDate)
END
Step 3.
Update Values of Holiday as per UK Government Declaration for National Holiday.
UPDATE [dbo].[DimDate]
SET HolidayUK = 'Good Friday'
WHERE [Month] = 4 AND [DayOfMonth] = 18
UPDATE [dbo].[DimDate]
SET HolidayUK = 'Easter Monday'
WHERE [Month] = 4 AND [DayOfMonth] = 21
UPDATE [dbo].[DimDate]
SET HolidayUK = 'Early May Bank Holiday'
WHERE [Month] = 5 AND [DayOfMonth] = 5
UPDATE [dbo].[DimDate]
SET HolidayUK = 'Spring Bank Holiday'
WHERE [Month] = 5 AND [DayOfMonth] = 26
UPDATE [dbo].[DimDate]
SET HolidayUK = 'Summer Bank Holiday'
WHERE [Month] = 8 AND [DayOfMonth] = 25
UPDATE [dbo].[DimDate]
SET HolidayUK = 'Boxing Day'
WHERE [Month] = 12 AND [DayOfMonth] = 26
UPDATE [dbo].[DimDate]
SET HolidayUK = 'Christmas Day'
WHERE [Month] = 12 AND [DayOfMonth] = 25
UPDATE [dbo].[DimDate]
SET HolidayUK = 'New Year''s Day'
WHERE [Month] = 1 AND [DayOfMonth] = 1
UPDATE [dbo].[DimDate]
SET IsHolidayUK = CASE WHEN HolidayUK IS NULL _
THEN 0 WHEN HolidayUK IS NOT NULL THEN 1 END
Step 4.
Update Values of Holiday as per USA Govt. Declaration for National Holiday.
UPDATE [dbo].[DimDate]
SET HolidayUSA = 'Thanksgiving Day'
WHERE
[Month] = 11
AND [DayOfWeekUSA] = 'Thursday'
AND DayOfWeekInMonth = 4
UPDATE [dbo].[DimDate]
SET HolidayUSA = 'Christmas Day'
WHERE [Month] = 12 AND [DayOfMonth] = 25
UPDATE [dbo].[DimDate]
SET HolidayUSA = 'Independance Day'
WHERE [Month] = 7 AND [DayOfMonth] = 4
UPDATE [dbo].[DimDate]
SET HolidayUSA = 'New Year''s Day'
WHERE [Month] = 1 AND [DayOfMonth] = 1
UPDATE [dbo].[DimDate]
SET HolidayUSA = 'Memorial Day'
FROM [dbo].[DimDate]
WHERE DateKey IN
(
SELECT
MAX(DateKey)
FROM [dbo].[DimDate]
WHERE
[MonthName] = 'May'
AND [DayOfWeekUSA] = 'Monday'
GROUP BY
[Year],
[Month]
)
UPDATE [dbo].[DimDate]
SET HolidayUSA = 'Labor Day'
FROM [dbo].[DimDate]
WHERE DateKey IN
(
SELECT
MIN(DateKey)
FROM [dbo].[DimDate]
WHERE
[MonthName] = 'September'
AND [DayOfWeekUSA] = 'Monday'
GROUP BY
[Year],
[Month]
)
UPDATE [dbo].[DimDate]
SET HolidayUSA = 'Valentine''s Day'
WHERE
[Month] = 2
AND [DayOfMonth] = 14
UPDATE [dbo].[DimDate]
SET HolidayUSA = 'Saint Patrick''s Day'
WHERE
[Month] = 3
AND [DayOfMonth] = 17
UPDATE [dbo].[DimDate]
SET HolidayUSA = 'Martin Luthor King Jr Day'
WHERE
[Month] = 1
AND [DayOfWeekUSA] = 'Monday'
AND [Year] >= 1983
AND DayOfWeekInMonth = 3
UPDATE [dbo].[DimDate]
SET HolidayUSA = 'President''s Day'
WHERE
[Month] = 2
AND [DayOfWeekUSA] = 'Monday'
AND DayOfWeekInMonth = 3
UPDATE [dbo].[DimDate]
SET HolidayUSA = 'Mother''s Day'
WHERE
[Month] = 5
AND [DayOfWeekUSA] = 'Sunday'
AND DayOfWeekInMonth = 2
UPDATE [dbo].[DimDate]
SET HolidayUSA = 'Father''s Day'
WHERE
[Month] = 6
AND [DayOfWeekUSA] = 'Sunday'
AND DayOfWeekInMonth = 3
UPDATE [dbo].[DimDate]
SET HolidayUSA = 'Halloween'
WHERE
[Month] = 10
AND [DayOfMonth] = 31
BEGIN
DECLARE @Holidays TABLE (ID INT IDENTITY(1,1), _
DateID int, Week TINYINT, YEAR CHAR(4), DAY CHAR(2))
INSERT INTO @Holidays(DateID, [Year],[Day])
SELECT
DateKey,
[Year],
[DayOfMonth]
FROM [dbo].[DimDate]
WHERE
[Month] = 11
AND [DayOfWeekUSA] = 'Monday'
ORDER BY
YEAR,
DayOfMonth
DECLARE @CNTR INT, @POS INT, @STARTYEAR INT, @ENDYEAR INT, @MINDAY INT
SELECT
@CURRENTYEAR = MIN([Year])
, @STARTYEAR = MIN([Year])
, @ENDYEAR = MAX([Year])
FROM @Holidays
WHILE @CURRENTYEAR <= @ENDYEAR
BEGIN
SELECT @CNTR = COUNT([Year])
FROM @Holidays
WHERE [Year] = @CURRENTYEAR
SET @POS = 1
WHILE @POS <= @CNTR
BEGIN
SELECT @MINDAY = MIN(DAY)
FROM @Holidays
WHERE
[Year] = @CURRENTYEAR
AND [Week] IS NULL
UPDATE @Holidays
SET [Week] = @POS
WHERE
[Year] = @CURRENTYEAR
AND [Day] = @MINDAY
SELECT @POS = @POS + 1
END
SELECT @CURRENTYEAR = @CURRENTYEAR + 1
END
UPDATE [dbo].[DimDate]
SET HolidayUSA = 'Election Day'
FROM [dbo].[DimDate] DT
JOIN @Holidays HL ON (HL.DateID + 1) = DT.DateKey
WHERE
[Week] = 1
END
UPDATE [dbo].[DimDate]
SET IsHolidayUSA = CASE WHEN HolidayUSA IS NULL THEN 0 WHEN HolidayUSA IS NOT NULL THEN 1 END
Reference: http://www.codeproject.com/Articles/647950/Create-and-Populate-Date-Dimension-for-Data-Wareho
The HTML encoded result looks something like this:
Chandara 
You must be logged in to post a comment.