Solved

Do filter suggestions just query a view, or the whole explore?

  • 13 July 2021
  • 5 replies
  • 46 views

Userlevel 1

When a user clicks a dashboard filter and Looker runs a quick query to return the distinct options that a user can select for that filter, does it just query the given view’s SQL table, or does it generate a query for the the whole explore?

 

For example, here’s an example explore:

explore: people {

join: countries {
type: inner
sql: ${countries.id} = ${people.country_id}
relationship: many_to_one
}
}

 

Let’s now say that on a dashboard, I set up a filter for the `people` explore and chose a dimension from the `countries` view called “Country Code”.

 

Is Looker determining a set of options by simply querying the countries table? Such as:

SELECT DISTINCT country_code
FROM bigquery_database.countries

 

Or is Looker setting up the entire explore? Such as:

SELECT DISTINCT countries.country_code
FROM bigquery_database.people AS people
INNER JOIN bigquery_database.countries AS countries ON countries.id = people.country_id

 

icon

Best answer by IanT 14 July 2021, 09:56

You can catch this query in the admin-queries page (or the system activity history explore). 

View original

5 replies

Userlevel 1

I tried to find the answer on my own but I don’t see a place to find where the query is displayed for a given filter’s suggestions. I do see an API request that occurs which lists the explore name (after /views/):

https://pricespiderstaging.cloud.looker.com/api/internal/models/people_model/views/people/fields/countries.country_code/suggestions?term=

Userlevel 6
Badge

You can catch this query in the admin-queries page (or the system activity history explore). 

Userlevel 1

Thanks! I found out that in order to retrieve the `country_code` values, it does indeed query the entire explore, as opposed to just that table:

SELECT DISTINCT countries.country_code
FROM bigquery_database.people AS people
INNER JOIN bigquery_database.countries AS countries ON countries.id = people.country_id

 

Userlevel 6
Badge

You may be aware of this already but you can improve performance around this (although it will show all options) by using https://docs.looker.com/reference/field-params/suggest_explore

Be warned that there is some draw backs such as cross filtering getting upset as it sees multiple explores being used on a dashboard even though they are based on the driving main explore.

Userlevel 1

Thank you–I was just about to start implementing those. Thanks for potential warning about the effects on cross-filtering; I will make sure to keep that in mind.

Reply