with_relationships

Definition

Specifies existing relationships to activate during calculating the measure

This function comes into handy when you have 2 tables that are connected through multiple paths, where only one path can be activated to avoid ambiguous path.

Syntax

with_relationships(measure, relationship, ...)

Examples

// Activate the relationship between order_items and products
sum(order_items.revenue) | with_relationships(order_items.product_id > products.id)

Input

• measure: A measure that you want to override the relationships

• relationship (repeatable): A relationship that you want to activate. It comes in 2 forms:

  • model1.column1 > model2.column2 (many-to-one). E.g. order_items.product_id > products.id
  • model1.column1 - model2.column2 (one-to-one). E.g. merchants.admin_id - users.id
  caution

  Only relationships that are already defined in the dataset can be used in with_relationships

Output

Measure with specified relationships.

caution

Relationships activated by with_relationships will have with priority over normal relationships, but the shortest relationships path will still always be used. For example, if you have 2 paths between order_items and products: order_items.product_id > products.id and order_items.order_id > orders.id > products.id, then the first path will be used even if you activate the second path in with_relationships.

---

Sample Usages

Consider the scenario where we have two facts models order_items and shippings, both connected to the dimension models products, orders, and countries.

In order to avoid ambiguous paths, for order_items relationships, we can only activate one link between order_items and products, and deactivate the other links. While deactivating these relationships, we still need to keep those definitions in order to reuse it in with_relationships.

Dataset e_commerce {
  (...)
  
  relationships: [
    // shippings relationships
    relationship(shippings.product_id > products.id, true),
    relationship(shippings.order_id > orders.id, true),
    relationship(shippings.country_id > countries.id, true),
    
    // order_items relationships
    relationship(order_items.product_id > products.id, true),
    relationship(order_items.order_id > orders.id, false), // deactivated
    relationship(order_items.country_id > countries.id, false) // deactivated
  ]
}

Then override the deactivated relationships in the measure/metric definition using with_relationships. We can also omit the link from order-items-products since we already activate it in the dataset.

Model order_items {
  (...)
  
  measure total_sales {
    label: 'Total Sales'
    type: 'number'
    definition: @aql 
      sum(order_items.revenue) | 
      with_relationships(
        // You can omit the first relationship as it is already activated in the dataset
        // order_items.product_id > products.id,
        order_items.order_id > orders.id,
        order_items.country_id > countries.id,
      )
    ;;
  }
}