End-user-defined dynamic tiers

  • 17 October 2019
  • 5 replies
  • 685 views

Userlevel 2

Looker’s tier dimension type is great for defining buckets for breaking out numeric dimensions. For example, how are 30-50yr olds behaving compared with the over 50s? By default, these tiers are fixed in LookML, and are consistent across all reports and dashboards.


But sometimes, we might want to compare different tiers based on a quick hunch or gut-feeling, and come up with our buckets on the fly. For example, how does our 30-50yr old group further break down into sub-groups (eg. 30-35, 35-40)?


Whats more, our less-technical dashboard users may want to do the same thing. Wouldn’t be great if we could just type in a list of numbers and get buckets based on this?


Hurray! Using LookML and Liquid, we can do exactly this. This pattern allows a user to input a comma-delimited string (eg 18, 30, 40, 50, 70) and Looker will generate a set of SQL-based buckets to reflect this. The new tier is a first-class dimension, and so can be used as a pivot, and with any combination of other dimensions and measures.



To do this, we’ll need to create a parameter of type string to capture the input, and another dimension age_compare_groups which will contain our auto-generated tiers, which will reference the parameter.


The following LookML contains everything you need 🔧


Note: The SQL syntax for this example may need to be adapted to suit your database dialect.


# A user's age. Just a number in our table
dimension: age {
type: number
sql: ${TABLE}.age ;;
}

# This is our parameter which our user will populate
parameter: age_buckets {
type: string
}

# This dimension uses liquid (the {%} stuff) to do some parsing and builds a case statement
dimension: age_compare_groups {
sql:
{% assign my_array = age_buckets._parameter_value | remove: "'" | split: "," %}
{% assign sort = '-1' %}
{% assign last_group_max_label = ' 0' %}

case
{%for element in my_array%}
{% assign sort = sort | plus: 1 %}

when ${age}<{{element}} then '{{sort}}. {{last_group_max_label}} < N < {{element}}'
{% assign last_group_max_label = element %}
{%endfor%}
{% assign sort = sort | plus: 1 %}

when ${age}>={{last_group_max_label}} then '{{sort}}. >= {{last_group_max_label}}'
else 'unknown'
end
;;
}

5 replies

Perfect!

thanks for the awesome code.

I have only one comment that I would be so glad If you can help me with.

the performance of the query is very slow.

Is there any way I can filter the data in the SQL code before running the Dynamic Buckets code?

Userlevel 7
Badge +1

Any filters you add to the explore would apply at the WHERE clause level, which would in turn filter any case logic in the SELECT clause— So adding explore filters should improve the query performance for sure.

Thanks a lot @izzy 

I will try to limit the results using filters, if the problem persists, I will post the code here.

Thank you @white this is just what I need and inspiring Lookml!

Reply