Drilling into a Measure after it has been "Dimensionalized"?

[Declan]

Hi all!

I am wondering if there is a way to preserve a Measure’s drilldown after it has been “dimensionalized” in a Derived Table, or if there are any alternative modeling suggestions.

To illustrate the issue, say I am exploring a ‘users’ view. I want to join a ‘user_summaries’ view, which pre-aggregates a ‘Profit’ Measure from an ‘orders’ view (grouped by user id) in order to improve performance. I am reusing some example code from Organizing your LookML into layers with our new refinement syntax here.

view: users {dimension: id {primary_key: yes }}

view: orders {
  dimension: id      { primary_key: yes }
  dimension: user_id { hidden: yes }
  dimension: cost    { type:number }
  dimension: price   { type:number }
  dimension: profit  { type:number sql:${price} - ${cost};; }
  dimension: date    { type:date }
  measure: total_profit  { 
    type:sum
    sql:${profit};; 
    drill_fields: [id, user_id, profit]
  }
}

view: user_summaries {
  derived_table: {
    explore_source: orders {
      column: user_id     { field: orders.user_id }
      column: user_profit { field: orders.total_profit }
    }
  }
  dimension: user_id { primary_key:yes hidden: yes}
  dimension: user_profit { type:number }
}

explore: users {
  join: user_summaries {
    sql_on: ${user_summaries.user_id} = ${users.id} ;;
    relationship: one_to_one
  }
}

By pre-aggregating profit by user in the derived table of ‘user_summaries’, my queries run much faster than if I was to left join the ‘orders’ view directly with a one_to_many relationship. However, the downside is that I’m no longer able to drill down into the raw orders data, and the drill_fields specified in the ‘total_profit’ Measure are not accessible in the ‘users’ Explore.

Does anyone have suggestions for preserving a drill down to the order-level data in this scenario?

[andy4]

Great question, Declan! This is a use case tailor-made for the new Aggregate Awareness feature. Think of dimensionalizing a measure as more of a way to make a metric into something you can group by, not as a way to pre-aggregate things. The right way to solve this problem will be to unravel your dimensionalization of that measure and instead use agg awareness to create pre-built rollups of that measure. This both pre-aggregates into a PDT the value so you don’t have to scan the raw table, AND allows you to drill and get to the row level detail when that is the goal.
Agg awareness takes some nuanced understanding to set it up truly perfectly, and we are still working on tooling to help make that easier. For now, the best way to set it up is to…

1. Make sure you are on a recent Looker version (7.8+) by clicking the Help dropdown in the top menu and checking the release number at the bottom there
2. “Explore from Here” on the dashboard tiles you want to run faster (built with measures, not dimensionalized ones)
3. If you haven’t already assigned a datagroup to that explore, do so now. It should contain a sql_trigger which can detect when new data is available to that explore.
4. Click the gear next to the run button and “Get LookML”
5. Copy and paste the “Aggregate Awareness” LookML into the model which contains that explore.
6. Follow instructions in that code block to move filters you expect to change on a per-user basis from filters to dimensions.
7. Run the same explore query again and check the SQL. There should be annotations on how things are running in the background. You should see Looker using the new agg table there.

More on Aggregate Awareness can be found here:

Looker Documentation

Aggregate Awareness

Use aggregate awareness to make database querying more efficient

[Declan]

Thanks Andy, that sounds like a good solution! In the future, I would love to have the option to add more dimensions to a query at runtime than just those included in the Aggregate Table. It would be great to define a GROUP BY key (‘user_id’ perhaps) for the aggregation and then additional dimensions can be selected post-aggregation. This way, SQL need to GROUP BY every dimension selected in the Explore - just the dimensions that make up the primary key of the aggregation, if that makes sense.