Create Cross-Model Calculation

info

This is part of our beta expression language AQL. Learn more. Request beta.

Overview

In most analytic setups, data are organized across multiple models. Thus, it is often necessary to perform calculations that involve data from multiple models. For example, in an e-commerce dataset, you may want to make calculations that involves data from orders, users, and products models. In AQL, this is called cross-model calculation.

In this guide, you will learn how to perform cross-model calculation in AQL. If you want to learn more about the concepts behind cross-model calculation, please refer to its reference page.

Prerequisites

This guide assumes that you have a basic understanding of the following concepts:

• How to define data models
• How to define data sets
• How to define relationships

Setup

To start, visit the following AQL playground (link) and examine the ecommerce dataset. This dataset contains 4 models: users, products, orders, and order_items. The relationships between these models are defined as follows:

You are free to modify the dataset and the models as you see fit, or using different models and relationships in your own project. But for the purpose of this guide, we will use the ecommerce dataset as an example.

Implementation

Defining your first cross-model dimension

Let's say you want to create a dimension that contains the actual value of each order item in the order_items model with the following definition:

$$\text{item value} = \text{item quantity} \times \text{product price}$$

Examining the order_items model, you will see that it only has a quantity column and a product_id column, and does not contain the price of the product. Thus, you need to somehow access the price of the corresponding product in the products model.

In AQL, this is a simple matter of:

1. Define a relationship between the order_items model and the products model.

   Dataset ecommerce {
     /* ... */
   
     relationships: [
       relationship(order_items.order_id > orders.id, true),
       relationship(order_items.product_id > products.id, true),
       /* ... */
     ]
   }

2. Define the value dimension directly referencing products.price in the order_items model. (You can also define it at the dataset level)

   Model order_items {
     /* ... */
   
     dimension value {
       label: 'value'
       type: 'number'
       definition: @aql order_items.quantity * products.price ;;
     }
   }

3. Test the new dimension.

   order_items | select(quantity, products.price, value)

   Open in AMQL playground
   Result

   SQL

   WITH "aql__t2" AS (
     SELECT
       "order_items"."quantity" AS "quantity",
       "order_items"."product_id" AS "product_id",
       ("order_items"."quantity" * "products"."price") AS "value"
     FROM
       "demo"."order_items" "order_items"
       LEFT JOIN "demo"."products" "products" ON "order_items"."product_id" = "products"."id"
   )
   SELECT
     "order_items"."quantity" AS "quantity",
     "products"."price" AS "products->price",
     "order_items"."value" AS "value"
   FROM
     "aql__t2" "order_items"
     LEFT JOIN "demo"."products" "products" ON "order_items"."product_id" = "products"."id"

Defining your first cross-model measure

Similar to the previous example, let's say you want to create a measure to calculate the total value of all order items in the order_items model with the following definition:

$$\text{total value} = \sum_{\text{item} \in \text{order items}} \text{item value}$$

This time, since you already defined the relationship between the order_items model and the products model, you can simply use the sum function to aggregate the value dimension you defined in the previous example.

Model order_items {
  /* ... */

  measure total_value {
    label: 'Total Value'
    type: 'number'
    definition: @aql sum(order_items.value) ;;
  }
}

order_items.total_value

Open in AMQL playground

Result

SQL

WITH "aql__t2" AS (
  SELECT
    ("order_items"."quantity" * "products"."price") AS "value"
  FROM
    "demo"."order_items" "order_items"
    LEFT JOIN "demo"."products" "products" ON "order_items"."product_id" = "products"."id"
)
SELECT
  SUM("order_items"."value") AS "order_items->total_value"
FROM
  "aql__t2" "order_items"

If you want, you can skip the value dimension and define the total_value measure directly.

Model order_items {
  /* ... */

  measure total_value {
    label: 'Total Value'
    type: 'number'
    definition: @aql sum(order_items, order_items.quantity * products.price) ;;
  }
}

Conclusion

In this guide, you have learned how to make cross-model calculation in AQL. If you want to learn more about the concepts behind cross-model calculation, please refer to its reference page.