Script for populating Date Dimension with Financial Year

Once I published the code I used for creating date dimension tables and populating data. But it had not handled finance year related date elements. Generally we handle financial year elements with Date Table with Star Schema not with Snowflake Schema. And in most cases (or old days), we usually use OLAP Data Warehouse  as the source for reporting and Analysis Services can easily handle financial dates with Start Schema implemented in the Relational Data Warehouse. However, in modern world, we try to make the solution just using the Relation Data Warehouse without using Multi-dimensional models (or using Tabular Models), thought make a another script for handling financial dates with Snowflake Schema.

With this script, four tables are created: Year, Quarter, Month and Date. If need to see elements related to the financial year, the link between Year and Quarter should be set with Year.YearKey and Quarter.FinancialYearKey. For calendar date elements, the link should be between Year.YearKey and Quarter.YearKey.

This is how you make a search for financial dates;

This is how you make a search for Calendar dates;

Here is the script for creating tables;

view plainprint?

1. CREATE TABLE dbo.DimYear  
2. (  
3.  YearKey smallint   
4.  , Year smallint not null  
5.  , Constraint pk_DimYear Primary Key (YearKey)  
6. );  
7. GO  
8.   
9. CREATE TABLE dbo.DimQuarter  
10. (  
11.  QuarterKey smallint   
12.  , YearKey smallint not null  
13.  , FinancialYearKey smallint not null  
14.  , Quarter smallint not null  
15.  , YearQuater varchar(20) not null  
16.  , QuarterName varchar(20) not null  
17.  , FinancialQuarter smallint not null  
18.  , FinancialYearQuarter varchar(20) not null  
19.  , Constraint pk_DimQuarter Primary Key (QuarterKey)  
20.  , Constraint fk_DimQuarter_DimYear Foreign Key (YearKey)  
21.   References dbo.DimYear (YearKey)  
22.  , Constraint fk_DimQuarter_DimYear_Financial Foreign Key (FinancialYearKey)  
23.   References dbo.DimYear (YearKey)  
24. );  
25. GO  
26.   
27. CREATE TABLE dbo.DimMonth  
28. (  
29.  MonthKey int   
30.  , QuarterKey smallint not null  
31.  , MonthNumber smallint not null  
32.  , MonthName varchar(20) not null  
33.  , YearMonth varchar(20) not null  
34.  , MonthShortName char(3) not null  
35.  , FinancialMonthNumber smallint not null  
36.  , Constraint pk_DimMonth Primary Key (MonthKey)  
37.  , Constraint fk_DimMonth_DimQuarter Foreign Key (QuarterKey)  
38.   References dbo.DimQuarter (QuarterKey)  
39. );  
40. GO  
41.   
42. CREATE TABLE dbo.DimDate  
43. (  
44.  DateKey int   
45.  , MonthKey int not null  
46.  , Date date not null  
47.  , WeekDayNumber smallint not null  
48.  , WeekDayName varchar(20) not null  
49.  , DayOfMonth smallint not null  
50.  , DayOfYear smallint not null  
51.  , IsWeekend bit not null  
52.  , IsHoliday bit not null  
53.  , WeekNumberOfYear smallint not null  
54.  , Constraint pk_DimDate Primary Key (DateKey)  
55.  , Constraint fk_DimDate_DimMonth Foreign Key (MonthKey)  
56.   References dbo.DimMonth (MonthKey)  
57. );  
58. GO  

CREATE TABLE dbo.DimYear
(
 YearKey smallint 
 , Year smallint not null
 , Constraint pk_DimYear Primary Key (YearKey)
);
GO

CREATE TABLE dbo.DimQuarter
(
 QuarterKey smallint 
 , YearKey smallint not null
 , FinancialYearKey smallint not null
 , Quarter smallint not null
 , YearQuater varchar(20) not null
 , QuarterName varchar(20) not null
 , FinancialQuarter smallint not null
 , FinancialYearQuarter varchar(20) not null
 , Constraint pk_DimQuarter Primary Key (QuarterKey)
 , Constraint fk_DimQuarter_DimYear Foreign Key (YearKey)
  References dbo.DimYear (YearKey)
 , Constraint fk_DimQuarter_DimYear_Financial Foreign Key (FinancialYearKey)
  References dbo.DimYear (YearKey)
);
GO

CREATE TABLE dbo.DimMonth
(
 MonthKey int 
 , QuarterKey smallint not null
 , MonthNumber smallint not null
 , MonthName varchar(20) not null
 , YearMonth varchar(20) not null
 , MonthShortName char(3) not null
 , FinancialMonthNumber smallint not null
 , Constraint pk_DimMonth Primary Key (MonthKey)
 , Constraint fk_DimMonth_DimQuarter Foreign Key (QuarterKey)
  References dbo.DimQuarter (QuarterKey)
);
GO

CREATE TABLE dbo.DimDate
(
 DateKey int 
 , MonthKey int not null
 , Date date not null
 , WeekDayNumber smallint not null
 , WeekDayName varchar(20) not null
 , DayOfMonth smallint not null
 , DayOfYear smallint not null
 , IsWeekend bit not null
 , IsHoliday bit not null
 , WeekNumberOfYear smallint not null
 , Constraint pk_DimDate Primary Key (DateKey)
 , Constraint fk_DimDate_DimMonth Foreign Key (MonthKey)
  References dbo.DimMonth (MonthKey)
);
GO

Here is the script for populating dates;

view plainprint?

1. SET DATEFIRST 1;  
2.   
3. DECLARE @StartDate date = '1990-01-01';  
4. DECLARE @EndDate date = '2019-12-31';  
5. DECLARE @FinancialYearStartingQuarter smallint = 3; -- Starting from July, If it starts from April then 2  
6.   
7. DECLARE @YearKey smallint;  
8. DECLARE @Quarter smallint, @QuarterKey smallint;  
9. DECLARE @Month int, @MonthKey int;  
10. DECLARE @Date int, @DateKey int;  
11.   
12. DECLARE @FinancialStartingMonth smallint;  
13. SET @FinancialStartingMonth = CASE @FinancialYearStartingQuarter WHEN 1 THEN 1 WHEN 2 THEN 4 WHEN 3 THEN 7 WHEN 4 THEN 10 END  
14.   
15. INSERT INTO dbo.DimYear   
16.  (YearKey, [Year])   
17. VALUES   
18.  (YEAR(@StartDate) - 1, YEAR(@StartDate) - 1);  
19.   
20.   
21. WHILE (@StartDate <= @EndDate)  
22. BEGIN  
23.   
24.  -- Inserting years  
25.  SET @YearKey = YEAR(@StartDate);  
26.  IF NOT EXISTS (SELECT * FROM dbo.DimYear WHERE YearKey = @YearKey)  
27.   INSERT INTO dbo.DimYear (YearKey, [Year])   
28.   VALUES   
29.   (@YearKey, @YearKey);  
30.   
31.   
32.  -- Inserting quarters  
33.  SET @QuarterKey = Convert(smallint, Convert(varchar(4), YEAR(@StartDate)) + Convert(varchar(1), DATEPART(q, @StartDate)))  
34.  SET @Quarter = DATEPART(q, @StartDate);  
35.   
36.  IF NOT EXISTS (SELECT * FROM dbo.DimQuarter WHERE QuarterKey = @QuarterKey)  
37.  INSERT INTO dbo.DimQuarter   
38.   (QuarterKey, YearKey  
39.   , FinancialYearKey  
40.   , [Quarter], YearQuater, QuarterName  
41.   , FinancialQuarter, FinancialYearQuarter)   
42.  VALUES (@QuarterKey, @YearKey  
43.   , CASE WHEN @Quarter < @FinancialYearStartingQuarter THEN @YearKey -1 ELSE @YearKey END  
44.   , @Quarter, Convert(varchar(4), YEAR(@StartDate)) + 'Q' + Convert(varchar(4), @Quarter)  
45.   , CASE @Quarter WHEN 1 THEN 'First Quarter' WHEN 2 THEN 'Second Quarter' WHEN 3 THEN 'Third Quarter' ELSE 'Forth Quarter' END  
46.   , CASE @Quarter WHEN 1 THEN 3 WHEN 2 THEN 4 WHEN 3 THEN 1 WHEN 4 THEN 2 END   
47.   , CASE @Quarter WHEN 1 THEN Convert(varchar(4), YEAR(@StartDate) - 1) + 'Q3'  
48.       WHEN 2 THEN Convert(varchar(4), YEAR(@StartDate) - 1) + 'Q4'  
49.       WHEN 3 THEN Convert(varchar(4), YEAR(@StartDate)) + 'Q1'  
50.       WHEN 4 THEN Convert(varchar(4), YEAR(@StartDate)) + 'Q2'END  
51.   );  
52.     
53.   
54.  ---- Inserting months  
55.  SET @MonthKey = Convert(int, Convert(varchar(4), YEAR(@StartDate)) + (CASE LEN(MONTH(@StartDate)) WHEN 1 THEN '0' ELSE '' END) + Convert(varchar(2), MONTH(@StartDate)));  
56.  SET @Month = MONTH(@StartDate)  
57.  IF NOT EXISTS (SELECT * FROM dbo.DimMonth WHERE MonthKey = @MonthKey)  
58.   INSERT INTO dbo.DimMonth   
59.    (MonthKey, QuarterKey, MonthNumber, MonthName, YearMonth, MonthShortName  
60.    , FinancialMonthNumber)   
61.   VALUES   
62.    (@MonthKey, @QuarterKey, @Month, DATENAME(MONTH, @StartDate)  
63.    , Convert(varchar(4), YEAR(@StartDate)) + ' ' + LEFT(DATENAME(MONTH, @StartDate), 3)  
64.    , LEFT(DATENAME(MONTH, @StartDate), 3)  
65.    , CASE   
66.      WHEN @FinancialStartingMonth = 1 THEN @Month  
67.      WHEN @FinancialStartingMonth = 4 AND @Month  < @FinancialStartingMonth THEN @Month + 9  
68.      WHEN @FinancialStartingMonth = 4 AND @Month  >= @FinancialStartingMonth THEN @Month - 3  
69.      WHEN @FinancialStartingMonth = 7 AND @Month  < @FinancialStartingMonth THEN @Month + 6  
70.      WHEN @FinancialStartingMonth = 7 AND @Month  >= @FinancialStartingMonth THEN @Month - 6  
71.      WHEN @FinancialStartingMonth = 10 AND @Month  < @FinancialStartingMonth THEN @Month + 6  
72.      WHEN @FinancialStartingMonth = 10 AND @Month  >= @FinancialStartingMonth THEN @Month + 3  
73.     END  
74.    );  
75.     
76.   ---- Inserting dates  
77.   SET @DateKey = Convert(int, Convert(varchar(8), @StartDate, 112))  
78.   IF NOT EXISTS (SELECT * FROM dbo.DimDate WHERE DateKey = @DateKey)  
79.    INSERT INTO dbo.DimDate (DateKey, MonthKey, Date, WeekDayNumber, WeekDayName, DayOfMonth  
80.     , DayOfYear, IsWeekend, IsHoliday, WeekNumberOfYear)   
81.    VALUES (@DateKey, @MonthKey, @StartDate, DatePart(WEEKDAY, @StartDate), DateName(WEEKDAY, @StartDate), DatePart(DAY, @StartDate)  
82.     , DatePart(DAYOFYEAR, @StartDate), CASE WHEN DatePart(WEEKDAY, @StartDate) IN (6, 7) THEN 1 ELSE 0 END, 0  
83.     , DatePart(WEEK, @StartDate));  
84.   
85.   SET @StartDate = DATEADD(dd, 1, @StartDate);  
86. END;  
87. GO  

SET DATEFIRST 1;

DECLARE @StartDate date = '1990-01-01';
DECLARE @EndDate date = '2019-12-31';
DECLARE @FinancialYearStartingQuarter smallint = 3; -- Starting from July, If it starts from April then 2

DECLARE @YearKey smallint;
DECLARE @Quarter smallint, @QuarterKey smallint;
DECLARE @Month int, @MonthKey int;
DECLARE @Date int, @DateKey int;

DECLARE @FinancialStartingMonth smallint;
SET @FinancialStartingMonth = CASE @FinancialYearStartingQuarter WHEN 1 THEN 1 WHEN 2 THEN 4 WHEN 3 THEN 7 WHEN 4 THEN 10 END

INSERT INTO dbo.DimYear 
 (YearKey, [Year]) 
VALUES 
 (YEAR(@StartDate) - 1, YEAR(@StartDate) - 1);


WHILE (@StartDate <= @EndDate)
BEGIN

 -- Inserting years
 SET @YearKey = YEAR(@StartDate);
 IF NOT EXISTS (SELECT * FROM dbo.DimYear WHERE YearKey = @YearKey)
  INSERT INTO dbo.DimYear (YearKey, [Year]) 
  VALUES 
  (@YearKey, @YearKey);


 -- Inserting quarters
 SET @QuarterKey = Convert(smallint, Convert(varchar(4), YEAR(@StartDate)) + Convert(varchar(1), DATEPART(q, @StartDate)))
 SET @Quarter = DATEPART(q, @StartDate);

 IF NOT EXISTS (SELECT * FROM dbo.DimQuarter WHERE QuarterKey = @QuarterKey)
 INSERT INTO dbo.DimQuarter 
  (QuarterKey, YearKey
  , FinancialYearKey
  , [Quarter], YearQuater, QuarterName
  , FinancialQuarter, FinancialYearQuarter) 
 VALUES (@QuarterKey, @YearKey
  , CASE WHEN @Quarter < @FinancialYearStartingQuarter THEN @YearKey -1 ELSE @YearKey END
  , @Quarter, Convert(varchar(4), YEAR(@StartDate)) + 'Q' + Convert(varchar(4), @Quarter)
  , CASE @Quarter WHEN 1 THEN 'First Quarter' WHEN 2 THEN 'Second Quarter' WHEN 3 THEN 'Third Quarter' ELSE 'Forth Quarter' END
  , CASE @Quarter WHEN 1 THEN 3 WHEN 2 THEN 4 WHEN 3 THEN 1 WHEN 4 THEN 2 END 
  , CASE @Quarter WHEN 1 THEN Convert(varchar(4), YEAR(@StartDate) - 1) + 'Q3'
      WHEN 2 THEN Convert(varchar(4), YEAR(@StartDate) - 1) + 'Q4'
      WHEN 3 THEN Convert(varchar(4), YEAR(@StartDate)) + 'Q1'
      WHEN 4 THEN Convert(varchar(4), YEAR(@StartDate)) + 'Q2'END
  );
  

 ---- Inserting months
 SET @MonthKey = Convert(int, Convert(varchar(4), YEAR(@StartDate)) + (CASE LEN(MONTH(@StartDate)) WHEN 1 THEN '0' ELSE '' END) + Convert(varchar(2), MONTH(@StartDate)));
 SET @Month = MONTH(@StartDate)
 IF NOT EXISTS (SELECT * FROM dbo.DimMonth WHERE MonthKey = @MonthKey)
  INSERT INTO dbo.DimMonth 
   (MonthKey, QuarterKey, MonthNumber, MonthName, YearMonth, MonthShortName
   , FinancialMonthNumber) 
  VALUES 
   (@MonthKey, @QuarterKey, @Month, DATENAME(MONTH, @StartDate)
   , Convert(varchar(4), YEAR(@StartDate)) + ' ' + LEFT(DATENAME(MONTH, @StartDate), 3)
   , LEFT(DATENAME(MONTH, @StartDate), 3)
   , CASE 
     WHEN @FinancialStartingMonth = 1 THEN @Month
     WHEN @FinancialStartingMonth = 4 AND @Month  < @FinancialStartingMonth THEN @Month + 9
     WHEN @FinancialStartingMonth = 4 AND @Month  >= @FinancialStartingMonth THEN @Month - 3
     WHEN @FinancialStartingMonth = 7 AND @Month  < @FinancialStartingMonth THEN @Month + 6
     WHEN @FinancialStartingMonth = 7 AND @Month  >= @FinancialStartingMonth THEN @Month - 6
     WHEN @FinancialStartingMonth = 10 AND @Month  < @FinancialStartingMonth THEN @Month + 6
     WHEN @FinancialStartingMonth = 10 AND @Month  >= @FinancialStartingMonth THEN @Month + 3
    END
   );
  
  ---- Inserting dates
  SET @DateKey = Convert(int, Convert(varchar(8), @StartDate, 112))
  IF NOT EXISTS (SELECT * FROM dbo.DimDate WHERE DateKey = @DateKey)
   INSERT INTO dbo.DimDate (DateKey, MonthKey, Date, WeekDayNumber, WeekDayName, DayOfMonth
    , DayOfYear, IsWeekend, IsHoliday, WeekNumberOfYear) 
   VALUES (@DateKey, @MonthKey, @StartDate, DatePart(WEEKDAY, @StartDate), DateName(WEEKDAY, @StartDate), DatePart(DAY, @StartDate)
    , DatePart(DAYOFYEAR, @StartDate), CASE WHEN DatePart(WEEKDAY, @StartDate) IN (6, 7) THEN 1 ELSE 0 END, 0
    , DatePart(WEEK, @StartDate));

  SET @StartDate = DATEADD(dd, 1, @StartDate);
END;
GO