This page describes the following common patterns in LookML:
- Labeling fields (and names in the UI)
- Filtering counts by a dimension
- Percentages
- Using sets for drill-down details
- Filtering result sets
Labeling fields (and names in the UI)
Looker converts LookML field names into the strings that the UI displays 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, both field names are bolded and the view name is capitalized (ORDERS Amount) to make the discussion clearer.
If you want a field to have a different name than its column name in a table, change the field name and use the sql
parameter to link the field to the appropriate column in the table. In the following example, the airports
table has a cntrl_twr
column. 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
}
}
You can 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
You can 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 that is filtered by some dimensional value. For example, you could make a new measure and name it 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 following format to compute 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. To avoid divide-by-zero errors, the NULLIF(value, 0)
converts a zero value to null, which makes the result null and avoids 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 you click a measure in the UI, Looker creates a new query to localize the set of data that makes up the measure. Each value for each dimension on the row in the table gets added to
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 you are measuring ORDERS Count by USERS State in the last week. In Looker, the query would look something like the following:
USERS State | ORDERS Count |
---|---|
California | 24 |
Texas | 5 |
Colorado | 4 |
Florida | 4 |
Illinois | 4 |
If you click 24 in the California row, you might expect to see the 24 orders that came from California. Although Looker does add the filter USERS State: California, Looker doesn't know which fields you want to show in the order. You would first 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. You use sets to give Looker the following information:
- The fields that you want to show when drilling into a count or another measure
- The fields that you want to import when you join a view
- The fields that you want to be indexed in an Explore
Because the same set can be used in many places in a model, Looker provides several methods for creating sets.
Literal sets
A literal set is a straightforward way to define a set in LookML, particularly when the set is used only once. A literal set is created by declaring the set as an array. You can declare literal sets by using []
.
Consider the following example:
view: customers {
dimension: id {
primary_key: yes
}
measure: count {
type: count
}
dimension: city {}
dimension: state {}
dimension: name {}
}
In this example, the fields that you want to show are id
, name
, and city
.
In the measure, you can declare a literal array as follows:
measure: count {
type: count
drill_fields: [id, name, city]
}
Named sets
Suppose that two counts are defined in the customers
view: count
and in_california_count
. When a user drills into either the Count field or the In California Count field in an Explore, you want to display the fields id
, name
, and city
.
Initially, declaring these fields literally might seem sufficient:
view: customers {
measure: count {
type: count
drill_fields: [id, name, city]
}
measure: in_california_count {
type: count
filters: [state: "California"]
drill_fields: [id, name, city]
}
}
However, if you wanted to add a new field (such as customers.state
), you would have to edit both lists, unless you used the set
parameter to create named sets that you 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"]
drill_fields: [detail*] # show fields in the set "customers.detail"
}
}
LookML sets are powerful in the following ways:
- 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 within other sets by typing the name of the other set followed by an asterisk, for example,
setname*
. - You can remove elements from sets by placing a hyphen before the field name, for example,
-fieldname
.
Customizing drill visualizations
If your Looker admin has enabled the Visual Drilling Labs feature, Look and Explore drill visualizations won't 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 on 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 that is run within an Explore. The filters will appear in the Looker UI and, although 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 include. For example, suppose that in the Orders Explore, you only wanted to see orders that were complete or pending. You could add the following LookML code:
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]
}