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

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

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` },
      },
    },
  },
});

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` },
      },
    },
  },
});

`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

cube(`products`, {
  // ...
 
  dimensions: {
    comment: {
      description: `Comments for orders`,
      sql: `comments`,
      type: `string`,
    },
  },
});

`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

cube(`orders`, {
  // ...
 
  dimensions: {
    total: {
      sql: `amount`,
      type: `number`,
      format: `currency`,
    },
  },
});

`meta`

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

YAML

JavaScript

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

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

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`
    }
  }
})

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

cube(`products`, {
  // ...
 
  dimensions: {
    users_count: {
      sql: `${users.count}`,
      type: `number`,
      sub_query: true,
      propagate_filters_to_sub_query: true,
    },
  },
});

`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

cube(`products`, {
  // ...
 
  dimensions: {
    comment: {
      sql: `comment`,
      type: `string`,
      public: false
    }
  }
})

`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

cube(`products`, {
  // ...
 
  dimensions: {
    users_count: {
      sql: `${users.count}`,
      type: `number`,
      sub_query: true,
    },
  },
});

`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

cube(`products`, {
  // ...
 
  dimensions: {
    meta_value: {
      title: `Size`,
      sql: `meta_value`,
      type: `string`,
    },
  },
});

`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

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

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

Measures Hierarchies