Creating a Calendar Dimension

Calendars and dates drive our lives; a calendar dimension is essential to most data warehouses.

Pope Gregory introduced the Gregorian Calendar in XIII in 1582. It took over the world, and we take it for granted.

create or replace table WAREHOUSE.DIM_CALENDAR as
with years as (
    select dateadd(day, seq4(), '2010-01-01')::date date
    from table (generator(rowcount => (14 * 365+ 3)))
)
select date
     , date_part(year, date)       year
     , date_part(month, date)      month
     , monthname(date)             month_name
     , date_part(dayofmonth, date) day_of_mon
     , date_part(dayofweek, date)  day_of_week
     , date_part(weekofyear, date) week_of_year
     , date_part(dayofyear, date)  day_of_year
     , date_part(weekday, date)    weekday

     , dayname(date)               dayname
from years;


select min(date), max(date)
from WAREHOUSE.DIM_CALENDAR;

+----------+----------+
|MIN(DATE) |MAX(DATE) |
+----------+----------+
|2010-01-01|2023-12-31|
+----------+----------+

Retailers generally use a 4-4-5 calendar to track inventory. That means four weeks, four weeks, then five weeks or 13 weeks makes a quarter. The fiscal year can then be digested into equal quarters and Owlstart and end on any date.