Implementing custom calendars

Model a 4-5-4 retail calendar as a calendar cube, overriding the week, month, quarter, and year granularities with pre-calculated columns.

A custom calendar divides the year into periods that do not line up with the Gregorian calendar. This recipe implements the 4-5-4 calendar, a retail calendar common in the US and Canada, as a calendar cube. The same approach applies to any other custom calendar, such as a fiscal one.

Calendar cubes are powered by Tesseract, the next-generation data modeling engine. In versions before v1.7.0, it was not enabled by default. Querying a to-date rolling window over an overridden granularity also requires v1.7.32 or later.

Use case

The 4-5-4 calendar makes sales comparable between years. It divides each retail year into quarters of three months, and each quarter into weeks in a 4 – 5 – 4 pattern, so a retail month is either four or five weeks long. Every month therefore begins on the same weekday and contains the same number of Saturdays and Sundays as its counterpart a year earlier, which is what makes like-for-like sales reporting possible.

Because a retail month varies in length, it cannot be derived arithmetically from a fixed-length interval. It has to be read from a calendar table that states, for every date, which retail period that date belongs to.

Data modeling

The implementation has two parts:

• A calendar cube over the calendar table, where the week, month, quarter, and year granularities are overridden with pre-calculated columns.
• A join from each cube with facts to that calendar cube.

Calendar table

Consider the following calendar table. Every row is a date, and the remaining columns state the retail periods that the date belongs to. In production, generate it with a data transformation tool and materialize it as a table:

date_value	retail_week_begins	retail_month_begins	retail_quarter_begins	retail_year_begins	date_prev_month	date_prev_year
2024-02-04	2024-02-04	2024-02-04	2024-02-04	2024-02-04	2024-01-07	2023-02-05
2024-02-05	2024-02-04	2024-02-04	2024-02-04	2024-02-04	2024-01-08	2023-02-06
…	…	…	…	…	…	…
2024-03-03	2024-03-03	2024-03-03	2024-02-04	2024-02-04	2024-02-04	2023-03-05
…	…	…	…	…	…	…
2024-04-07	2024-04-07	2024-04-07	2024-02-04	2024-02-04	2024-03-03	2023-04-09
…	…	…	…	…	…	…
2024-05-05	2024-05-05	2024-05-05	2024-05-05	2024-02-04	2024-04-07	2023-05-07

The retail year 2024 begins on 2024-02-04. The month beginning on that date is four weeks long, so the next one begins on 2024-03-03; that one is five weeks long, so the third begins on 2024-04-07. Those three months make up the first retail quarter, and the second one begins on 2024-05-05. That 4 – 5 – 4 sequence is exactly what no interval can express, and it is why these dates are pre-calculated rather than computed at query time.

The last two columns hold the date one retail month and one retail year earlier. They are what make time shifts follow the retail calendar as well.

Calendar cube

Set calendar to true on the cube over the calendar table, and override the granularities of its primary_key dimension:

YAML

cubes:
  - name: retail_calendar
    calendar: true
    sql_table: retail_calendar

    dimensions:
      - name: date
        sql: date_value
        type: time
        primary_key: true

        granularities:
          - name: week
            sql: "{CUBE}.retail_week_begins"

          # 4 or 5 weeks long, so no interval can reproduce it
          - name: month
            sql: "{CUBE}.retail_month_begins"

          - name: quarter
            sql: "{CUBE}.retail_quarter_begins"

          - name: year
            sql: "{CUBE}.retail_year_begins"

        time_shift:
          - type: prior
            interval: 1 month
            sql: "{CUBE}.date_prev_month"

          - type: prior
            interval: 1 year
            sql: "{CUBE}.date_prev_year"

Each granularity keeps the name of the default granularity it replaces. A granularity defined with sql must be named after a default one; retail_month would not compile. See naming a granularity defined with sql for the rule and for when to use interval instead.

Override the granularities on the dimension you group by. A calendar cube can expose more than one time dimension, and an override applies only to the dimension it is defined on. A query that groups by a dimension without the override falls back to DATE_TRUNC and returns Gregorian months, with no error. The same is true per granularity: this cube still answers day with DATE_TRUNC, because day is not overridden.

Cubes with facts

Join each cube with facts to the calendar cube on its own time dimension:

YAML

cubes:
  - name: orders
    sql_table: orders

    joins:
      - name: retail_calendar
        sql: "{CUBE}.created_at = {retail_calendar.date}"
        relationship: many_to_one

    dimensions:
      - name: id
        sql: id
        type: number
        primary_key: true

      - name: created_at
        sql: created_at
        type: time

    measures:
      - name: count
        type: count

Both sides of the join must be time dimensions, and the calendar cube’s side must be its primary_key.

A pair of cubes can only be joined once, so translating a second time dimension, such as completed_at, needs a second calendar cube. Define it with extends to inherit the granularities and time shifts, and repeat calendar on it:

YAML

cubes:
  - name: retail_calendar_completed
    extends: retail_calendar
    calendar: true

Then join it to orders as well, on the second time dimension:

YAML

cubes:
  - name: orders
    sql_table: orders

    joins:
      - name: retail_calendar
        sql: "{CUBE}.created_at = {retail_calendar.date}"
        relationship: many_to_one

      - name: retail_calendar_completed
        sql: "{CUBE}.completed_at = {retail_calendar_completed.date}"
        relationship: many_to_one

    dimensions:
      # ...

      - name: completed_at
        sql: completed_at
        type: time

Repeat calendar: true on the extending cube. A cube inherits it from its parent, but inherited cube-level parameters are not always passed to the query engine. Without it, the granularity overrides still apply, but the time shifts silently revert to interval arithmetic: prior + 1 month adds INTERVAL '1 month' instead of reading date_prev_month, and returns different numbers with no error.

Querying

Query orders.count by retail_calendar.date with the month granularity. The result is grouped by retail months, not Gregorian ones:

retail_calendar.date	orders.count
2024-02-04	3
2024-03-03	5
2024-04-07	4

The month beginning on 2024-03-03 spans five weeks; the ones around it span four. Grouping by week, quarter, and year works the same way, and each returns the retail period rather than the Gregorian one.

Comparing with a prior period

Because the calendar cube also overrides the time shifts, a period-over-period measure compares a retail month with the retail month before it. Define it on the cube with facts, next to the measure it shifts:

YAML

cubes:
  - name: orders
    # ...

    measures:
      - name: count
        type: count

      - name: count_prior_month
        type: number
        multi_stage: true
        sql: "{count}"
        time_shift:
          - interval: 1 month
            type: prior

The shift resolves through the date_prev_month column, so it lands on the equivalent day of the previous retail month rather than a calendar month earlier.

Measuring a period to date

A rolling window of type to_date also follows the calendar. It belongs on the cube with facts as well:

YAML

cubes:
  - name: orders
    # ...

    measures:
      - name: count_month_to_date
        type: count
        rolling_window:
          type: to_date
          granularity: month

Each window opens on the retail month’s own first day and closes on its last, so a five-week month accumulates over all five of its weeks.

Pre-aggregations

A pre-aggregation over an overridden granularity must declare that granularity. A rollup on month is built from the retail_month_begins column and serves queries at month:

YAML

cubes:
  - name: orders
    # ...

    pre_aggregations:
      - name: orders_by_retail_month
        measures:
          - count
        time_dimension: retail_calendar.date
        granularity: month

Declare the overridden granularity explicitly rather than relying on a finer rollup. Cube can match a day rollup for a month query through the granularity hierarchy, but that rollup holds DATE_TRUNC buckets, and retail months cannot be assembled from them. The query then either fails or returns Gregorian months.

Add a rollup for each retail period you query.