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! Go to Solution.
You can catch this query in the admin-queries page (or the system activity history explore).
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/):
You can catch this query in the admin-queries page (or the system activity history explore).
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
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.
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.