Calendar table for Data Warehouse

Question

For my data warehouse, I am creating a calendar table as follows:

SET NOCOUNT ON

DROP Table dbo.Calendar
GO
Create Table dbo.Calendar
(
    CalendarId              Integer NOT NULL,
    DateValue               Date    NOT NULL,
    DayNumberOfWeek         Integer NOT NULL,
    NameOfDay               VarChar (10) NOT NULL,
    NameOfMonth             VarChar (10) NOT NULL,
    WeekOfYear              Integer NOT NULL,
    JulianDay               Integer NOT NULL,
    USAIsBankHoliday        Bit     NOT NULL,
    USADayName              VarChar (100) NULL,
)
ALTER TABLE dbo.Calendar ADD CONSTRAINT
    DF_Calendar_USAIsBankHoliday DEFAULT 0 FOR USAIsBankHoliday
GO
ALTER TABLE dbo.Calendar ADD CONSTRAINT
    DF_Calendar_USADayName DEFAULT '' FOR USADayName
GO

Declare @StartDate  DateTime = '01/01/2000'
Declare @EndDate    DateTime = '01/01/2020'

While @StartDate < @EndDate
Begin
    INSERT INTO dbo.Calendar 
    (
        CalendarId, 
        DateValue, 
        WeekOfYear,
        DayNumberOfWeek,
        NameOfDay,
        NameOfMonth,
        JulianDay
    )
    Values 
    (
        YEAR (@StartDate) * 10000 + MONTH (@StartDate) * 100 + Day (@StartDate), --CalendarId
        @StartDate,                 -- DateValue
        DATEPART (ww, @StartDate),  -- WeekOfYear
        DATEPART (dw, @StartDate),  -- DayNumberOfWeek
        DATENAME (dw, @StartDate),  -- NameOfDay
        DATENAME (M, @StartDate),   -- NameOfMonth
        DATEPART (dy, @StartDate)   -- JulianDay
    )

    Set @StartDate += 1
End

--=========================== Weekends
-- saturday and sunday
UPDATE dbo.Calendar SET USAIsBankHoliday = 1, USADayName += 'Weekend, ' WHERE DayNumberOfWeek IN (1, 7) 


--=========================== Bank Holidays
-- new years day
UPDATE dbo.Calendar SET USAIsBankHoliday = 1, USADayName += 'New Year''s Day, '  WHERE (CalendarId % 2000) IN (101)

-- memorial day (last Monday in May)
UPDATE dbo.Calendar 
SET USAIsBankHoliday = 1, 
    USADayName += 'Memorial Day, '  
WHERE 1=1
AND CalendarId IN 
    (
        SELECT MAX (CalendarId)
        FROM dbo.Calendar 
        WHERE MONTH (DateValue) = 5 
        AND DATEPART (DW, DateValue)=2
        GROUP BY YEAR (datevalue)
    )

-- independence day
UPDATE dbo.Calendar SET USAIsBankHoliday = 1, USADayName += 'Independence Day, '  WHERE (CalendarId % 2000) IN (704)

-- labor day (first Monday in September)
UPDATE dbo.Calendar 
SET USAIsBankHoliday = 1, 
    USADayName += 'Labor Day, '  
WHERE 1=1
AND CalendarId IN 
    (
        SELECT MIN (CalendarId)
        FROM dbo.Calendar 
        WHERE MONTH (DateValue) = 9
        AND DATEPART (DW, DateValue)=2
        GROUP BY YEAR (datevalue)
    )

-- thanksgiving day (fourth Thursday in November)
UPDATE dbo.Calendar 
SET USAIsBankHoliday = 1, 
    USADayName += 'Thanksgiving Day, '  
WHERE 1=1
AND CalendarId IN 
    (
        SELECT Max (CalendarId)-2
        FROM dbo.Calendar 
        WHERE MONTH (DateValue) = 11
        AND DATEPART (DW, DateValue)=7
        GROUP BY YEAR (datevalue)
    )

-- christmas
UPDATE dbo.Calendar SET USAIsBankHoliday = 1, USADayName += 'Christmas Day, '  WHERE (CalendarId % 2000) IN (1225)

--=========================== Other named days
-- new years eve
UPDATE dbo.Calendar SET USADayName += 'New Year''s Eve, '  WHERE (CalendarId % 2000) IN (1231)

-- black friday (day after thanksgiving day)
UPDATE dbo.Calendar SET USADayName += 'Black Friday, '  WHERE CalendarId IN (SELECT CalendarId+1 From dbo.Calendar Where USADayName like '%Thanksgiving%')

-- christmas eve
UPDATE dbo.Calendar SET USADayName += 'Christmas Eve, '  WHERE (CalendarId % 2000) IN (1224)

-- boxing day
UPDATE dbo.Calendar SET USADayName += 'Boxing Day, '  WHERE (CalendarId % 2000) IN (1226)

--=========================== Remove trailing comma
UPDATE dbo.Calendar SET USADayName = SubString (USADayName, 1, LEN (USADayName) -1) WHERE LEN (USADayName) > 2

SELECT * FROM dbo.Calendar

Here is the output of this command

I have seen similar structures implemented in various flavours by data architects.

My question is: What other data warehousing / dimension style useful information can I add to this table structure?

Answer 1

• Quarter
• Year
• Financial/Accounting Year
• Financial/Accounting Quarter
• isWeekend
• isWeekday
• isWorkDay
• WeekId (weeks since start of year)
• isLastDayofMonth
• DaysSince (e.g. days since 1/1/2000)

Answer 2

This is my list of possible columns in calendar dimension:

• Key
• Date
• Is Yesterday
• Is Today
• Is Tomorrow
• Day of Year
• Day of Halfyear
• Day of Quarter
• Day of Month
• Day of Week
• Day of Week Short Name
• Day of Week Short Name CS
• Day of Week Long Name
• Day of Week Long Name CS
• Days in Week
• Days in Month
• Days in Quarter
• Days in Halfyear
• Days in Year
• Reverse Day of Week
• Reversse Day of Month
• Reverse Day of Quarter
• Reverse Day of Halfyear
• Reverse Day of Year
• Is Last 7 days
• Is Last 14 days
• Is Last 30 days
• Is Last 90 Days
• Is Last 180 Days
• Is Last 365 Days
• Is Weekday
• Is Weekend
• Workday of Week
• Workday of Month
• Workday of Quarter
• Workday of Halfyear
• Workday of Year
• Reverse Workday of Week
• Reverse Workday of Month
• Reverse Workday of Quarter
• Reverse Workday of Halfyear
• Reverse Workday of Year
• Workdays in Week
• Workdays in Month
• Workdays in Quarter
• Workdays in Halfyear
• Workdays in Year
• Is Last Workday in Week
• Is Last Workday in Month
• Is Workday
• Is Holiday
• Is Future
• Is Past
• Is Previous Month
• Is Current Month
• Is Following Month
• Is Month to Date
• Is Beginning of Month
• Is End of Month
• Is Past Month
• Beginning of Month
• End of Month
• Month Number
• Month Name Long
• Month Name Long CS
• Month Name Short
• Month Name Short CS
• Month of Quarter
• Month of Halfyear
• Is Previous Week
• Is Current Week
• Is Following Week
• Is Week to Date
• Is Beginning of Week
• Is End of Week
• Is Past Week
• Beginning of Week
• End of Week
• Week Number
• Week Name Long
• Week Name Short
• Week of Month
• Is Previous Quarter
• Is Current Quarter
• Is Following Quarter
• Is Quarter to Date
• Is Beginning of Quarter
• Is End of Quarter
• Is Past Quarter
• Beginning of Quarter
• End of Quarter
• Quarter Number
• Quarter Name Long
• Quarter Name Long CS
• Quarter Name Short
• Is Previous Halfyear
• Is Current Halfyear
• Is Following Halfyear
• Is Halfyear to Date
• Is Beginning of Halfyear
• Is End of Halfyear
• Is Past Halfyear
• Beginning of Halfyear
• End of Halfyear
• Halfyear Number
• Halfyear Name Long
• Halfyear Name Long CS
• Halfyear Name Short
• Is Previous Year
• Is Current Year
• Is Following Year
• Is Year to Date
• Is Beginning of Year
• Is End of Year
• Is Past Year
• Beginning of Year
• End of Year
• Year Number
• Year Name Long
• Year Name Short
• Year Quarter Text
• Year Month Day
• Year Halfyear
• Year Quarter
• Year Month
• Year Day of Year
• Is Leap Year
• Distance in Days from Today
• Distance in Working Days from Today
• Distance in Calendar Weeks from Today
• Distance in Calendar Months from Today
• Distance in Calendar Quarters from Today
• Distance in Calendar Halfyears from Today
• Distance in Calendar Years from Today
• Nth Day of Week in Month
• Reverse Nth Day of Week in Month

I created interactive spreadsheet where you can create your own time dimension for PostgreSQL database.