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:
We will rename the 'cntrl_twr' dimension to be human readable.
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:
Filters can use any expression. If you wanted a field that counted users from the EU, you could use something like this:
If you want to filter with a mathematical expression, be sure to enclose it in double quotes:
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.
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.
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|
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.
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:
The fields we'd like to show are id, name, and city.
In the measure, we could simply declare a literal array.
In old LookML, based on YAML, you can also use a dash (
-) to define the same array, as shown here:
For sets that are only used once, declaring them literally is simple and easily understood.
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:
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.
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
- You can even remove elements from sets by placing a hyphen before the field name, like
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
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:
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:
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
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
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:
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.