In our last article we gave an introduction on Looker and highlighted areas where Looker differentiates itself.
In this article, we discuss Looker’s proprietary modeling language called LookML that lets data teams define the relationships in their database so that business users can explore, save and download data without needing to know the SQL query language. Looker uses a model written in LookML to construct SQL queries against a particular database.
LookML is a language for describing dimensions, aggregates, calculations and data relationships in a SQL database. LookML separates structure from content, so the query structure (Model having join and relationship information) is independent of the query content (the columns to access, derived fields, aggregate functions and filtering expressions etc.)
Once Model and Views are ready as part of LookML, user can start exploring the data using dimensions and measures available as part of the different explores defined. By default, Looker provides capability to filter and limit the data based on dimension & measures which can only modify the outermost WHERE or HAVING clauses in SQL query. However, there are many use cases, where user would want to manipulate other parts of the SQL and get their analysis done e.g. Adjusting part of a derived table, adjusting which database table gets queried or creating multipurpose dimensions. These scenarios can easily be achieved with the use of parameter or templated filters.
In this blog post we have explained how to implement Liquid parameters and Templated filters in detail and have also covered various scenarios where they can be handy.
Templated filters and Liquid parameters
Templated filters and Liquid parameters make use of the liquid templating language to insert user input into SQL queries. First, we use LookML parameter/templated filter to create a filter only field for users to interact with and then, use a Liquid variable to inject the user input into SQL queries.
Many times, a data visualization developer comes across a scenario where s/he needs to develop a viz filter for dynamic dimension selection over a measure OR dynamic measure selection over a dimension. This use case is very important, as implementing the same avoids creation of similar charts multiple times with varying dimensions /measures and helps in reducing the clutter in a dashboard too while covering the desired analysis to be done.
Below are few examples to understand this better –
- Sales needs to be analyzed by Regions, Age-groups and brands.
- Total Registrations by Visitor type and Registration Type
- Revenue, Profit and Total gross margin across last 5 years
Above scenarios can be achieved with the use of parameters in Looker by creating 1 chart only with the use of Parameter as Filter only field. A filter to a Look/Dashboard must be created from Filter only field. Based on User’s selection underlying dimension/measure will be selected from grouped dimension/measure created as part of a View.
Let’s see how this can be implemented in Looker using Liquid statements
Liquid is a templating language that you can use in Looker to create more dynamic content. Liquid statements are built from variables, filters, and tags. Variables contain information that you want to use, and the variables that Looker provides are described below. You can further modify those values by using filters and tag.
Liquid can be used with many parameters in LookML. Including Action, Html, Label, Link, Sql or Sql_on. There are two ways to make use of a Liquid variable:
- Output Syntax: This type of usage can insert text and is probably the most common way to use Liquid in Looker. In this method, you enclose the Liquid variable in two curly braces. For example: {{ value }}
- Tag Syntax: This type of usage usually doesn’t insert text; instead, it is for logical comparisons and other Liquid operations. In this method, you enclose the Liquid variable in one curly brace with a single percent sign. For example: {% if value > 100 %}
Now that we are clear on the basics, let’s solve a use case on the same. In the code block below, we are using the parameter which allows user to filter on values and dynamically update LookML definitions.
Fig 1.1
Note: As per need, many more values can be passed in parameter using allowed_value.
Select_dimension is the string type parameter which needs to be created first. (Ref Fig 1.1) The parameter is surfaced as a filter-only field in the UI and the user will see only labels e.g. Registration Type, Visitor Type but upon selection of a label corresponding value is passed for logical calculation.
Fig 1.2
Now, to pass selected value from Filter (Parameter) further into a logical statement we are using {% parameter_name._parameter_value %} Liquid variable. We created a grouped dimension named “selected_dimension” and injecting the selected parameter’s value using liquid variable under CASE statement.
Note: If underlying fields are coming from different Views, you need to pass field’s full name e.g. ${View_name.Field_name}
Fig 1.3 (Visitor Type selected as Dimension)
Similarly, if requirement is to create dynamic measure, we can create a parameter for measure selection and then can achieve the expected behaviours using liquid variable in logical statement.
Parameters are very powerful and there are plenty scenarios where parameters can be quite useful. We have elaborated on the most common and important scenarios here:
- If measure type (e.g. SUM, AVG, MAX etc.) required to be selected dynamically to provide more flexibility in filter criteria for a single visualization. This will allow user to quickly toggle between various aggregations for a specific variable.
We can create parameters similar to below:
- If data for one variable like country is stored in multiple tables, each containing data for one country then we can make use of parameters to reference multiple tables rather than appending the data and creating a huge data file. In this case one can see tables such as proj:01.in_sessions, proj:02.in_sessions, proj:03.in_sessions. 01, 02, 03 prefixes represent data for different countries.
With the use of Parameter, we can provide filter that allow users to select the country from multiple tables with an intuitive name. Below is the sample code.
- Top N items vs others – Often Users want to find out the top N performing products and compare those with the rest of the products.
One way to achieve this is to hardcode certain buckets, however the best way is to use parameter with the flexibility for User to select value of N and then label the remainder as “Other”.
In the context of an online retail store, where we want to find out how the top N products are performing, we can use a derived table where we rank each product based on the number of sales and further with the use of parameter, can easily calculate the values dynamically.
- Another interesting use case for parameters is to use it to conditionally format the results based on a threshold (e.g. threshold on sale price).
With the use of parameter, User can set the sensitivity in the UI. Further we can reference the parameter in the html of the measure and conditionally format the result. This will allow the user to tweak the threshold value from time to time and still be able to visually interpret the insights.
- Parameters can also be used to dynamically select the drill down fields based on the user selection. Normally, the drill fields are hard coded but with the help of parameters the drill fields can be changed on the fly based on the user input.
Sample below:
Hope you appreciate the use of parameters in Looker and have a good understanding of where they can be used.
We have implemented multiple Looker dashboards which include a lot of advanced looker functionalities. To know more and see our work, please contact us.
At Acrotrend we help our clients get the right insights from the data. If you are struggling to get the right insights from your data or want to develop BI reports for your organisation, get in touch today.
References
https://docs.looker.com/reference/field-params/parameter
https://docs.looker.com/reference/liquid-variables