Skip to main content

​
Calculated measures and dimensions

Often, dimensions are mapped to table columns and measures are defined as aggregations of top of table columns. However, measures and dimensions can also reference other members of the same or other cubes, use SQL expressions, and perform calculations involving other measures and dimensions. Most common patterns are known as calculated measures, proxy dimensions, and subquery dimensions.

​
Calculated measures

Calculated measures perform calculations on other measures using SQL functions and operators. They provide a way to decompose complex measures (e.g., ratios or percents) into formulas that involve simpler measures. Also, calculated measures can help to use non-additive measures with pre-aggregations.

​
Members of the same cube

In the following example, the completed_ratio measure is calculated as a division of completed_count by total count. Note that the result is also multiplied by 1.0 since integer division in SQL would otherwise produce an integer value. 
cubes:
  - name: orders
    sql: |
      SELECT 1 AS id, 'processing' AS status UNION ALL
      SELECT 2 AS id, 'completed'  AS status UNION ALL
      SELECT 3 AS id, 'completed'  AS status

    measures:
      - name: count
        type: count

      - name: completed_count
        type: count
        filters:
          - sql: "{CUBE}.status = 'completed'"

      - name: completed_ratio
        sql: "1.0 * {completed_count} / {count}"
        type: number
If you query for completed_ratio, Cube will generate the following SQL: 
SELECT
  1.0 * COUNT(
    CASE WHEN ("orders".status = 'completed') THEN 1 END
  ) / COUNT(*) "orders__completed_ratio"
FROM (
  SELECT 1 AS id, 'processing' AS status UNION ALL
  SELECT 2 AS id, 'completed'  AS status UNION ALL
  SELECT 3 AS id, 'completed'  AS status
) AS "orders"

​
Members of other cubes

If you have first_cube that is joined to second_cube, you can define a calculated measure that references measures from both first_cube and second_cube. When you query for this calculated measure, Cube will transparently generate SQL with necessary joins. In the following example, the orders.purchases_to_users_ratio measure references the purchases measure from the orders cube and the count measure from the users cube: 
cubes:
  - name: orders
    sql: >
      SELECT 1 AS id, 11 AS user_id, 'processing' AS status UNION ALL
      SELECT 2 AS id, 11 AS user_id, 'completed'  AS status UNION ALL
      SELECT 3 AS id, 11 AS user_id, 'completed'  AS status

    dimensions:
      - name: id
        sql: id
        type: number
        primary_key: true

    measures:
      - name: purchases
        type: count
        filters:
          - sql: "{CUBE}.status = 'completed'"

  - name: users
    sql: >
      SELECT 11 AS id, 'Alice' AS name UNION ALL
      SELECT 12 AS id, 'Bob'   AS name UNION ALL
      SELECT 13 AS id, 'Eve'   AS name

    joins:
      - name: orders
        sql: "{CUBE}.id = {orders}.user_id"
        relationship: one_to_many

    dimensions:
      - name: id
        sql: id
        type: number
        primary_key: true

    measures:
      - name: count
        type: count

      - name: purchases_to_users_ratio
        sql: "1.0 * {orders.purchases} / {CUBE.count}"
        type: number
If you query for users.purchases_to_users_ratio, Cube will generate the following SQL: 
SELECT
  1.0 * COUNT(
    CASE
      WHEN ("orders".status = 'completed') THEN "orders".id
    END
  ) / COUNT(DISTINCT "users".id) "users__purchases_to_users_ratio"
FROM (
  SELECT 11 AS id, 'Alice' AS name UNION ALL
  SELECT 12 AS id, 'Bob'   AS name UNION ALL
  SELECT 13 AS id, 'Eve'   AS name
) AS "users"
LEFT JOIN (
  SELECT 1 AS id, 11 AS user_id, 'processing' AS status UNION ALL
  SELECT 2 AS id, 11 AS user_id, 'completed'  AS status UNION ALL
  SELECT 3 AS id, 11 AS user_id, 'completed'  AS status
) AS "orders" ON "users".id = "orders".user_id

​
Proxy dimensions

Proxy dimensions reference dimensions from the same cube or other cubes. Proxy dimensions are convenient for reusing existing dimensions when defining new ones.

​
Members of the same cube

If you have a dimension with a non-trivial definition, you can reference that dimension to reuse the existing definition and reduce code duplication. In the following example, the full_name dimension references initials and last_name dimensions of the same cube: 
cubes:
  - name: users
    sql_table: users

    dimensions:
      - name: initials
        sql: "SUBSTR(first_name, 1, 1)"
        type: string

      - name: last_name
        sql: "UPPER(last_name)"
        type: string

      - name: full_name
        sql: "{initials} || '. ' || {last_name}"
        type: string
If you query for users.full_name, Cube will generate the following SQL: 
SELECT
  SUBSTR(first_name, 1, 1) || '. ' || UPPER(last_name) "users__full_name"
FROM
  users AS "users"
GROUP BY
  1

​
Members of other cubes

If you have first_cube that is joined to second_cube, you can use a proxy dimension to bring second_cube.dimension to first_cube as dimension (or under a different name). When you query for a proxy dimension, Cube will transparently generate SQL with necessary joins. In the following example, orders.user_name is a proxy dimension that brings the users.name dimension to orders. You can also see that there’s a join relationship between orders and users: 
cubes:
  - name: orders
    sql: |
      SELECT 1 AS id, 1 AS user_id UNION ALL
      SELECT 2 AS id, 1 AS user_id UNION ALL
      SELECT 3 AS id, 2 AS user_id

    dimensions:
      - name: id
        sql: id
        type: number
        primary_key: true

      - name: user_name
        sql: "{users.name}"
        type: string

    measures:
      - name: count
        type: count

    joins:
      - name: users
        sql: "{users}.id = {orders}.user_id"
        relationship: one_to_many

  - name: users
    sql: |
      SELECT 1 AS id, 'Alice' AS name UNION ALL
      SELECT 2 AS id, 'Bob'   AS name

    dimensions:
      - name: name
        sql: name
        type: string
If you query for orders.user_name and orders.count, Cube will generate the following SQL: 
SELECT
  "users".name "orders__user_name",
  COUNT(DISTINCT "orders".id) "orders__count"
FROM (
  SELECT 1 AS id, 1 AS user_id UNION ALL
  SELECT 2 AS id, 1 AS user_id UNION ALL
  SELECT 3 AS id, 2 AS user_id
) AS "orders"
LEFT JOIN (
  SELECT 1 AS id, 'Alice' AS name UNION ALL
  SELECT 2 AS id, 'Bob'   AS name
) AS "users" ON "users".id = "orders".user_id
GROUP BY 1
Note that if you query for orders.user_name only, Cube will figure out that it’s equivalent to querying just users.name and there’s no need to generate a join in SQL: 
SELECT
  "users".name "orders__user_name"
FROM (
  SELECT 1 AS id, 'Alice' AS name UNION ALL
  SELECT 2 AS id, 'Bob'   AS name
) AS "users"
GROUP BY 1

​
Time dimension granularity

When referencing a time dimension of the same or another cube, you can specificy a granularity to refer to a time value with that specific granularity. It can be one of the default granularities (e.g., year or week) or a custom granularity: 
cubes:
  - name: users
    sql: |
      SELECT '2025-01-01T00:00:00Z' AS created_at UNION ALL
      SELECT '2025-02-01T00:00:00Z' AS created_at UNION ALL
      SELECT '2025-03-01T00:00:00Z' AS created_at

    dimensions:
      - name: created_at
        sql: created_at
        type: time

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

      - name: created_at__year
        sql: "{created_at.year}"
        type: time

      - name: created_at__sunday_week
        sql: "{created_at.sunday_week}"
        type: time
If you query for users.created_at, users.created_at__sunday_week, and users.created_at__year dimensions, Cube will generate the following SQL: 
SELECT
  "users".created_at "users__created_at",
  date_trunc('week', ("users".created_at::timestamptz AT TIME ZONE 'UTC') - interval '-1 day') + interval '-1 day' "users__created_at__sunday_week",
  date_trunc('year', ("users".created_at::timestamptz AT TIME ZONE 'UTC')) "users__created_at__year"
FROM (
  SELECT '2025-01-01T00:00:00Z' AS created_at UNION ALL
  SELECT '2025-02-01T00:00:00Z' AS created_at UNION ALL
  SELECT '2025-03-01T00:00:00Z' AS created_at
) AS "users"
GROUP BY 1, 2, 3

​
Subquery dimensions

Subquery dimensions reference measures from other cubes. Subquery dimensions provide a way to define measures that aggregate values of other measures. They can be useful to calculate nested and filtered aggregates.
See the following recipes:
If you have first_cube that is joined to second_cube, you can use a subquery dimension to bring second_cube.measure to first_cube as dimension (or under a different name). When you query for a subquery dimension, Cube will transparently generate SQL with necessary joins. It works as a correlated subquery but is implemented via joins for optimal performance and portability. In the following example, users.order_count is a subquery dimension that brings the orders.count measure to users. Note that the sub_query parameter is set to true on users.order_count. You can also see that there’s a join relationship between orders and users: 
cubes:
  - name: orders
    sql: |
      SELECT 1 AS id, 1 AS user_id UNION ALL
      SELECT 2 AS id, 1 AS user_id UNION ALL
      SELECT 3 AS id, 2 AS user_id

    dimensions:
      - name: id
        sql: id
        type: number
        primary_key: true

    measures:
      - name: count
        type: count

    joins:
      - name: users
        sql: "{users}.id = {orders}.user_id"
        relationship: one_to_many

  - name: users
    sql: |
      SELECT 1 AS id, 'Alice' AS name UNION ALL
      SELECT 2 AS id, 'Bob'   AS name

    dimensions:
      - name: id
        sql: id
        type: number
        primary_key: true

      - name: name
        sql: name
        type: string

      - name: order_count
        sql: "{orders.count}"
        type: number
        sub_query: true

    measures:
      - name: avg_order_count
        sql: "{order_count}"
        type: avg
You can reference subquery dimensions in measures just like usual dimensions. In the example above, the avg_order_count measure performs an aggregation on order_count. If you query for users.name and users.order_count, Cube will generate the following SQL: 
SELECT
  "users".name "users__name",
  "users__order_count" "users__order_count"
FROM (
  SELECT 1 AS id, 'Alice' AS name UNION ALL
  SELECT 2 AS id, 'Bob' AS name
) AS "users"
LEFT JOIN (
  SELECT
    "users_order_count_subquery__users".id "users__id",
    count(distinct "users_order_count_subquery__orders".id) "users__order_count"
  FROM (
    SELECT 1 AS id, 1 AS user_id UNION ALL
    SELECT 2 AS id, 1 AS user_id UNION ALL
    SELECT 3 AS id, 2 AS user_id
  ) AS "users_order_count_subquery__orders"
  LEFT JOIN (
    SELECT 1 AS id, 'Alice' AS name UNION ALL
    SELECT 2 AS id, 'Bob' AS name
  ) AS "users_order_count_subquery__users" ON "users_order_count_subquery__users".id = "users_order_count_subquery__orders".user_id
  GROUP BY 1
) AS "users_order_count_subquery" ON "users_order_count_subquery"."users__id" = "users".id
GROUP BY 1, 2