Maximizing code reusability with DRY LookML: Defining reusable lists of fields with the set parameter

One way to make your LookML projects more efficient and easier to maintain is with the LookML set parameter. The set parameter lets you group fields that may often be repeated throughout your project within the fields parameter or within drill fields. If you use a set to group repeated fields, you create a single place within the model where that group of fields can be maintained.

This page includes the following examples of using LookML sets to make your LookML projects more efficient and maintainable:

Ingredients

Prerequisites

Example: Adding the same drill fields to multiple measures

Suppose you want your users to be able to drill down to see more granular user details wherever they see sales metrics in Explores, Looks, or dashboards, so that selecting a value for the Count measure in the Explore UI opens a query on the fields ID, City, State, and Country:

Selecting a value for the Count measure opens a query on the fields ID, City, State, and Country.

You can create a set called user_details that includes the fields id, city, state, and country and then reference them in multiple measures for your users to drill into:


set: user_details {
  fields: [id, city, state, country]
}

The user_details set can be used to define a drill field for the count measure:


measure: count {
  type: count
  drill_fields: [user_details*]
}

In this example, the syntax [set_name*] is used to specify that the fields within the user_details will appear in the query results when you drill into the Count measure.

You can then reuse this set to create additional drill fields. For example, you can add the LookML code drill_fields: [user_details*] to the order_count, total_sale_price, and average_sale_price measures:


measure: order_count {
  view_label: "Orders"
  type: count_distinct
  description: "Number of orders"
  sql: ${order_id} ;;
  drill_fields: [user_details*]

}

measure: total_sale_price {
  type: sum
  value_format_name: usd
  sql: ${sale_price} ;;
  drill_fields: [user_details*]
}

measure: average_sale_price {
  type: average
  value_format_name: usd
  sql: ${sale_price} ;;
  drill_fields: [user_details*]
}

As with the Count measure, when a user selects a value for Order Count, Total Sale Price, or Average Sale Price in the Explore UI, Looker opens a window containing a query on the fields ID, City, State, and Country.

If you want to change anything about the drill fields, such as adding or removing a field, you would only have to update the user_details set.

You can also reference a set within the drill_fields parameter of a view to define drill fields for all measures within that view that do not already have drill fields specified at the field level.

Example: Including or omitting a set of fields from an Explore

Suppose you want to simplify the Explore field picker for users by excluding fields that are not crucial for those users.

For example, suppose you have a users view that contains information about your customers, including fields for ID, First Name, Last Name, Email, and Age, as well as location fields like City, State, Country, and Zip. The users view is joined to the orders Explore:

The field picker for the Orders view includes all the fields from the Orders and Users view, including the fields that you want to omit.

What if you want to exclude the First Name, Last Name, Email, Age, and Gender fields from the Explore for your users? You can create a set called user_info that lists the fields you want to exclude from the Orders Explore:

The Orders Without User Data Explore excludes the First Name, Last Name, Email, Age, and Gender fields from the Explore field picker.

The LookML for the user_info set would look like this:


set: user_info {
  fields: [first_name, last_name, email, age, gender]
}

To define an Orders Explore with the label Orders Without User Data, join the users view to the orders Explore. Add the LookML code fields: [ALL_FIELDS*, -users.user_info*] to the orders Explore to include all fields except for the fields in the user_info set from the users view:


explore: orders {
  fields: [ALL_FIELDS*, -users.user_info*]
  label: "Orders Without User Data"
  join: users {
    type: left_outer
    sql_on: ${orders.user_id} = ${users.id} ;;
    relationship: many_to_one
  }
}

Because the user_details set is excluded from the Explore with the syntax -users.user_details* in the fields parameter of the orders Explore, the Orders view within the Orders Without User Data Explore omits the fields listed in the users_user.info set from the Explore field picker.

You can reuse the user_info set to omit these fields from the Explore UI for any other Explores to which the users view is joined. If you want to change anything about the user_info set, such as adding or removing a field, you would only have to update the user_info set.