Calendar cubes
Calendar cubes are used to implement custom calendars, such as retail calendars. If your data model contains a calendar table, it can be modeled as a calendar cube.
Calendar cubes can be used to override the default time shift behavior of time-shift measures as well as override the default granularities of time dimensions.
Calendar cubes are powered by Tesseract, the [next-generation data modeling engine][link-tesseract]. Tesseract is currently in preview. Use the CUBEJS_TESSERACT_SQL_PLANNER environment variable to enable it.
Configuration
Calendar cubes are cubes where the calendar parameter is set to true. This indicates that the cube is a calendar cube and allow the use of custom time shifts and granularities.
cubes: - name: fiscal_calendar calendar: true sql: > SELECT date_key, calendar_date, start_of_week, start_of_month, start_of_year, week_ago, month_ago, year_ago FROM calendar_table dimensions: - name: date_key sql: date type: time primary_key: true - name: date sql: date type: time time_shift: - type: prior interval: 1 week sql: "{CUBE}.week_ago" - type: prior interval: 1 month sql: "{CUBE}.month_ago" - type: prior interval: 1 year sql: "{CUBE}.year_ago" granularities: - name: week sql: "{CUBE}.start_of_week" - name: month sql: "{CUBE}.start_of_month" - name: year sql: "{CUBE}.start_of_year"Calendar cubes are only useful when they are joined with other cubes in the data model.
cubes: - name: sales sql_table: sales_facts joins: - name: fiscal_calendar sql: "{CUBE}.date = {fiscal_calendar.date_key}" relationship: many_to_one # ...Overriding time shifts
Calendar cubes can be used to override the default time shift behavior of time-shift measures. It can help implement custom time shifts or reuse common time shifts across multiple cubes.
By default, a time shift like prior + 1 month will add INTERVAL '1 month' to the time dimension value in the generated SQL. However, with custom calendars, a more nuanced approach is often needed, such as mapping each date to another pre-calculated date from the calendar table.
In the following example, the custom_calendar cube defines a custom time shift for prior + 1 month that uses the month_ago column from the calendar table. It also defines a custom time shift my_favorite_time_shift of type prior + the 42 days interval.
cubes: - name: custom_calendar calendar: true sql: > SELECT '2025-01-01' AS date, '2024-12-15' AS month_ago UNION ALL SELECT '2025-02-01' AS date, '2025-01-15' AS month_ago UNION ALL SELECT '2025-03-01' AS date, '2025-02-15' AS month_ago UNION ALL SELECT '2025-04-01' AS date, '2025-03-15' AS month_ago UNION ALL SELECT '2025-05-01' AS date, '2025-04-15' AS month_ago UNION ALL SELECT '2025-06-01' AS date, '2025-05-15' AS month_ago dimensions: - name: date_key sql: "{CUBE}.date::TIMESTAMP" type: time primary_key: true - name: date sql: "{CUBE}.date::TIMESTAMP" type: time time_shift: - type: prior interval: 1 month sql: "{CUBE}.month_ago::TIMESTAMP" - type: prior interval: 42 days name: my_favorite_time_shift - name: sales sql: > SELECT 1 AS id, 101 AS amount, '2025-01-01'::TIMESTAMP AS date UNION ALL SELECT 2 AS id, 202 AS amount, '2025-02-01'::TIMESTAMP AS date UNION ALL SELECT 3 AS id, 303 AS amount, '2025-03-01'::TIMESTAMP AS date UNION ALL SELECT 4 AS id, 404 AS amount, '2025-04-01'::TIMESTAMP AS date UNION ALL SELECT 5 AS id, 505 AS amount, '2025-05-01'::TIMESTAMP AS date UNION ALL SELECT 6 AS id, 606 AS amount, '2025-06-01'::TIMESTAMP AS date joins: - name: custom_calendar sql: "{CUBE}.date = {custom_calendar.date_key}" relationship: many_to_one dimensions: - name: id sql: id type: number primary_key: true measures: - name: total_sales sql: amount type: sum - name: total_sales_prior_month sql: "{total_sales}" type: number time_shift: - type: prior interval: 1 month - name: total_sales_few_days_ago sql: "{total_sales}" type: number time_shift: - name: my_favorite_time_shiftWhe sales.total_sales_prior_month and sales.total_sales_few_days_ago measures are queried together with the calendar.date time dimension, the generate SQL will use the custom time shifts defined in the custom_calendar cube: one with the month_ago column and another with INTERVAL '42 days'.
Overriding granularities
Calendar cubes can be used to override the default granularities of time dimensions.
By default, SQL functions like DATE_TRUNC are used to calculate default granularities, such as day, month, or year. However, custom calendars often have different definitions for these periods, e.g., a retail calendar might use 4-5-4 week patterns.
Calendar cubes allow you to define custom SQL expressions for each granularity. In the following example, the fiscal_calendar cube overrides the default month granularity to the to a pre-calculated mid_month column:
cubes: - name: custom_calendar calendar: true sql: > SELECT '2025-01-02' AS date, '2025-01-15' AS mid_month UNION ALL SELECT '2025-02-04' AS date, '2025-02-15' AS mid_month UNION ALL SELECT '2025-03-09' AS date, '2025-03-15' AS mid_month UNION ALL SELECT '2025-04-17' AS date, '2025-04-15' AS mid_month UNION ALL SELECT '2025-05-21' AS date, '2025-05-15' AS mid_month UNION ALL SELECT '2025-06-30' AS date, '2025-06-15' AS mid_month dimensions: - name: date_key sql: date type: time primary_key: true - name: date sql: date type: time primary_key: true granularities: - name: month sql: "{CUBE}.mid_month::TIMESTAMP" - name: sales sql: > SELECT 1 AS id, 101 AS amount, '2025-01-02'::TIMESTAMP AS date UNION ALL SELECT 2 AS id, 202 AS amount, '2025-02-04'::TIMESTAMP AS date UNION ALL SELECT 3 AS id, 303 AS amount, '2025-03-09'::TIMESTAMP AS date UNION ALL SELECT 4 AS id, 404 AS amount, '2025-04-17'::TIMESTAMP AS date UNION ALL SELECT 5 AS id, 505 AS amount, '2025-05-21'::TIMESTAMP AS date UNION ALL SELECT 6 AS id, 606 AS amount, '2025-06-30'::TIMESTAMP AS date joins: - name: custom_calendar sql: "{CUBE}.date = {custom_calendar.date}" relationship: many_to_one dimensions: - name: id sql: id type: number primary_key: true measures: - name: revenue sql: amount type: sumWhen querying sales.revenue by custom_calendar.date with monthly granularity, the mid_month column will be used instead of the standard DATE_TRUNC('month', date) expression in the generated SQL.