Extending cubes and views

The extends parameter, supported for cubes and views, allows you to create a child cube (or a view) that reuses all declared members of a parent cube (or a view). This helps build reusable data models. Cubes declare members such as measures, dimensions, and segments. When a child cube extends the parent cube, lists of measures, dimensions, and segments are merged. For example, if the parent cube defines the a measure and the child cube defines the b measure, the resulting cube will have both measures a and b. The usual pattern is to extract common measures, dimensions, and joins into the parent cube and then extend from it. This helps prevent code duplication and makes code easier to maintain and refactor. In the example below, the base_events cube defines the common events measures, dimensions, and a join to the users cube:

cubes:
  - name: base_events
    sql_table: events

    joins:
      - name: users
        relationship: many_to_one
        sql: "{CUBE}.user_id = {users.id}"

    measures:
      - name: count
        type: count

    dimensions:
      - name: timestamp
        sql: time
        type: time

cube(`base_events`, {
  sql_table: `events`,

  joins: {
    users: {
      relationship: `many_to_one`,
      sql: `${CUBE}.user_id = ${users.id}`
    }
  },

  measures: {
    count: {
      type: `count`
    }
  },

  dimensions: {
    timestamp: {
      sql: `time`,
      type: `time`
    }
  }
})

It’s important to use the CUBE variable when referencing members and columns of the cube. Not specifying the cube name or using ${base_events} does not work when the cube is extended.

The product_purchases and page_views cubes are extended from base_events and define only the specific dimensions: product_name for product purchases and page_path for page views.

cubes:
  - name: product_purchases
    sql_table: product_purchases
    extends: base_events

    dimensions:
      - name: product_name
        sql: product_name
        type: string

  - name: page_views
    sql_table: page_views
    extends: base_events

    dimensions:
      - name: page_path
        sql: page_path
        type: string

cube(`product_purchases`, {
  sql_table: `product_purchases`,
  extends: base_events,

  dimensions: {
    product_name: {
      sql: `product_name`,
      type: `string`
    }
  }
})

cube(`page_views`, {
  sql_table: `page_views`,
  extends: base_events,

  dimensions: {
    page_path: {
      sql: `page_path`,
      type: `string`
    }
  }
})

Extending views

A child view that extends a parent view gets these items from the parent view:

The cubes includes. The child view’s own cubes items are added to them.
The view’s own measures and dimensions, defined on the parent view.
The folders. The child view’s own folders are added to them.
The access policies. The child view’s own policies are added to them; they do not replace the parent’s.
Each other parameter that the child view does not set, such as public and description.

In the example below, sales_overview gets orders_total_amount, orders_count and average_order_value from orders_base, and adds orders_status:

views:
  - name: orders_base
    public: false

    cubes:
      - join_path: orders
        prefix: true
        includes:
          - total_amount
          - count

    measures:
      - name: average_order_value
        type: number
        sql: "{CUBE.orders_total_amount} / NULLIF({CUBE.orders_count}, 0)"

  - name: sales_overview
    extends: orders_base
    public: true

    cubes:
      - join_path: orders
        prefix: true
        includes:
          - status

view(`orders_base`, {
  public: false,

  cubes: [
    {
      join_path: orders,
      prefix: true,
      includes: [`total_amount`, `count`]
    }
  ],

  measures: {
    average_order_value: {
      type: `number`,
      sql: `${CUBE.orders_total_amount} / NULLIF(${CUBE.orders_count}, 0)`
    }
  }
})

view(`sales_overview`, {
  extends: orders_base,
  public: true,

  cubes: [
    {
      join_path: orders,
      prefix: true,
      includes: [`status`]
    }
  ]
})

A child view cannot remove an inherited member. To reuse a multi-fact measure that only a view defines, and for the limits of view inheritance, see Reusing a view measure across views.

Usage with `FILTER_PARAMS`

If the parent cube is using FILTER_PARAMS in any sql parameter, then child cubes can accomodate to that in two ways. First, the sql parameter can be overridden in each child cube:

cubes:
  - name: product_purchases
    sql: |
      SELECT *
      FROM events
      WHERE {FILTER_PARAMS.product_purchases.timestamp.filter('time')}

    # ...

cube(`product_purchases`, {
  sql: `
    SELECT *
    FROM events
    WHERE ${FILTER_PARAMS.product_purchases.timestamp.filter("time")}
  `,

  // ...
})

Alternatively, all filters can be put inside the parent cube and referenced in the child cubes using AND. The unused filters will be rendered to 1 = 1 in the SQL query:

cubes:
  - name: base_events
    sql: |
      SELECT *
      FROM events
      WHERE
        {FILTER_PARAMS.base_events.timestamp.filter('time')} AND
        {FILTER_PARAMS.product_purchases.timestamp.filter('time')} AND
        {FILTER_PARAMS.page_views.timestamp.filter('time')}

    # ...

cube(`base_events`, {
  sql: `
    SELECT *
    FROM events
    WHERE
      {$FILTER_PARAMS.base_events.timestamp.filter('time')} AND
      {$FILTER_PARAMS.product_purchases.timestamp.filter('time')} AND
      {$FILTER_PARAMS.page_views.timestamp.filter('time')}
  `,

  // ...
})

Getting Started

Explore & Analyze

Present & Share

Organize content

Data Modeling

Pre-aggregations

Integrations

Extending views

Usage with `FILTER_PARAMS`

​Extending views

​Usage with FILTER_PARAMS

Extending views

Usage with `FILTER_PARAMS`