Case/when based dimension: Want to filter more efficiently

  • 14 November 2021
  • 1 reply

I have a dimension that is defined as follows:

  dimension: product_id_group_biz {
    label: "Product Business Group"
    type: string
    sql: case when ${product_id_group}='Marketplace' and ${share} <= 0.5 then 'EMP'
              when ${product_id_group}='Marketplace' and ${share} > 0.5 then 'MP'
              when ${product_id_group}=’Premium’ then ‘Premium’
              when ${product_id_group}=’PRO’ then ‘PRO’
              end ;;

it can alternatively be defined using a LookML CASE statement as follows:

dimension: product_id_group_biz_2 {
  type: string
  case: {
    when: {
      sql: ${product_id_group}='Premium' ;;
      label: "Premium"
    when: {
      sql: ${product_id_group}='PRO' ;;
      label: "PRO"
    when: {
      sql: ${product_id_group}='Marketplace' and ${share} < 0.5  ;;
      label: "EMP"
    when: {
      sql: ${product_id_group}='Marketplace' and ${share} >= 0.5  ;;
      label: "MP"

In both cases, when I filter this dimension to be a specific value, in the generated SQL I see a condition that includes the entire case/when statement and conditions that case/when statement to be equal to the value I specify in the filter. 

BUT WHAT I REALLY WANT is that if I filter the dimension to be a specific value, that the second dimension definition would enable Looker to generate much more efficient SQL, by taking the specific SQL for that WHEN value instead of filtering the case/when value. This would be much more efficient especially if I have essentially indexed the field(s) that is used in the SQL for most of the WHEN clauses.

In other words, I want Looker to take a dimension based on a CASE and understand that my CASE conditions are all independent of each other, not ordered and without an ELSE, and know that a filter can be implemented using just the specific SQL.

Is there any way to do this? Meaning to indicate to Looker that this dimension can be filtered using the specific SQL conditions and not the whole case/when?

1 reply

To clarify, for the above example, if I filter the dimension to be “PRO” my SQL will say WHERE case when…...when…..when…..end=’PRO’ but it should be able to say WHERE ${product_id_group}=’PRO’ by using the SQL snippet specific to that WHEN element. Obviously this is only true when there is no ELSE and when the WHEN terms are unordered (independent). Maybe there can be a TERMS_INDEPENDENT:YES field in a case dimension?