Skip to main content
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, and join aliases require v1.8.7 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, one per time dimension, each with its own alias.

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: 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:
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 once per time dimension, and give each join an alias. Here, orders is translated by both its creation and its completion date:
Both sides of a join must be time dimensions, and the calendar cube’s side must be its primary_key. In JavaScript, define joins as an array, because an alias is not available when joins is an object keyed by cube name. Each alias is a separate copy of the calendar cube with the same granularities and time shifts. A query chooses the date by the path: orders.created_calendar.date translates created_at, and orders.completed_calendar.date translates completed_at. To expose these members in the SQL API and Playground, include them in a view.

Querying

Query orders.count by orders.created_calendar.date with the month granularity. The result is grouped by retail months, not Gregorian ones: 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:
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. It applies to the alias that the query groups by, so the same measure compares creation months by orders.created_calendar.date and completion months by orders.completed_calendar.date.

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:
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. Its time dimension goes through an alias, so it serves only queries by that alias, here orders.created_calendar.date:
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.