Create Metrics in Datasets

How to create metrics in datasets

There are two ways to create metrics in datasets:

• Create metrics via UI in the Data tab.
• Create metrics as-code in the Code tab.

Please note that dataset metrics are persisted to datasets directly. They can be viewed and used by anyone who has access to these datasets. If you are just exploring different ways to answer a business question, you may want to conside creating Ad-hoc Fields instead.

Common Metrics use cases

The next step after defining a Dataset is to add Metrics.

We will use the familiar ecommerce dataset to introduce you some of the most common metric use cases:

1. Simple aggregation: Number of orders (all time)
2. Cross-model aggregation: Total order value (all time)
3. Multiple aggregations in one metric: Average order value (Total order value / orders count) of customers (all time)
4. Metric with filtering condition: Average order value in the last 3 months

The final setup of a dataset with metrics will look something like this:

Final setup

Dataset Definition

Dataset ecommerce {
  label: 'Ecommerce'
  owner: "[email protected]"
  data_source_name: 'demodb'

  models: [
    orders,
    order_items,
    products,
    users,
  ]

  relationships: [
    relationship(orders.user_id > users.id, true),
    relationship(order_items.order_id > orders.id, true),
    relationship(order_items.product_id > products.id, true)
  ]

  // 1. Simple aggregation
  metric count_orders {
    label: 'Count Orders'
    type: 'number'
    definition: @aql count(orders.id) ;;
  }

  // 2. Cross-model aggregation:
  metric sum_order_value {
    label: 'Sum Order Values'
    type: 'number'
    definition: @aql sum(order_items, order_items.quantity * products.price) ;;
  }

  // 3. Multiple aggregation
  metric average_order_value {
    label: 'Average Order Value'
    type: 'number'
    definition: @aql sum_order_value / count_orders;;
  }

  // 4. Metric with condition:
  metric aov_last3m {
    label: 'AOV Last 3 Months'
    type: 'number'
    definition: @aql average_order_value | where(orders.created_at matches @(last 3 months)) ;;
  }
}

Model Relationships

Components of a Metric

info

Unlike model fields, you can only use AQL to define dimensions and metrics in datasets, SQL definitions are not supported.

It is highly recommended to go through AQL in 30 minutes to familiarize yourself with AQL before continue reading.

A basic AQL Metric in Holistics is required to have the following components:

• The table expression that we will run the aggregation on e.g. table, table | filter() | group() | select() ...
• The field to be aggregated e.g. table.field
• The aggregation function e.g. sum, count, min, max, etc.

And optionally:

• The meric context that provides more advanced functionality for metrics e.g. filtering, level of details, window functions etc.

These four components are combined into a metric expression. All of the following syntax are equivalent:

// Full form
aggregate_function(table, table.field)

// Shorthand form - where table is automatically used as the first argument
aggregate_function(table.field)

// With pipe operator
table | aggregate_function(table.field)

Notes

• Please refer to Aggregate Functions document for more details on different forms of aggregation functions.
• Please refer to Table Expression document for more details on how to construct table expressions. In this guide, we will work with a simple table expression consisting of only a model name.
• The | symbol is the pipe operator that take the result of the expression on the left and pass it as the first argument to the function on the right.

Create Metrics

1. Simple aggregation

The most basic metric in Holistics involves aggregating a single field in a single model. In this case, we simply count the values of id field in the orders model:

// 1. Simple aggregation
metric count_orders {
  label: 'Count Orders'
  type: 'number'
  definition: @aql count(orders.id) ;;
}

2. Cross-model aggregation

Here we want to calculate the value of orders, which equals to quantity of items in the order x the item's price. The definition is now a bit more complicated:

metric sum_order_value {
  label: 'Sum Order Values'
  type: 'number'
  definition: @aql order_items | sum(order_items.quantity * products.price);;
  // This is also valid: sum(order_items, order_items.quantity * products.price)
}

In this metrics, there are two tables involved: order_items and products. To let Holistics know exactly from which table should we run the aggregation on, we have to explicitly specify it. In this case, it is the order_items table.

This AQL expression equals to the following SQL:

SELECT
  SUM(order_items.quantity * products.price) AS sum_order_value
FROM order_items
LEFT JOIN products on order_items.product_id = products.id

For cross-model aggregation to work correctly

There are two specific requirements to make cross-model aggregation works correctly:

1. Aggregation table must be specified: If it is not specified in the metric definition like so:

   definition: @aql sum(order_items.quantity * products.price) ;;

   you will encounter an Invalid expression error (that basically says the Holistics engine cannot know which table to aggregate on)

2. Aggregation table must be the N-table in an N - 1 relationship: For example, the relationship between order_items and products is N - 1. If you specify products as the aggregation table like so:

   definition: @aql sum(products, order_items.quantity * products.price) ;;

   you will encounter a fan-out error.

This is a very simple example of cross-model calculation. For more details about this feature, please check out the dedicated Cross-model Calculation doc.

3. Multiple aggregations in one Metric

To calculate Average Order Value (AOV), we need to divide total order value by number of orders. In other words, AOV is an aggregation that involves two other aggregations.

Division is a simple arithmetic operation. We can combine the two aggregations like so:

metric average_order_value {
  label: 'Average Order Value'
  type: 'number'
  definition: @aql sum(order_items, order_items.quantity * products.price) 
                    / count(orders, orders.id);;
}

However, we can reuse the definition of those two metrics by simply referring their names:

metric average_order_value {
  label: 'Average Order Value'
  type: 'number'
  definition: @aql sum_order_value / count_orders;;
}

4. Metric with filtering condition

To calculate a metric only on a subset of records, like AOV of orders in the last 3 months, we append a condition to the metric:

metric aov_last3m {
  label: 'AOV Last 3 Months'
  type: 'number'
  definition: @aql average_order_value | where(orders.created_at matches @(last 3 months)) ;;
}

tip

Useful documents that you should check out:

• where: For more details on condition functions
• AQL Operators: List of logical operators that you can use in the conditions
• AQL Operators - Datetime section: How to write datetime filtering conditions
• Metrics By Examples: more examples of metrics expressions

Using Metrics

After everything is done, if you go to the Data tab to preview your dataset, you will see a new Metrics section on the left panel with all of the metrics you have created.

More advanced Metrics use cases

Metrics in Holistics is a powerful tool that allows complex calculations. Here are some topics that may interest you once you have mastered the basics of Metrics:

• Single vs. Multiple Aggregation Metrics
• Level of Details
• Cumulative Metrics
• Moving Average
• Nested Aggregation
• Cross-model Calculation