keep_grains

Definition

The keep_grains() function is used when you want your metric to only be evaluated against some specific grain(s)/dimension(s), excluding any other dimensions presented in the query. If the specified grain(s) are not included in the query, the metric will be evaluated against the whole dataset. Note that all filters applied on the excluded dimensions are also ignored.

Knowledge Checkpoint

This documentation assumes you are familiar with the following concepts:

Syntax

keep_grains(metric, model, ..., dimension, ...)

Examples
keep_grains(users.total_spent, users.email)
users.total_spent | keep_grains(users.email)

keep_grains(users.total_spent, users)
users.total_spent | keep_grains(users)

Input:

metric: An AQL Metric
model (optional, repeatable): The model that contains the dimensions that you want to keep. If a model is specified, the function will keep all the dimensions in that model.
dimension (optional, repeatable): The dimension that you want to keep. If a dimension was not specified here, it will be ignored.

Output:

A metric that was only evaluated against at most the specified grain(s)/dimension(s).

Behaviors

When the grains is a specific Dimension

Let's say that you have defined a measure called total_spent in side model users:

Model users {
  dimension id {}
  dimension name {}
  dimension email {}
  measure total_spent {
    definition: @aql
      // Use a specific field as the dimension to keep
      sum(order_items.order_value) | keep_grains(users.email)
    ;;
  }
}

When exploring the dataset, if you include the following fields: you include the following fields:

Dimension: users.email, order_items.order_month
Measure: users.total_spent

Then the measure users.total_spent will be grouped by users.email, and the value will be duplicated for each month:

users.email	order_items.order_month	users.total_spent
abc	1	1000
abc	2	1000
abc	3	1000
def	1	1500
def	2	1500
def	3	1500

If you do not include the users.email field when exploring data, the users.total_spent measure will not be grouped by any dimension, and will return the total value across all users (In this case: 1000 + 1500 = 2500):

order_items.order_month	users.total_spent
1	2500
2	2500
3	2500

When the grain is a Model

Model users {
    dimension id {}
    dimension name {}
    dimension email {}
    measure total_spent {
        definition: @aql sum(order_items.order_value) | keep_grains(users) ;; // Use the whole Model as the grain
    }
}

When using the whole users model as the grain to keep, if you do not include any fields from the users model when exploring, then the users.total_spent measure will not be grouped by any dimension, and will return the total value across all users:

order_items.order_month	users.total_spent
1	2500
2	2500
3	2500

Examples

Suppose that you have a dataset containing the order_items and users model:

Model order_items {
  dimension user_id {}
  dimension quantity {}
  dimension price {}
  dimension order_status {}

  dimension order_value {
    definition: @aql order_items.price * order_items.quantity
  }

  measures total_amount {
    definition: @aql sum(order_items.order_value) ;;
  }

  measures total_orders {
    definition: @aql count_distinct(order_items.order_id) ;;
  }
}

Model users {
  dimension full_name {}
}

You want to calculate the Customer’s Average Order Value metric with the following logic:

Calculate the customer’s total orders’ value
Calculate the customer’s total number of orders
Divide (1) by (2)

You would want the two aggregations (1) and (2) to always be calculated against the customer, and not any other dimensions.

The metric will be defined in the ecommerce dataset like this:

Dataset ecommerce {
  ...

  models: [order_items, users]
  relationships: [
    relationship(order_items_ext.user_id > users.id, true)
  ]

  // AOV measure definition
  measure customer_aov {
      label: 'Customer AOV'
      type: 'number'
      definition: @aql
        (order_items.total_amount * 1.0 / order_items.total_orders) | keep_grains(users) ;;
    }

  // With distributive property, you can also use keep_grains() on each measure:
  measure customer_aov_dist {
      label: 'Customer AOV'
      type: 'number'
      definition: @aql
        (order_items.total_amount * 1.0 | keep_grains(users))
        / (order_items.total_orders | keep_grains(users));;
    }
}

Result:

Even when we add another dimension like Order Status, the Customer AOV metric will still only be calculated for Customers, and remain unaffected by the new dimension:

Definition​

Syntax​

Behaviors​

When the grains is a specific Dimension​

When the grain is a Model​

Examples​