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"

cube('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: {
    date_key: {
      sql: 'date_key',
      type: 'time',
      primary_key: true
    },

    date: {
      sql: 'calendar_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
    
    # ...

cube(`sales`, {
  sql_table: `sales_facts`,

  joins: {
    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

cube(`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: {
    date_key: {
      sql: `${CUBE}.date::TIMESTAMP`,
      type: `time`,
      primary_key: true
    },

    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` }
      ]
    }
  }
})

cube(`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: {
    custom_calendar: {
      sql: `${CUBE}.date = ${custom_calendar.date_key}`,
      relationship: `many_to_one`
    }
  },

  dimensions: {
    id: {
      sql: `id`,
      type: `number`,
      primary_key: true
    }
  },

  measures: {
    total_sales: {
      sql: `amount`,
      type: `sum`
    },

    total_sales_prior_month: {
      sql: `{total_sales}`,
      type: `number`,
      time_shift: [
        { type: `prior`, interval: `1 month` }
      ]
    },

    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

cube(`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: {
    date_key: {
      sql: `date`,
      type: `time`,
      primary_key: true
    },

    date: {
      sql: `date`,
      type: `time`,
      primary_key: true,

      granularities: [
        { name: `month`, sql: `${CUBE}.mid_month::TIMESTAMP` }
      ]
    }
  }
})

cube(`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: {
    custom_calendar: {
      sql: `${CUBE}.date = ${custom_calendar.date}`,
      relationship: `many_to_one`
    }
  },

  dimensions: {
    id: {
      sql: `id`,
      type: `number`,
      primary_key: true
    }
  },

  measures: {
    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.