This page covers more common patterns in LookML.
Labeling fields (and names in the UI)
Looker converts LookML field names into the strings displayed in the UI by combining the view name in regular-weight font with the field's short name in bold. For example, a field called Amount in the Orders view would appear in the UI as Orders Amount. On this page, they are both bolded and the view name is capitalized (ORDERS Amount) to make the discussion clearer.
If you would like a field to be named differently than its column name in a table, simply change the field name and declare its sql: linkage. In the example below, there is a table airports with a column cntrl_twr. Looker would generate the following declaration:
view: airports {
dimension: cntrl_twr { # full name: airports.cntrl_twr
type: yesno # default name: AIRPORT Cntrl Twr (Yes/No)
sql: ${TABLE}.cntrl_twr ;; # the sql expression for this field
}
}
We will rename the cntrl_twr dimension to be human-readable.
view: airports {
dimension: has_control_tower { # full name: airports.has_control_tower
type: yesno # aliased name: AIRPORTS Has Control Tower (Yes/No)
sql: ${TABLE}.cntrl_twr ;; # the sql expression for this field
}
}
Filtering counts by a dimension
It is pretty easy to group by a dimension and count entities — grouping by USERS Country, ORDERS Count will tell you where your orders are coming from by country. However, it is often useful to build a count filtered by some dimensional value. For example, you could make a new measure ORDERS France Count:
view: users {
dimension: country {}
}
view: orders {
dimension: id {
primary_key: yes
sql: ${TABLE}.id ;;
}
measure: count {
type: count
drill_fields: [detail]
}
measure: france_count {
type: count # COUNT(CASE WHEN users.country = 'France' THEN 1 ELSE NULL END)
filters: [users.country: "France"]
}
}
Filters can use any expression. If you wanted a field that counted users from the EU, you could use something like this:
measure: eu_count {
type: count # COUNT(CASE WHEN users.countrycode IN 'UK','FR','ES' THEN 1 ELSE NULL END)
drill_fields: [detail]
filters: [users.countrycode: "UK,FR,ES"]
}
If you want to filter with a mathematical expression, be sure to enclose it in double quotes:
measure: total_orders_above_100_dollars {
type: sum # SUM(CASE WHEN order.value > 100 THEN order.value ELSE NULL END)
sql: ${order.value} ;;
drill_fields: [detail]
filters: [order.value: ">100"]
}
Percentages
Many key performance indicators are expressed in the form of percentages, such as "the percent of items returned," "the percent of emails that resulted in a sale," or other instances of "the percent of X that Y." In LookML, the design pattern is to create counts for the two conditions and create a third field that computes the percentage between the two.
dimension: returned {
type: yesno
}
measure: count { # total count of items
type: count_distinct
sql: ${TABLE}.id ;;
drill_fields: [detail]
}
measure: returned_count { # count of returned items
type: count_distinct
sql: ${TABLE}.id ;;
drill_fields: [detail]
filters: [returned: "Yes"]
}
measure: percent_returned {
type: number
sql: 100.0 * ${returned_count} / NULLIF(${count}, 0) ;;
value_format: "0.00"
}
Use the form below when computing percentages. In Postgres, counts are integers, and division between integers results in integers. Multiplying by 100.0 converts the first count to a floating point number, thus converting the rest of the expression to a float. In order to avoid divide-by-zero errors, the NULLIF(value, 0) will convert a zero value to null, making the result null and avoiding an error.
100.0 * ${returned_count} / NULLIF(${count}, 0)
Using sets for drill-down details
One of the most powerful features of Looker is the ability to drill into data to see the underlying entities that make up a count or other measure.
When a measure is clicked on the Looker UI, a new query is created localizing the set of data making up the measure. Each value for each dimension on the row in the table gets added to the current filters.
In order to show the detail, Looker needs a specified list of drill fields to show when the measure's value has been clicked. When you generate a model, the generator typically creates some initial drill fields for you. In addition, you can add drill fields yourself. For example, assume that we are measuring ORDERS Count by USERS State in the last week. In Looker, the query would look something like this:
| USERS State | ORDERS Count |
|---|---|
| California | 24 |
| Texas | 5 |
| Colorado | 4 |
| Florida | 4 |
| Illinois | 4 |
If we click on 24 in the California row, the expected behavior is that we'd see the 24 orders that came from California.
Looker handles adding the filter USERS State: California, but Looker doesn't know which fields you want to show in the order. You'll need to use a set to declare those fields in your model.
In LookML, a set is a list of field (dimension, measure, and filter) names. Sets are used to tell Looker which fields:
- You want to show when drilling into a count or other measure
- To import when joining a view
- Are indexed in an Explore
The same set can be used in many places in a model, so Looker provides several ways of creating sets.
Literal sets
The simplest form of a set is a literal set. A literal set is created by simply declaring the set as an array. You can declare literal sets by using '[]'.
Given this example:
view: customers {
dimension: id {
primary_key: yes
}
measure: count {
type: count
}
dimension: city {}
dimension: state {}
dimension: name {}
}
The fields we'd like to show are id, name, and city.
In the measure, we could simply declare a literal array.
measure: count {
type: count
drill_fields: [id, name, city]
}
For sets that are only used once, declaring them literally is simple and easily understood.
Named sets
Suppose we have two counts: CUSTOMERS Count and CUSTOMERS In California Count. When we drill into either of these counts, we'd like to show the fields id, name, and city. If we declare the fields literally, we could:
view: customers {
measure: count {
type: count
drill_fields: [id, name, city]
}
measure: in_california_count {
type: count
filters: [state: "California"]
}
}
If we wanted to add a new field (say the field customers.state), we'd have to edit both lists. Instead, LookML provides a way to create named sets that we can maintain in one place and use in multiple places.
The following code creates a set customers.detail and points both counts to the same set of fields.
view: customers {
set: detail {
fields: [id, name, city] # creates named set customers.detail
}
measure: count {
type: count
drill_fields: [detail*] # show fields in the set "customers.detail"
}
measure: in_california_count {
type: count
filters: [state: "California"]
}
}
LookML sets are pretty powerful:
- Redeclaration of sets is additive — if you declare a set in multiple places, then Looker includes all the fields that were declared for the set in all locations.
- You can embed sets in other sets by typing the other set name, followed by an asterisk, like
setname*. - You can even remove elements from sets by placing a hyphen before the field name, like
-fieldname.
Customizing drill visualizations
If your Looker admin has enabled the Visual Drilling Labs feature, Look and Explore drill visualizations will not always default to a data table. In this case, you can customize the visualization that is displayed by using Liquid variables in the link parameter, as shown on the link parameter documentation page and in the More powerful data drilling Best Practices page.
Dashboards support visual drilling using the link parameter without the need to enable the Visual Drilling Labs feature.
Filtering result sets
LookML provides a set of filter operations that can be applied to fields and Explores to filter result sets before they are returned to the user.
always_filter on the Explore
Use always_filter to always apply a set of filters to any query run within an Explore. The filters will appear in the Looker UI, and though users can change the default filter value that you provide, they cannot remove the filters. Generally, these filters are used to remove data that you normally don't want to included. For example, suppose in the Orders Explore we only wanted to see orders that were complete or pending. We could add:
explore: orders {
view_name: order
filters: [status: "complete,pending"]
}
}
If the user wanted to see orders with other status values, they could set ORDERS Status to % in the UI.
sql_always_where on the Explore
If you want to apply a query restriction that users cannot change, you can use sql_always_where. In addition to queries run by human users, the restriction will apply to dashboards, scheduled Looks, and embedded information that relies on that Explore. A sql_always_where condition is not displayed to the user, unless they look at the underlying SQL of any queries that they create.
The following example prevents users from looking at orders before 2012-01-01:
# Using Looker references
explore: order {
sql_always_where: ${created_date} >= '2012-01-01' ;;
}
# Using raw SQL
explore: order {
sql_always_where: DATE(created_time) >= '2012-01-01' ;;
}
conditionally_filter on the Explore
Very large tables require some thought when querying, since unlimited queries can quickly become too burdensome on the database. LookML provides a way to address this in the form of conditionally_filter.
You use the conditionally_filter parameter to apply a filter to the query unless the user has already added a filter for one of the fields listed in the unless section.
The following example won't make any change to the user's query if the user applied a filter on one or more of these fields: created_date, shipped_time, shipped_date, orders.id, or customer.name. If the user didn't filter on any of those fields, Looker will automatically add a filter of 1 day on orders.created_time.
filters: [orders.created_time: "1 day"]
unless: [created_date, shipped_time, shipped_date, orders.id, customer.name]
}