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

Ryan_PS
Participant III

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
Solved Solved
0 5 484
1 ACCEPTED SOLUTION

IanT
Participant V

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

View solution in original post

5 REPLIES 5

Ryan_PS
Participant III

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/cou...

IanT
Participant V

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

Ryan_PS
Participant III

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

IanT
Participant V

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.

Ryan_PS
Participant III

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.

Top Labels in this Space
Top Solution Authors