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!
