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. In versions before v1.7.0, it was not enabled by default.

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"

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.

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.

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:

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.

Naming a granularity defined with `sql`

A granularity defined with sql must be named after a default granularity: second, minute, hour, day, week, month, quarter, or year. Names are matched case-insensitively. The sql parameter changes what an existing unit of time means; it cannot introduce a new one. A granularity under a name of your own, such as fiscal_week, does not compile:

dimensions.<dimension>.granularities.fiscal_week: a granularity defined with 'sql'
must be named after one of the predefined granularities (day, hour, minute, month,
quarter, second, week, year). Define 'fiscal_week' with 'interval' instead

The reason is that interval is what places a granularity in the day → week → month → quarter → year hierarchy, so Cube knows what to roll it up from and what to decompose it into. A default granularity brings that position with it; a name of your own does not, so it has to state its interval. The two styles serve different purposes:

	`interval` with `offset` or `origin`	`sql`
Name	Any name of your own	A default granularity only
Definition	Derived arithmetically from a fixed-length unit	Read from a pre-calculated column
Variable-length periods	Not supported	Supported

Use interval to add a granularity, for example a fiscal week of a regular seven days. See the custom granularity recipe for fiscal_week, fiscal_quarter, and fiscal_year defined that way. Use sql when a period varies in length and cannot be derived arithmetically, such as the months and quarters of a 4-5-4 retail calendar. The custom calendar recipe models a 4-5-4 calendar in full.

A pre-aggregation over an overridden granularity must declare that granularity. A rollup on the month granularity above serves queries at month, and it is built from the mid_month column rather than DATE_TRUNC. A rollup at another granularity will not serve those queries correctly.

Getting Started

Explore & Analyze

Present & Share

Organize content

Data Modeling

Pre-aggregations

Integrations

Configuration

Joins

Overriding time shifts

Overriding granularities

Naming a granularity defined with `sql`

​Configuration

​Joins

​Overriding time shifts

​Overriding granularities

​Naming a granularity defined with sql

Configuration

Joins

Overriding time shifts

Overriding granularities

Naming a granularity defined with `sql`