What is a Metric?

We often see the word “metric” or “measure” in the context of business and data analysis. But what do they mean?

Metric vs Measure

First, let’s clarify the difference between “metric” and “measure”, so we can use a single term for the rest of this article.

In the Business Intelligence (BI) world, the words “metric” and “measure” are often used interchangeably. Some BI tools use the term “metric”, while others use “measure”. However, in general, these terms are intended to convey the same meaning. This makes sense, as the word “metric” is derived from the Greek word “metron”, which translates to “a means of measure”.

In AMQL (AML + AQL), we have both “metric” and “measure”, where “measure” is defined in a model, while “metric” is defined at the dataset level. But for the purpose of this article, we will use the term “metric” to refer to both “metric” and “measure”.

General definitions

Let’s start with the general definitions of “metric”.

To a business user

A metric is a number that tells them something about their business. It could be a number of sales, a number of customers, a percentage of something, etc. It’s a number that enables them to know how their business is doing, and more importantly, drive decisions to help improve their business.

To a data analyst

A metric is a piece of logic that calculates the number that the business wants above. It could be a simple count of sales records or a complex formula that involves multiple tables and fields.

For example, a business user might want to know the number of sales in the last 30 days. An analyst would then write a SQL query that calculates this number, and this query is a metric by that definition.

Metric: Number of sales in the last 30 days

SELECT COUNT(*) FROM sales WHERE sales.created_at >= now() - interval '30 days'

Why are metrics important?

Metrics define the core of your business logic. They are the numbers that your business cares about, and they are the numbers that drive your business decisions. They are the numbers that you want to track and improve over time.

In a metric-based BI tools, they are the building block of your reports and dashboards. You start with defining metrics, and then build reports and dashboards by slice-and-dicing these metrics with different dimensions.

Example: Slice-and-dice a metric to see finer details

Defining metrics beforehand allows you to reuse them across your whole organization. This ensures that everyone is looking at the same numbers, and that everyone is making decisions based on the same numbers.

Metrics before AQL

Before AQL, metrics in Holistics are defined in the modeling layer (AML), using a subset of SQL that can appear in the SELECT clause.

sales.model.aml

Model sales {
  measure number_of_sales {
    label: 'Number of Sales'
    type: 'number'
    definition: @sql COUNT(*) ;;
  }
}

When you want to use this metric, you can just reference it by name, and the SQL definition will be injected into the query generated by Holistics. For example, this is the query generated by Holistics when you use the number_of_sales metric in a report with the dimension category and filter created_at in last 7 days:

SELECT
  category,
  COUNT(*) as number_of_sales
FROM sales
WHERE sales.created_at >= now() - interval '7 days'
GROUP BY category

Everything in gray is generated by Holistics

This is the power of metrics in Holistics. They are defined once, and can be reused across different contexts, ensuring consistency and accuracy in your reports and dashboards.

But there's a problem with this approach. The subset of SQL that can appear in the SELECT clause is very limited, and it’s not enough to express all the metrics that business users want to see. We cannot add WHERE clauses to filter the data, we cannot join with other tables, we cannot add GROUP BY clauses for nested aggregation, etc.

Imagine, what if we can define the metric like this instead? Utilizing the full power of SQL to define the metric logic.

measure number_of_sales_in_last_30_days {
  definition: @sql
    SELECT COUNT(*) FROM sales WHERE sales.created_at >= now() - interval '30 days'
  ;;
}

measure average_number_of_sales_by_category {
  definition: @sql
    SELECT AVG(SELECT COUNT(*)
               FROM sales WHERE sales.created_at >= now() - interval '7 days'
               GROUP BY category)
  ;;
}

measure sales_value {
  definition: @sql
    SELECT SUM(sales.number_of_sales * products.price)
    FROM sales LEFT JOIN products ON sales.product_id = products.id
  ;;
}

How are metrics defined in AQL?

In an ideal world, we would love to define metrics like the above. However, the problem is that SQL is not designed to be a metric definition language. Even though SQL is a declarative language, it’s still too precise in how it wants the data to be aggregated, joined, and grouped.

We need a more abstract and high-level language to define metrics where we can translate a metric into completely different SQL queries with different aggregation, join, and group by logic based on the business user’s intent and interaction.

This is where AQL comes in. AQL is a high-level language that is designed to be a metric definition language. It’s designed to be a language that can express the full power of SQL but in a more abstract and high-level way.

In AQL, a metric is a full-fledged query that can be translated into SQL. It can be as simple as a simple aggregation:

count(sales.id) // same as SELECT COUNT(id) FROM sales

Or it can be more complex with added where clause:

// SELECT COUNT(id) FROM sales WHERE sales.created_at >= now() - interval '30 days'
count(sales.id) | where(sales.created_at >= @(30 days ago))

Or even more complex with nested aggregation and group by:

// SELECT AVG(SELECT COUNT(*)
//            FROM sales WHERE sales.created_at >= now() - interval '7 days'
//            GROUP BY category)

avg(
  sales
  | filter(sales.created_at >= @(7 days ago))
  | group(sales.category)
  | select(count(sales.id))
)

And any of these AQL can run by itself, or be used as a building block for more complex metrics.

sales.model.aml

measure total_orders {
  label: 'Total Orders'
  type: 'number'
  definition: @aql count(orders.id) ;;
}

measure total_orders_of_all_countries {
  label: 'Total Orders of all Countries'
  type: 'number'
  definition: @aql orders.total_orders | of_all(countries) ;;
}

measure country_orders_pct {
  label: 'Country Orders Pct'
  type: 'number'
  definition: @aql orders.total_orders * 1.0 / orders.total_orders_of_all_countries ;;
}

Conclusion

In conclusion, metric in AQL is a query that can contains complex logic with aggregation, join, group by and more, while still being dynamic and reusable across different contexts. It’s a powerful concept that allows you to define and manipulate metrics with ease, and this is one of the core objective of AQL.