Benchmarking when restricting data access

Looker Tips & Resources Technical Tips & Tricks
1

In a classic embed use case, we use user attributes in conjunction with access_filter to make sure each customer only see their own data.
Consider this example where I have a user_attribute called brand_ua and my customers are segregated by the column brand.

In my explore, I would add an access filter:

Explore: order_items {
Access_filter: {
     User_attribute:brand_ua
     Field:brand
}

But what if we want customers to see other customers’ data for benchmarking purposes , i.e. “where other customers/your competitors are”?
when I do benchmarking, I usually use a parameter and a dynamic dimension, like this:

parameter: brand_selector {
type: unquoted
value_from_dimension: brand
}

dimension: benchmarking {
sql: CASE WHEN ${brand}='{{_parameter['brand']}}' THEN ${brand_name} ELSE 'Other Brands' END ;;
}

in this case, I don’t need a parameter, I will be using the user attribute value and inject it instead:

dimension: benchmarking {
sql: CASE WHEN ${brand}='{{_user_attribute['brand']}}' THEN ${brand} ELSE 'Other Brands' END ;;
}

But if I continue to use my access filter, all the other brands data will be filtered out. So I need a work around. This is where a custom sql_always_where comes in:

explore: order_items {   
   sql_always_where: {% if order_items.benchmarking._in_query %}   
      1=1 
   {% else %}  
      ${order_items.brand}= '{{ _user_attributes['brand_ua'] }}' 
   {% endif %};;

We only apply the user_atytribute security when the benchmarking dimension is not in the query, and when it is, all the data will be grouped by brand (make sure to add a similar snippet in the brand dimension as well or add an access_grant to it:

And voila!

Bonus points:

But what if we want the user attribute to have several brands?

dimension: benchmarking {
  sql: CASE
    WHEN ${brand} IN (                      
      {% assign comps = _user_attributes['brand_ua'] | split: "," %}
      {% for c in comps %}
        '{{ c | strip }}'                    
        {% unless forloop.last %}, {% endunless %}
      {% endfor %}
    )
    THEN ${brand}
    ELSE 'Other Brands'
  END ;;
}

and in the explore

explore: order_items {
  sql_always_where: {% if order_items.benchmarking._in_query %}
      1=1                                     
    {% else %
      {% assign comps = _user_attributes['brand_ua'] | split: "," %}
      ${order_items.brand} IN (
        {% for c in comps %}
          '{{ c | strip }}'
          {% unless forloop.last %}, {% endunless %}
        {% endfor %}
      )
    {% endif %};;
}