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 allows the use of custom time shifts and granularities.

cubes:
  - name: fiscal_calendar
    calendar: true
    sql: >
      SELECT
        calendar_date,
        start_of_week, start_of_month, start_of_year,
        week_ago, month_ago, year_ago
      FROM calendar_table
    
    dimensions:
      - name: date
        sql: calendar_date
        type: time
        primary_key: true

        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
      calendar_date,
      start_of_week, start_of_month, start_of_year,
      week_ago, month_ago, year_ago
    FROM calendar_table
  `,

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

      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: {
        week: { sql: `${CUBE}.start_of_week` },
        month: { sql: `${CUBE}.start_of_month` },
        year: { sql: `${CUBE}.start_of_year` }
      }
    }
  }
})

A calendar cube must have exactly one primary_key dimension. A second one compiles without error but makes every query that references the cube fail with Cube '...' has multiple primary keys, but only one is allowed for calendar cubes.

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}"
        relationship: many_to_one
    
    # ...

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

  joins: {
    fiscal_calendar: {
      sql: `${CUBE}.date = ${fiscal_calendar.date}`,
      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
        sql: "{CUBE}.date::TIMESTAMP"
        type: time
        primary_key: true

        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}"
        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
        multi_stage: true
        time_shift:
          - type: prior
            interval: 1 month

      - name: total_sales_few_days_ago
        sql: "{total_sales}"
        type: number
        multi_stage: true
        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: {
      sql: `${CUBE}.date::TIMESTAMP`,
      type: `time`,
      primary_key: true,

      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}`,
      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`,
      multi_stage: true,
      time_shift: [
        { type: `prior`, interval: `1 month` }
      ]
    },

    total_sales_few_days_ago: {
      sql: `${total_sales}`,
      type: `number`,
      multi_stage: true,
      time_shift: [
        { name: `my_favorite_time_shift` }
      ]
    }
  }
})

When the sales.total_sales_prior_month and sales.total_sales_few_days_ago measures are queried together with the custom_calendar.date time dimension, the generated SQL uses 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 custom_calendar cube overrides the default month granularity with 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
        sql: "{CUBE}.date::TIMESTAMP"
        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: {
      sql: `${CUBE}.date::TIMESTAMP`,
      type: `time`,
      primary_key: true,

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

A pre-aggregation must declare the overridden granularity itself. A rollup that names it is built from the overriding column and serves those queries correctly.A rollup at a finer granularity is not a correct source. Cube can still match one through the granularity hierarchy — a day rollup for a month query — but the rollup holds DATE_TRUNC buckets that the overriding column cannot be recovered from, so the query either fails or returns Gregorian periods. Custom periods cannot be assembled from predefined ones.

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.

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`