Calendar cubes

Defines calendar cubes from dedicated calendar tables to drive custom fiscal or retail time shifts and non-default time granularities where Tesseract is enabled.

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. 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.

YAML

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"

Joins

Calendar cubes are only useful when they are joined with other cubes in the data model.

YAML

cubes:
  - name: sales
    sql_table: sales_facts

    joins:
      - name: fiscal_calendar
        sql: "{CUBE}.date = {fiscal_calendar.date_key}"
        relationship: many_to_one
    
    # ...

When joining a calendar cube to other cubes, the following requirements must be met:

• The calendar cube’s join dimension must be of type time and also be the primary_key.
• The other cube’s join dimension must also be of type time.

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.

YAML

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_shift

Whe 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:

YAML

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: sum

When 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.