Derived metrics

Derived metrics are metrics that are computed from other metrics, which can be themselves derived metrics or “leaf” metrics (metrics with an aggregation expression). Like in the examples before, derived metrics are created through database views, but with one major difference: the SQL does a CROSS JOIN of the referenced metrics.

---

Database selector loading…

Let’s look at a simple example. Suppose we have these tables:

PRAGMA foreign_keys = ON;

CREATE TABLE dim_products (
  id INTEGER PRIMARY KEY,
  category TEXT
);

CREATE TABLE fact_orders (
  id INTEGER PRIMARY KEY,
  product_id INTEGER,
  amount INTEGER,
  FOREIGN KEY (product_id) REFERENCES dim_products(id)
);

CREATE TABLE dim_products (
  id INTEGER,
  category TEXT
);

CREATE TABLE fact_orders (
  id INTEGER,
  product_id INTEGER,
  amount INTEGER,
  FOREIGN KEY (product_id) REFERENCES dim_products(id)
);

CREATE TABLE dim_products (
  id SERIAL PRIMARY KEY,
  category TEXT
);

CREATE TABLE fact_orders (
  id SERIAL PRIMARY KEY,
  product_id INTEGER REFERENCES dim_products(id),
  amount INTEGER
);

We create a couple leaf metrics:

CREATE VIEW total_orders AS
SELECT SUM(amount) AS "total_orders [type=INTEGER]"
FROM fact_orders;

CREATE VIEW order_count AS
SELECT COUNT(*) AS "order_count [type=INTEGER]"
FROM fact_orders;

CREATE VIEW total_orders AS
SELECT SUM(amount) AS total_orders
FROM fact_orders;

CREATE VIEW order_count AS
SELECT COUNT(*) AS order_count
FROM fact_orders;

Now, to define a derived metrics we do a CROSS JOIN between the 2 views:

CREATE VIEW avg_order_value AS
SELECT 1.0 * total_orders / NULLIF(order_count, 0) AS "avg_order_value [type=REAL]"
FROM total_orders
CROSS JOIN order_count;

CREATE VIEW avg_order_value AS
SELECT 1.0 * total_orders / NULLIF(order_count, 0) AS avg_order_value
FROM total_orders
CROSS JOIN order_count;

Why a cross join? Since leaf metrics must be defined without a fixed grain, doing a cross join allows the overall value of the derived metric to be computed by simply querying the view. In our example:

SELECT * FROM total_orders;
350
SELECT * FROM order_count;
3
SELECT * FROM avg_order_value;  -- 350/3
116.666666666667

It’s also possible to create derived metrics from other derived metrics, or even a combination of leaf and derived metrics. Note that we shouldn’t use a cross join when the derived metrics reads from a single metric, for example:

CREATE VIEW avg_order_value_plus_one AS
SELECT avg_order_value + 1 AS "avg_order_value_plus_one [type=REAL]"
FROM avg_order_value;

CREATE VIEW avg_order_value_plus_two AS
SELECT avg_order_value_plus_one + 1 AS "avg_order_value_plus_two [type=REAL]"
FROM avg_order_value_plus_one;

CREATE VIEW avg_order_value_plus_one AS
SELECT avg_order_value + 1 AS avg_order_value_plus_one
FROM avg_order_value;

CREATE VIEW avg_order_value_plus_two AS
SELECT avg_order_value_plus_one + 1 AS avg_order_value_plus_two
FROM avg_order_value_plus_one;