Dimensions

You can use the dimensions parameter within cubes to define dimensions. You can think about a dimension as an attribute related to a measure, e.g. the measure user_count can have dimensions like country, age, occupation, etc.

Any dimension should have the following parameters: name, sql, and type.

Dimensions can be also organized into hierarchies.

Parameters

`name`

The name parameter serves as the identifier of a dimension. It must be unique among all dimensions, measures, and segments within a cube and follow the naming conventions.

YAML

JavaScript

cubes:
  - name: products
 
    dimensions:
      - name: price
        sql: price
        type: number
 
      - name: brand_name
        sql: brand_name
        type: string

`case`

The case statement is used to define dimensions based on SQL conditions.

The when parameters declares a series of sql conditions and labels that are returned if the condition is truthy. The else parameter declares the default label that would be returned if there's no truthy sql condition.

The following example will create a size dimension with values xl, xxl, and Unknown:

YAML

JavaScript

cubes:
  - name: products
    # ...
 
    dimensions:
      - name: size
        type: string
        case:
          when:
            - sql: "{CUBE}.size_value = 'xl-en'"
              label: xl
            - sql: "{CUBE}.size_value = 'xl'"
              label: xl
            - sql: "{CUBE}.size_value = 'xxl-en'"
              label: xxl
            - sql: "{CUBE}.size_value = 'xxl'"
              label: xxl
          else:
            label: Unknown

The label property can be defined dynamically as an object with a sql property in JavaScript models:

cube(`products`, {
  // ...
 
  dimensions: {
    size: {
      type: `string`,
      case: {
        when: [
          {
            sql: `${CUBE}.meta_value = 'xl-en'`,
            label: { sql: `${CUBE}.english_size` }
          },
          {
            sql: `${CUBE}.meta_value = 'xl'`,
            label: { sql: `${CUBE}.euro_size` }
          },
          {
            sql: `${CUBE}.meta_value = 'xxl-en'`,
            label: { sql: `${CUBE}.english_size` }
          },
          {
            sql: `${CUBE}.meta_value = 'xxl'`,
            label: { sql: `${CUBE}.euro_size` }
          }
        ],
        else: { label: `Unknown` }
      }
    }
  }
})

`title`

You can use the title parameter to change a dimension's displayed name. By default, Cube will humanize your dimension key to create a display name. In order to override default behavior, please use the title property:

YAML

JavaScript

cubes:
  - name: products
    # ...
 
    dimensions:
      - name: meta_value
        title: Size
        sql: meta_value
        type: string

`description`

This parameter provides a human-readable description of a dimension. When applicable, it will be displayed in Playground and exposed to data consumers via APIs and integrations.

YAML

JavaScript

cubes:
  - name: products
    # ...
 
    dimensions:
      - name: comment
        description: Comments for orders
        sql: comments
        type: string

`public`

The public parameter is used to manage the visibility of a dimension. Valid values for public are true and false. When set to false, this dimension cannot be queried through the API. Defaults to true.

YAML

JavaScript

cubes:
  - name: products
    # ...
 
    dimensions:
      - name: comment
        sql: comment
        type: string
        public: false

`format`

format is an optional parameter. It is used to format the output of dimensions in different ways, for example, a link for external_url. Please refer to the Dimension Formats for the full list of supported formats.

YAML

JavaScript

cubes:
  - name: orders
    # ...
 
    dimensions:
      - name: total
        sql: amount
        type: number
        format: currency

`meta`

Custom metadata. Can be used to pass any information to the frontend.

YAML

JavaScript

cubes:
  - name: products
    # ...
 
    dimensions:
      - name: users_count
        sql: "{users.count}"
        type: number
        meta:
          any: value

`primary_key`

Specify if a dimension is a primary key for a cube. The default value is false.

A primary key is used to make joins work properly.

Setting primary_key to true will change the default value of the public parameter to false. If you still want public to be true, set it explicitly.

YAML

JavaScript

cubes:
  - name: products
    # ...
 
    dimensions:
      - name: id
        sql: id
        type: number
        primary_key: true

It is possible to have more than one primary key dimension in a cube if you'd like them all to be parts of a composite key:

YAML

JavaScript

cubes:
  - name: products
    sql: |
      SELECT 1 AS column_a, 1 AS column_b UNION ALL
      SELECT 2 AS column_a, 1 AS column_b UNION ALL
      SELECT 1 AS column_a, 2 AS column_b UNION ALL
      SELECT 2 AS column_a, 2 AS column_b
 
    dimensions:
      - name: composite_key_a
        sql: column_a
        type: number
        primary_key: true
 
      - name: composite_key_b
        sql: column_b
        type: number
        primary_key: true
 
    measures:
      - name: count
        type: count

Querying the count measure of the cube shown above will generate the following SQL to the upstream data source:

SELECT
  count(
    CAST("product".column_a as TEXT) || CAST("product".column_b as TEXT)
  ) "product__count"
FROM (
  SELECT 1 AS column_a, 1 AS column_b UNION ALL
  SELECT 2 AS column_a, 1 AS column_b UNION ALL
  SELECT 1 AS column_a, 2 AS column_b UNION ALL
  SELECT 2 AS column_a, 2 AS column_b
) AS "product"

`propagate_filters_to_sub_query`

When this statement is set to true, the filters applied to the query will be passed to the subquery.

YAML

JavaScript

cubes:
  - name: products
    # ...
 
    dimensions:
      - name: users_count
        sql: "{users.count}"
        type: number
        sub_query: true
        propagate_filters_to_sub_query: true

`sql`

sql is a required parameter. It can take any valid SQL expression depending on the type of the dimension. Please refer to the Dimension Types to understand what the sql parameter should be for a given dimension type.

YAML

JavaScript

cubes:
  - name: orders
    # ...
 
    dimensions:
      - name: created_at
        sql: created_at
        type: time

`sub_query`

The sub_query statement allows you to reference a measure in a dimension. It's an advanced concept and you can learn more about it here.

YAML

JavaScript

cubes:
  - name: products
    # ...
 
    dimensions:
      - name: users_count
        sql: "{users.count}"
        type: number
        sub_query: true

`type`

type is a required parameter. There are various types that can be assigned to a dimension. Please refer to the Dimension Types for the full list of dimension types.

YAML

JavaScript

cubes:
  - name: orders
    # ...
 
    dimensions:
      - name: rating
        sql: rating
        type: number

`granularities`

By default, the following granularities are available for time dimensions: year, quarter, month, week (starting on Monday), day, hour, minute, second.

You can use the granularities parameter with any dimension of the type time to define one or more custom granularities, such as a week starting on Sunday or a fiscal year.

See this recipe for more custom granularity examples.

Custom granularities are supported for the following data sources: Amazon Athena, Amazon Redshift, DuckDB, Databricks, Google BigQuery, ClickHouse, Microsoft SQL Server, MySQL, Postgres, and Snowflake. Please file an issue (opens in a new tab) if you need support for another data source.

For each custom granularity, the interval parameter is required. It specifies the duration of the time interval and has the following format: quantity unit [quantity unit...], e.g., 5 days or 1 year 6 months.

Optionally, a custom granularity might use the offset parameter to specify how the time interval is shifted forward or backward in time. It has the same format as interval, however, you can also provide negative quantities, e.g., -1 day or 1 month -10 days.

Alternatively, instead of offset, you can provide the origin parameter. When origin is provided, time intervals will be shifted in a way that one of them will match the provided origin. It accepts an ISO 8601-compliant date time string (opens in a new tab), e.g., 2024-01-02 or 2024-01-02T12:00:00.000Z.

Optionally, a custom granularity might have the title parameter with a human-friendly description.

YAML

JavaScript

cubes:
  - name: orders
    sql: |
      SELECT '2025-01-01T00:12:00.000Z'::TIMESTAMP AS time UNION ALL
      SELECT '2025-02-01T00:15:00.000Z'::TIMESTAMP AS time UNION ALL
      SELECT '2025-03-01T00:18:00.000Z'::TIMESTAMP AS time
 
    dimensions:
      - name: time
        sql: time
        type: time
 
        granularities:
          - name: quarter_hour
            interval: 15 minutes
 
          - name: week_starting_on_sunday
            interval: 1 week
            offset: -1 day
 
          - name: fiscal_year_starting_on_april_01
            title: Corporate and government fiscal year in the United Kingdom
            interval: 1 year
            origin: "2025-04-01"
            # You have to use quotes here to make `origin` a valid YAML string

Calendar cubes

When the granularities parameter is used in time dimensions within calendar cubes, you can still use it to define custom granularities.

Additionally, you can override the default granularities. This can be useful for modeling custom calendars, such as fiscal calendars.

YAML

JavaScript

cubes:
  - name: fiscal_calendar
    calendar: true
    sql: >
      SELECT
        date_key,
        calendar_date,
        start_of_month,
        start_of_quarter,
        start_of_year
      FROM calendar_table
 
    dimensions:
      - name: date_key
        sql: date_key
        type: time
        primary_key: true
 
      - name: date
        sql: calendar_date
        type: time
        granularities:
          - name: month
            sql: "{CUBE}.start_of_month"
 
          - name: quarter
            sql: "{CUBE}.start_of_quarter"
 
          - name: year
            sql: "{CUBE}.start_of_year"

`time_shift`

The time_shift parameter allows overriding the time shift behavior for time dimensions within calendar cubes. Such time shifts can be referenced in time-shift measures of other cubes, enabling the use of custom calendars.

The time_shift parameter can only be set on time dimensions within calendar cubes, i.e., cubes where the calendar parameter is set to true.

The time_shift parameter accepts an array of time shift definitions. Each definition can include time_dimension, type, interval, and name parameters, similarly to the time_shift parameter of time-shift measures. Additionally, you can use the sql parameter to define a custom time mapping using a SQL expression.

YAML

JavaScript

cubes:
  - name: fiscal_calendar
    calendar: true
    sql: >
      SELECT
        date_key,
        calendar_date,
        fiscal_date_prior_year,
        fiscal_date_next_quarter
      FROM calendar_table
 
    dimensions:
      - name: date_key
        sql: date_key
        type: time
        primary_key: true
 
      - name: date
        sql: calendar_date
        type: time
        time_shift:
          - name: prior_calendar_year
            type: prior
            interval: 1 year
 
          - name: next_calendar_quarter
            type: next
            interval: 1 quarter
 
          - name: prior_fiscal_year
            sql: "{CUBE}.fiscal_date_prior_year"
 
          - name: next_fiscal_quarter
            sql: "{CUBE}.fiscal_date_next_quarter"

Measures Hierarchies