Avoiding inconsistencies across explores

[fabio1]

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 🙂