Avoiding inconsistencies across explores

LookML developers have many easily-discoverable tools to optimize the UI of a particular explore - group labels, descriptions, hiding, etc. With a bit of practice, the best practices around using these tools is readily learned. For example, hiding foreign keys, labeling joined lookup tables into the parent view, and more.

Despite all this, sometimes the end user experience around explores can still become fragmented, inconsistent, and off-putting. Usually this is due to how the model is structured, and the tools and best practices here are not so easy to discover.

If your organization’s users struggle to pick which explores to use, spend a lot of time starting down wrong paths in an explore, or just avoid explores altogether, some of these modeling best practices may be for you.

1. Cleaner inter-view dependencies

Models start out very clean, with views that only reference themselves. Over time, developers inevitably find the need to create fields across views. Usually, they do this directly in one of the two views, like so:

#    ( -__- ) { What could go wrong? )
view: users {...}
view: orders {
  ...
  measure: orders_per_user {
    sql: ${count} / NULLIF(${users.count},0)
  }
}

This later leads to problems when the dependency isn’t there in a second explore. As a solution, developers often either use the explore>fields parameter, or move the cross-view field into a new extended view. However, both of these result in unintentional consistency issues down the road.

Instead, I recommend breaking out your cross-view fields into their own “field only” helper view:

view: users {...}
view: orders {...}
view: users_orders {
  # No need for a sql_table_name or derived_table
  measure: orders_per_user {
    sql: ${orders.count} / NULLIF(${users.count},0)
  }
}
explore: orders {} #Doesn't break like before!
explore: users {
  join: orders {...}
  join: users_orders {
    sql: ;;
    # Use `sql` instead of `sql_on` and put some whitespace in it
    relationship: one_to_one
    view_label: "Orders" #For cleaner explore UI
  } 
}

This seems like some kind of black magic at first. How is the join logic affected?? Well, actually, because the “join clause” is just whitespace, your join SQL isn’t changed at all! And due to the ${} operator, Looker already knows to bring in the user and order joins whenever a user selects a field from this helper view. Easy!

2. Consistent “Dimensional” Joins

Consider a snowflake schema with an “accounts” table. Although there are situations in which you want to pick and choose the available fields for an explore, by default, your users will expect some consistency. When these fields come from a different physical table, such as the account status from a normalized lookup table, or billing and shipping addresses from an addresses table on two different foreign keys, you can use LookML’s extends keywords to encapsulate this logic.

explore: account_joins {
  extension: required #Note this
  join: account {
    type: full_outer
    sql_on: ${account.id} = {{_explore._name}}.account_id ;;
    # ^ This {{_explore}} usage is a bit of a hack, but the extending
    # explore can just re-declare the account join if the above isn't
    # what is needed. See explore "C" below for an example
    relationship: many_to_one
  }
  join: account_facts {
    view_label: "Account"
    sql_on: ${account_facts.account_id} = ${account.id} ;;
    relationship: many_to_one
  }
  join: account_team {
    from: distkey_account_account_team
    view_label: "Account"
    sql_on: ${account_team.account_id} = ${account.id} ;;
    relationship: many_to_one
  }
}

explore: a {
  # ...
  extends: [account_joins]
}
explore: b {
  # ...
  extends: [account_joins]
}
explore: c {
  # ...
  extends: [account_joins]
  join: account {
    type: left_outer
    sql_on: ${account.id} =${c.differently_named_foreign_key} ;;
  }
}

In case you don’t want all these joins to share the same view label, you should at least have them share a prefix / breadcrumb, so that they will consistently be located together when brought into an explore. For example “Users”, “Users > Shipping Address”, “Users > Billing Address”

3. Safely combining explores

Of course, the most elegant way to have consistent explores is to consolidate them into one explore! (The more granular view-specific explores can be kept, but hidden, if desired)

Easier said than done, right? As you probably know, sometimes combining certain tables/joins in an explore can cause data quality and performance problems. For that, I prescribe my cure for the one_to_many blues 🙂

12 5 2,040
5 REPLIES 5

Thank you, Fabio! Great idea!

Do you think it is safe to use sql parameter in join, as there is a warning about it in the documentation:

sql should be avoided as of Looker 3.12.
Use a combination of sql_on, foreign_key, type, and/or sql_table_nameinstead

Yes, I think it’s safe. Although it is an older keyword, and the new keywords are designed to be more intuitive and less verbose, there are many situations in which an explicit sql is used

Just remember that you have to specify the entire JOIN clause when using the sql param! The number of times I’ve been bamboozled by strange SQL errors only to discover that I’d written

sql: ${field} = ${field}

instead of

sql: LEFT JOIN table ON ${field} = ${field}

is embarrassingly high.

Declan
Participant I

Hi @fabio1, thanks for this! I’m trying to create a measure in a “users_orders” field-only type of view that relies on a filter and I’m running into some difficulty since filters are not supported for measures of type ‘string’.

For example, if I want to create a measure that counts orders for users from the USA, how would you recommend building that?

measure: usa_orders {
  sql: ${orders.count}
  filters: [users.country: "USA"] # Not allowed since filters parameter doesn't work for measures without a defined type.
}

Hi @Declan

I think the issue you’re describing is somewhat orthogonal to having a field-only view. You would have a similar issue if the field you described above was directly in the users or orders view. Normally, filters can only be applied to a field with certain specific types like count or sum

This is getting a bit into the weeds, and you may want to play around with Looker’s count type a bit more to understand all the nuances, but something like this should work:

measure: usa_orders {
  type: count
  sql_distinct_key: ${orders.id} ;;
  filters: [users.country: "USA"]
}

The distinct key helps in case you are counting a view that has been fanned out and needs a distinct clause. Depending on your join logic, you may also want to add a filter for orders.id IS NOT NULL

Top Labels in this Space
Top Solution Authors