How to model drill-across relationships?

[tan (Tan Huynh)]

Yes, I’m been waiting to share the progress on this, but the documentation is not there yet.

But I can tell you that we have a way to support these scenarios in the new AQL engine for 4.0.

The gist of the unreleased documentation is that we will support the new with_relationships function that allows you to choose the relationships at the metric/expression level. Here is an example of it in action:

Dataset ecommerce_aql {
  __engine__: 'aql'
  data_source_name: 'demodb'

  models: [
    users,
    orders,
    dim_dates,
  ]

  relationships: [
    relationship(users.sign_up_date > dim_dates.date_key, true),
    relationship(orders.created_date > dim_dates.date_key, false),
    // This is enabled by default, which means by default orders are retrieved 
    // through users
    relationship(orders.user_id > users.id, true),
  ]

  owner: '[email protected]'

  metric total_orders_by_date {
    description: '''
      This metric counts the number of orders created in a period. 
      If you want the orders created by users, please use the default count(orders.id)
    '''
    label: 'Total Orders By Date'
    type: 'number'
    definition: @aql count(orders.id) | with_relationships(orders.created_date > dim_dates.date_key) ;;
  }
}