Skip to main content

​
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 an entity-first or a metrics-first approach.

​
Entity-first approach

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 approach

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