Advanced LookML Concepts

LookML
Version

On this Page
Docs Menu

This page covers more advanced aspects of programming LookML.

Labeling Fields (and Names in the UI)

Looker converts LookML field names into strings displayed in the UI by combining the view name and the field’s short name. For example, Orders Amount. On this page, 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’s Generator would generate the following declaration:

- view: airports fields: - 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
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 fields: - 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
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 Us Count:

- view: users fields: - dimension: country - view: orders fields: - dimension: id primary_key: true sql: ${TABLE}.id - measure: count type: count drill_fields: detail - measure: us_count type: count # COUNT(CASE WHEN user.country = ‘US’ THEN 1 ELSE NULL END) drill_fields: detail # Also, when drilling, adds the filter users.country=’US’ filters: users.country: 'US'
view: users { dimension: country {} } view: orders { dimension: id { primary_key: yes sql: ${TABLE}.id ;; } measure: count { type: count drill_fields: [detail] } measure: us_count { type: count # COUNT(CASE WHEN user.country = ‘US’ THEN 1 ELSE NULL END) drill_fields: [detail] # Also, when drilling, adds the filter users.country=’US’ filters: { field: users.country value: "US" } } }

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 user.country IN ‘UK’,’FR’,’ES’ THEN 1 ELSE NULL END) drill_fields: detail filters: users.country: 'UK,FR,ES'
measure: eu_count { type: count # COUNT(CASE WHEN user.country IN ‘UK’,’FR’,’ES’ THEN 1 ELSE NULL END) drill_fields: [detail] filters: { field: users.country value: "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'
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: { field: order.value value: ">100" } }

Percentages

Many key performance indicators (KPI) 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'
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: { field: returned value: "Yes" } } measure: percent_returned { type: number sql: 100.0 * ${returned_count} / NULLIF(${count}, 0) ;; value_format: "0.00" }

NOTE: 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 StateORDERS Count
California24
Texas5
Colorado4
Florida4
Illinois4

If we click on the 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 need to declare that in your model. We’ll need a set for that.

In LookML a set is a list of field names (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 a 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 created by simply declaring the set as an array. You can declare literal sets by using ‘[]’.

Given this example:

- view: customers fields: - dimension: id primary_key: true - measure: count type: count - dimension: city - dimension: state - dimension: name
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]
measure: count { type: count drill_fields: [id, name, city] }

In old LookML, based on YAML, you can also use a dash (-) to define the same array, as shown here:

- 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 fields: - measure: count type: count drill_fields: [id, name, city] - measure: in_california_count type: count drill_fields: [id, name, city] filters: state: 'California'
view: customers { measure: count { type: count drill_fields: [id, name, city] } measure: in_california_count { type: count drill_fields: [id, name, city] filters: { field: state value: "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 code below creates a set customers.detail and points both counts to the same set of fields.

- view: customers   sets: detail: [id, name, city] # creates named set customers.detail   fields: - measure: count type: count drill_fields: detail* # show fields in the set "customers.detail" - measure: in_california_count type: count drill_fields: detail* # show fields in the set "customers.detail" filters: state: 'California'
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 drill_fields: [detail*] # show fields in the set "customers.detail" filters: { field: state value: "California" } } }

LookML sets are pretty powerful:

  • Redeclaration of sets are additive — if you declare a set in multiple places, then Looker includes all of the fields 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.

Read the full sets reference

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 orders, we only wanted to see orders that were complete or pending. We could add:

- explore: orders view: order always_filter: status: 'complete,pending'
explore: orders { view_name: order always_filter: { filters: { field: status value: "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'
# 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 on orders.created_time of 1 day.

conditionally_filter: orders.created_time: '1 day' unless: - created_date - shipped_time - shipped_date - orders.id - customer.name
conditionally_filter: { filters: { field: orders.created_time value: "1 day" } unless: [created_date, shipped_time, shipped_date, orders.id, customer.name] }

Still have questions?
Go to Discourse - or - Email Support
Top