Implementing custom time dimension granularities - Cube

Use case

Sometimes, you might need to group the result set by units of time that are different from default granularities such as week (starting on Monday) or year (starting on January 1).

Below, we explore the following examples of custom granularities:

Week starting on Sunday, commonly used in the US and some other countries.
Fiscal year and fiscal quarter, commonly used in accounting and financial reporting.

Data modeling

Consider the following data model. interval and offset parameters are used to configure each custom granularity in granularities.

Note that each custom granularity is also exposed via a proxy dimension. This is not just a convenience: while the REST API can query custom granularities by name, the SQL API and the GraphQL API can only address the default granularities, so a proxy dimension is the way to query a custom granularity from those APIs or from a BI tool. Proxy dimensions can also be used in further calculations, like rendering fiscal_quarter_label below.

yaml

cubes:
  - name: custom_granularities
    sql: |
      SELECT '2024-01-15'::TIMESTAMP AS timestamp UNION ALL
      SELECT '2024-02-15'::TIMESTAMP AS timestamp UNION ALL
      SELECT '2024-03-15'::TIMESTAMP AS timestamp UNION ALL
      SELECT '2024-04-15'::TIMESTAMP AS timestamp UNION ALL
      SELECT '2024-05-15'::TIMESTAMP AS timestamp UNION ALL
      SELECT '2024-06-15'::TIMESTAMP AS timestamp UNION ALL
      SELECT '2024-07-15'::TIMESTAMP AS timestamp UNION ALL
      SELECT '2024-08-15'::TIMESTAMP AS timestamp UNION ALL
      SELECT '2024-09-15'::TIMESTAMP AS timestamp UNION ALL
      SELECT '2024-10-15'::TIMESTAMP AS timestamp UNION ALL
      SELECT '2024-11-15'::TIMESTAMP AS timestamp UNION ALL
      SELECT '2024-12-15'::TIMESTAMP AS timestamp

    dimensions:
      - name: timestamp
        sql: timestamp
        type: time

        granularities:
          - name: sunday_week
            interval: 1 week
            offset: -1 day

          - name: fiscal_year
            title: Federal fiscal year in the United States
            interval: 1 year
            offset: -3 months

          - name: fiscal_quarter
            title: Federal fiscal quarter in the United States
            interval: 1 quarter
            offset: -3 months
      
      - name: sunday_week
        sql: "{timestamp.sunday_week}"
        type: time
      
      - name: fiscal_year
        sql: "{timestamp.fiscal_year}"
        type: time
      
      - name: fiscal_quarter
        sql: "{timestamp.fiscal_quarter}"
        type: time

      - name: fiscal_quarter_label
        sql: |
          'FY' || (EXTRACT(YEAR FROM {timestamp.fiscal_year}) + 1) ||
          '-Q' || EXTRACT(QUARTER FROM {timestamp.fiscal_quarter} + INTERVAL '3 MONTHS')
        type: string

javascript

cube(`custom_granularities`, {
  sql: `
    SELECT '2024-01-15'::TIMESTAMP AS timestamp UNION ALL
    SELECT '2024-02-15'::TIMESTAMP AS timestamp UNION ALL
    SELECT '2024-03-15'::TIMESTAMP AS timestamp UNION ALL
    SELECT '2024-04-15'::TIMESTAMP AS timestamp UNION ALL
    SELECT '2024-05-15'::TIMESTAMP AS timestamp UNION ALL
    SELECT '2024-06-15'::TIMESTAMP AS timestamp UNION ALL
    SELECT '2024-07-15'::TIMESTAMP AS timestamp UNION ALL
    SELECT '2024-08-15'::TIMESTAMP AS timestamp UNION ALL
    SELECT '2024-09-15'::TIMESTAMP AS timestamp UNION ALL
    SELECT '2024-10-15'::TIMESTAMP AS timestamp UNION ALL
    SELECT '2024-11-15'::TIMESTAMP AS timestamp UNION ALL
    SELECT '2024-12-15'::TIMESTAMP AS timestamp
  `,

  dimensions: {
    timestamp: {
      sql: `timestamp`,
      type: `time`,

      granularities: {
        sunday_week: {
          interval: `1 week`,
          offset: `-1 day`
        },

        fiscal_year: {
          title: `Federal fiscal year in the United States`,
          interval: `1 year`,
          offset: `-3 months`
        },

        fiscal_quarter: {
          title: `Federal fiscal quarter in the United States`,
          interval: `1 quarter`,
          offset: `-3 months`
        }
      }
    },
    
    sunday_week: {
      sql: `${timestamp.sunday_week}`,
      type: `time`
    },

    fiscal_year: {
      sql: `${timestamp.fiscal_year}`,
      type: `time`
    },
      
    fiscal_quarter: {
      sql: `${timestamp.fiscal_quarter}`,
      type: `time`
    },

    fiscal_quarter_label: {
      sql: `
          'FY' || (EXTRACT(YEAR FROM {timestamp.fiscal_year}) + 1) ||
          '-Q' || EXTRACT(QUARTER FROM {timestamp.fiscal_quarter} + INTERVAL '3 MONTHS')
      `,
      type: `string`
    }
  }
})

</CodeGroup>

Result

Querying this data modal would yield the following result: