Search
  • Tim Burns

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.




2 views0 comments

Recent Posts

See All

Here is the ticket master public API. https://developer.ticketmaster.com/ Could be a very interesting edition to cross-reference with Playlist info to get hot concerts that may be under the radar.

Elon Musk embodies the worst aspects of the tech bro spirit. He has no moral compass. He lacks compassion. He elevates the vilest voices in our world. He is not the kind of person I would want to