Question

Transforms on Filter Inputs

  • 21 June 2017
  • 1 reply
  • 237 views

Userlevel 2

Not sure if this is an unusual request. Some of our most common filters in dashboards are actually business-user friendly versions of some column’s values. For instance, folks often want to filter on AWS region in a table, and they may be familiar with a “us-west” or “us-east” convention. However, internally, our system uses a cryptic numeric code to represent AWS region. These are the values that reside in our tables.


The go-to Looker pattern would be to define a dimension that maps the user unfriendly version into a more friendly version. This could be done in a number of ways. For my example below, imagine I have a UDF that does a big case statement, mapping the original region codes to user-friendly region names.


The issue is that, over large data sets, there’s a rather sizable performance differential between transforming the column and comparing it to our user-input filter value vs. leaving the column alone and actually transforming the user’s filter input.


--the current Looker approach
select count(*) as number_of_events
from public.events
where deployment_decoder(deployment) = 'us-west'

--a more performant approach
select count(*) as number_of_events
from public.events
where deployment = region_decoder('us-west')

I believe this reasoning is similar to how Looker constructs its time filters: all of the nasty transformations are on the right-hand side of the predicate.


So I guess what I’m asking is this: it’d be pretty cool to have a way to actually translate user’s filter values with some SQL function/operation instead of defining a dimension-level transform to match user inputs.


If I’m missing an obvious (existing) solution, do let me know. Thanks!


1 reply

Userlevel 3

Hey Scott. You’re not missing anything obvious, this isn’t natively supported but it is possible!


One of the reasons we put transformations on the left side is so that we can support filters other than a straight equalities like in and like. I don’t think it would be easy to filter to “any region that starts with US or EU” if we needed to transform the user input instead of the database column.


That said, let’s assume you always want to filter on exactly one region. In that case, this setup should work:


Note that this uses parameters, which are still in beta and subject to change.


 dimension: region {
type: number
sql: ${TABLE}. region_code
can_filter: no ---- this is optional but prevents redundant filtering
}
filter: region_filter {
type: string
suggestions: [us-east, us-west, eu-ireland, emea-singapore]
sql: ${region} = region_decoder( {% parameter region_filter %} ) ;;
}

This pattern also highlights the fact that if we’re transforming the user input instead of the database column, you have to have one filter field for filtering on region, and another for adding it as a column to an explore.


If you’re building this filter into a dashboard, I’d highly recommend incorporating our hot off the presses in 4.14 [single value dashboard filters] (https://looker.com/docs/dashboards/working-with-user-dashboards#customize_filter_to_limit_the_number_of_values) to ensure users only select one value for the filter.


In short, there are some usability tradeoffs for using this in Explore, but if performance optimization is critical this should get you what you need, and it will work really well in a dashboard.


Let us know how it goes!

-Zev

Reply