Designing Metrics

Cube is a dataset-centric semantic layer, where all primary objects, cubes and views, are table-like datasets. When designing how your semantic layer will be exposed and consumed by end users, you can follow either entity-first approach or metrics-first. In both cases, views will be used to build the semantic layer interface.

Entity-first

In entity-first approach, views are built around entities in your data model. Views are built as denormalzied tables, bringing measures and dimensions from different cubes needed to fully decscribe the entity.

views:
  - name: orders_view
 
    cubes:
      - join_path: orders
        includes:
          - status
          - created_at
 
          - completed_count
          - count
          - total_amount
          - average_order_value
 
      - join_path: orders.users
        prefix: true
        includes: 
          - city
          - age
          - gender

In our example ecom project, these would be orders or line_items. In the example below we define orders_view view to describe orders entity. It has multiple measures and dimensions

You can optionally create multiple views to describe single entity. It can be useful for entities with a large number of dimensions to not overwhelm the end users with all the possible dimensions in a single view, but instead creating multiple slices of data with only relevant measures and dimensions.

In the example below, we are breaking down orders_view into orders_view and orders_with_users_view. Only the latter view will contain dimensions related to user who placed the order.

views:
  - name: orders_view
 
    cubes:
      - join_path: orders
        includes:
          - status
          - created_at
 
          - completed_count
          - count
          - total_amount
          - average_order_value

views:
  - name: orders_with_users_view
 
    cubes:
      - join_path: orders
        includes:
          - status
          - created_at
 
          - completed_count
          - count
          - total_amount
          - average_order_value
 
      - join_path: orders.users
        prefix: true
        includes: 
          - city
          - age
          - gender

Views are exposed as tables in Cube SQL API, dimensions can be queried as is

Metrics-first

In metrics-first approach, views are built around measures, or metrics, in your data model. Views are built as denormalzied tables, containing one measure and all the relevant dimensions from different cubes. These include all the dimensions you would group or filter by, and up to one time dimension.
Views are usually named after that single measure.

views:
  - name: average_order_value
 
    cubes:
      - join_path: orders
        includes:
          - average_order_value
 
          - status
          - created_at
 
      - join_path: orders.users
        prefix: true
        includes: 
          - city
          - age
          - gender

SELECT
  status,
  MEASURE(average_order_value)
FROM average_order_value
GROUP BY 1

NOTE: If a metric is interesting across more than one time dimension, create multiple views (metrics), one with each time dimension, and name the metric distinctly for each.
This approach improves compatibility with BI tooling and adds clarity for the consumer.

views:
  - name: order_count_by_order_date
 
    cubes:
      - join_path: orders
        includes:
          - order_count
 
          - status
          - created_at
 
  - name: order_count_by_ship_date
 
    cubes:
      - join_path: orders
        includes:
          - order_count
 
          - status
          - shipped_at

Integrations with BI Tools

Some metrics-based BI tools will specify requirements for the views or be able to accept additional metadata to enrich the experience.
Below is an example of using the meta property to do this.

views:
  - name: order_count_by_order_date
    description: For finance team to track orders on accrual/earned basis.
    meta:
      type: metric
      owner: alice@acme.com
 
    cubes:
      - join_path: orders
        includes:
          # MEASURE
          - order_count
 
          # TIME
          - created_at
 
          # DIMENSIONS
          - status
          - city

Using Cube with dbt Recipes