Subquery dimensions

You can use subquery dimensions to reference measures from other cubes inside a dimension. Under the hood, it behaves as a correlated subquery (opens in a new tab), but is implemented via joins for optimal performance and portability.

Consider the following tables, where we have deals and sales_managers. deals belong to sales_managers and have the amount dimension. What we want is to calculate the amount of deals for sales_managers:

To calculate the deals amount for sales managers in pure SQL, we can use a correlated subquery, which will look like this:

SELECT
  id,
  (
    SELECT SUM(amount)
    FROM deals
    WHERE deals.sales_manager_id = sales_managers.id
  ) AS deals_amount
FROM sales_managers
GROUP BY 1

Cube makes subqueries easy and efficient. Subqueries are defined as regular dimensions with the parameter sub_query set to true.

cubes:
  - name: deals
    sql_table: deals
 
    measures:
      - name: amount
        sql: amount
        type: sum
 
    dimensions:
      - name: sales_manager_id
        sql: sales_manager_id
        type: string
 
  - name: sales_managers
    sql_table: sales_managers
 
    joins:
      - name: deals
        relationship: one_to_many
        sql: "{sales_managers}.id = {deals.sales_manager_id}"
 
    measures:
      - name: average_deal_amount
        sql: "{deals_amount}"
        type: avg
 
    dimensions:
      - name: id
        sql: id
        type: string
        primary_key: true
 
      - name: deals_amount
        sql: "{deals.amount}"
        type: number
        sub_query: true

A subquery requires referencing at least one measure in its definition. Generally speaking, all the columns used to define a subquery dimension should first be defined as measures on their respective cubes and then referenced from a subquery dimension over a join. For example the following data model will not work:

cubes:
  - name: deals
    sql_table: deals
 
    measures:
      - name: count
        type: count
 
  - name: sales_managers
    # ...
 
    dimensions:
      # ...
 
      - name: deals_amount
        sql: "SUM({deals}.amount)" # Doesn't work, because `amount` is not a measure on `deals`
        type: number
        sub_query: true

You can reference subquery dimensions in measures as usual dimensions. The example below shows the definition of an average deal amount per sales manager:

cubes:
  - name: sales_managers
 
    measures:
      - name: average_deals_amount
        sql: "{deals_amount}"
        type: avg
 
    dimensions:
      - name: id
        sql: id
        type: string
        primary_key: true
 
      - name: deals_amount
        sql: "{deals.amount}"
        type: number
        sub_query: true

Under the hood

Based on the subquery dimension definition, Cube will create a query that will include the primary key dimension of the main cube and all measures and dimensions included in the SQL definition of the subquery dimension.

This query will be joined as a LEFT JOIN onto the main SQL query. For example, when using the sales_managers.deals_amount subquery dimension, the following query will be generated:

{
  "measures": ["sales_managers.deals_amount"],
  "dimensions": ["sales_managers.id"]
}

If a query includes the sales_managers.average_deal_amount measure, the following SQL will be generated:

SELECT
  AVG(sales_managers__average_deal_amount)
FROM sales_managers
LEFT JOIN (
  SELECT
    sales_managers.id sales_managers__id,
    SUM(deals.amount) sales_managers__average_deal_amount
  FROM sales_managers
  LEFT JOIN deals
    ON sales_managers.id = deals.sales_manager_id
  GROUP BY 1
) sales_managers__average_deal_amount_subquery
  ON sales_managers__average_deal_amount_subquery.sales_managers__id = sales_managers.id