Skip to Content
DocumentationData modelingReferenceDimensions

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.

cube(`products`, {
  dimensions: {
    price: {
      sql: `price`,
      type: `number`
    },
 
    brand_name: {
      sql: `brand_name`,
      type: `string`
    }
  }
})
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:

cube(`products`, {
  // ...
 
  dimensions: {
    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` }
      }
    }
  }
})
 
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:

cube(`products`, {
  // ...
 
  dimensions: {
    meta_value: {
      title: `Size`,
      sql: `meta_value`,
      type: `string`
    }
  }
})
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.

cube(`products`, {
  // ...
 
  dimensions: {
    comment: {
      description: `Comments for orders`,
      sql: `comments`,
      type: `string`
    }
  }
})
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.

cube(`products`, {
  // ...
 
  dimensions: {
    comment: {
      sql: `comment`,
      type: `string`,
      public: false
    }
  }
})
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.

cube(`orders`, {
  // ...
 
  dimensions: {
    total: {
      sql: `amount`,
      type: `number`,
      format: `currency`
    }
  }
})
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.

cube(`products`, {
  // ...
 
  dimensions: {
    users_count: {
      sql: `${users.count}`,
      type: `number`,
      meta: {
        any: "value"
      }
    }
  }
})
cubes:
  - name: products
    # ...
 
    dimensions:
      - name: users_count
        sql: "{users.count}"
        type: number
        meta:
          any: value

order

The order parameter specifies the default sort order for a dimension. Valid values are asc (ascending) and desc (descending). This parameter is optional.

When set, the dimension’s default sort order is exposed via APIs and integrations. Consuming applications, such as BI tools and custom frontends, can use this metadata to apply consistent default sorting when displaying dimension values, ensuring a uniform user experience across different tools connected to the semantic layer.

cube(`orders`, {
  // ...
 
  dimensions: {
    status: {
      sql: `status`,
      type: `string`,
      order: `asc`
    },
 
    created_at: {
      sql: `created_at`,
      type: `time`,
      order: `desc`
    }
  }
})
cubes:
  - name: orders
    # ...
 
    dimensions:
      - name: status
        sql: status
        type: string
        order: asc
 
      - name: created_at
        sql: created_at
        type: time
        order: desc

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.

cube(`products`, {
  // ...
 
  dimensions: {
    id: {
      sql: `id`,
      type: `number`,
      primary_key: true
    }
  }
})
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:

cube(`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: {
    composite_key_a: {
      sql: `column_a`,
      type: `number`,
      primary_key: true
    },
 
    composite_key_b: {
      sql: `column_b`,
      type: `number`,
      primary_key: true
    }
  },
 
  measures: {
    count: {
      type: `count`
    }
  }
})
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.

cube(`products`, {
  // ...
 
  dimensions: {
    users_count: {
      sql: `${users.count}`,
      type: `number`,
      sub_query: true,
      propagate_filters_to_sub_query: true
    }
  }
})
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.

cubes:
  - name: orders
    # ...
 
    dimensions:
      - name: created_at
        sql: created_at
        type: time
cube(`orders`, {
  // ...
 
  dimensions: {
    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.

cube(`products`, {
  // ...
 
  dimensions: {
    users_count: {
      sql: `${users.count}`,
      type: `number`,
      sub_query: true
    }
  }
})
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.

cube(`orders`, {
  // ...
 
  dimensions: {
    rating: {
      sql: `rating`,
      type: `number`
    }
  }
})
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  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 , 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.

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 
cube(`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: {
    time: {
      sql: `time`,
      type: `time`,
      granularities: {
        quarter_hour: {
          interval: `15 minutes`
        },
        week_starting_on_sunday: {
          interval: `1 week`,
          offset: `-1 day`
        },
        fiscal_year_starting_on_april_01: {
          title: `Corporate and government fiscal year in the United Kingdom`,
          interval: `1 year`,
          origin: `2025-04-01`
        }
      }
    }
  }
})

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.

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"
 
cube(`fiscal_calendar`, {
  calendar: true,
  sql: `
    SELECT
      date_key,
      calendar_date,
      start_of_month,
      start_of_quarter,
      start_of_year
    FROM calendar_table
  `,
 
  dimensions: {
    date_key: {
      sql: `date_key`,
      type: `time`,
      primary_key: true
    },
 
    date: {
      sql: `calendar_date`,
      type: `time`,
      granularities: {
        month: {
          sql: `${CUBE}.start_of_month`
        },
        quarter: {
          sql: `${CUBE}.start_of_quarter`
        },
        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.

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"
cube(`fiscal_calendar`, {
  calendar: true,
  sql: `
    SELECT
      date_key,
      calendar_date,
      fiscal_date_prior_year,
      fiscal_date_next_quarter
    FROM calendar_table
  `,
 
  dimensions: {
    date_key: {
      sql: `date_key`,
      type: `time`,
      primary_key: true
    },
 
    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`
        }
      ]
    }
  }
});

Was this page useful?

MeasuresHierarchies