Implementing custom calendars
This recipe explains the implementation of the 4-5-4 calendar (opens in a new tab), a common retail calendar used in the US and Canada. However, the same approach can be used to implement other custom calendars.
Unlike custom time dimension granularities, custom calendars provide more flexibility and can be used when time units have variable lengths, such as the months and quarters in the 4-5-4 calendar. See the custom granularities recipe for more information.
Use case
The 4-5-4 calendar ensures sales comparability between years by dividing the year into months based on a 4 weeks – 5 weeks – 4 weeks format. The layout of the calendar lines up holidays and ensures the same number of Saturdays and Sundays in comparable months. Hence, like days are compared to like days for sales reporting purposes.
Data modeling
The data modeling includes the following steps:
- Create a calendar cube, e.g.,
calendar_454. - Extend it a number of times, so there's one calendar cube for every time dimension in cubes with facts that needs translation to a custom calendar.
- Define joins from your cubes with facts to those calendar cubes, e.g.,
base_orders, and bring relevant calendar attributes as proxy dimensions.
The last two steps require a few lines of code but it can totally be optimized with a Jinja macro if needed.
Calendar table
Consider the following calendar cube. It was generated using a large language model (LLM) and then tested against the official calendar (opens in a new tab). In this example, it's generated on the fly, however, in production, it should be materialized as a table using a data transformation tool:
cubes: - name: calendar_454 public: false sql: | WITH RECURSIVE fiscal_weeks AS ( -- Step 1: Define the start of the fiscal years (Sunday closest to Feb 1st) SELECT year AS fiscal_year, CASE WHEN strftime('%w', date_trunc('week', make_date(year, 2, 1)))::INTEGER <= 3 THEN date_trunc('week', make_date(year, 2, 1)) + INTERVAL 6 DAY ELSE date_trunc('week', make_date(year, 2, 1) + INTERVAL 7 DAY) + INTERVAL 7 DAY END AS week_start, 1 AS week_number, 1 AS month_number, 1 AS month_week_count FROM range(2015, 2031) t(year) UNION ALL -- Step 2: Generate weeks recursively following the 4-5-4 pattern SELECT fiscal_year, week_start + INTERVAL 7 DAY AS week_start, week_number + 1, CASE WHEN month_number = 12 AND ((month_week_count = 4 AND month_number % 3 = 1) OR (month_week_count = 5 AND month_number % 3 = 2) OR (month_week_count = 4 AND month_number % 3 = 0)) THEN 1 WHEN month_week_count = 4 AND (month_number % 3 = 1) THEN month_number + 1 WHEN month_week_count = 5 AND (month_number % 3 = 2) THEN month_number + 1 WHEN month_week_count = 4 AND (month_number % 3 = 0) THEN month_number + 1 ELSE month_number END AS month_number, CASE WHEN month_week_count = 4 AND (month_number % 3 = 1) THEN 1 WHEN month_week_count = 5 AND (month_number % 3 = 2) THEN 1 WHEN month_week_count = 4 AND (month_number % 3 = 0) THEN 1 ELSE month_week_count + 1 END AS month_week_count FROM fiscal_weeks WHERE week_number < 52 OR (week_number = 52 AND (fiscal_year % 5 = 2)) -- Account for 53rd week ) SELECT fiscal_year, week_number, month_number, make_timestamp(fiscal_year, month_number, 1, 0, 0, 0) AS fiscal_month_date, week_start AS week_start_date, make_timestamp(year(week_start + INTERVAL 6 DAY), month(week_start + INTERVAL 6 DAY), day(week_start + INTERVAL 6 DAY), 23, 59, 59.999) AS week_end_date FROM fiscal_weeks ORDER BY fiscal_year, week_number dimensions: - name: retail_year sql: fiscal_year type: number - name: week_number sql: week_number type: number - name: month_number sql: month_number type: number - name: retail_month_date sql: fiscal_month_date type: time - name: week_start_date sql: week_start_date type: time - name: week_end_date sql: week_end_date type: timeAs you can see, this cube defines week_start_date and week_end_date time dimensions as the start and end dates of the retail week. They can be used to join this cube to cubes with facts.
Auxiliary calendar cubes
We will also extend the calendar_454 cube to create auxiliary calendar cubes for three time dimensions that we'd like to translate to the 4-5-4 calendar:
cubes: - name: calendar_454__base_orders__created_at extends: calendar_454 - name: calendar_454__base_orders__completed_at extends: calendar_454Cubes with facts
Finally, we define joins from the base_orders cube to the auxiliary calendar cubes. We also bring the week_number and month_number attributes as proxy dimensions:
cubes: - name: base_orders sql: SELECT * FROM 's3://cube-tutorial/orders.csv' joins: # BEGIN — Joins to calendar tables - name: calendar_454__base_orders__created_at sql: "{CUBE.created_at} BETWEEN {calendar_454__base_orders__created_at.week_start_date} AND {calendar_454__base_orders__created_at.week_end_date}" relationship: many_to_one - name: calendar_454__base_orders__completed_at sql: "{CUBE.completed_at} BETWEEN {calendar_454__base_orders__completed_at.week_start_date} AND {calendar_454__base_orders__completed_at.week_end_date}" relationship: many_to_one # END — Joins to calendar tables dimensions: - name: id sql: id type: number primary_key: true - name: status sql: status type: string # BEGIN — Regular time dimension + ones derived from calendar table - name: created_at sql: "{CUBE}.created_at::TIMESTAMP" type: time - name: created_at_retail_month sql: "{calendar_454__base_orders__created_at.retail_month_date}" type: time - name: created_at_retail_week sql: "{calendar_454__base_orders__created_at.week_number}" type: number - name: completed_at sql: "{CUBE}.completed_at::TIMESTAMP" type: time - name: completed_at_retail_month sql: "{calendar_454__base_orders__completed_at.retail_month_date}" type: time - name: completed_at_retail_week sql: "{calendar_454__base_orders__completed_at.week_number}" type: number # END — Regular time dimension + ones derived from calendar table measures: - name: count type: count - name: completed_count type: count filters: - sql: "{CUBE}.status = 'completed'"Result
Querying this data modal would yield the following result: