My Looker setup contains dashboards that have many filters (usually between 6-10) in order to enable self-serve analytics reporting for my users (so that they may optionally filter by date, user name, browser, etc.) – this means most of these filters are often unused for any particular query, but any individual filter could be required due to the “long tail” of reporting that needs to be handled.
I have come across an interesting wrinkle that is hindering query performance across the board – namely, that setting a join-dependent filter with no query data will generate SQL that performs the join required for that filter. This results in unnecessary joins that hinder performance.
In the below screenshot, I am Exploring the Visit view (which directly mirrors an underlying redshift table), which can join on the Client, Country, and Users tables. I have set up filters from these joined tables, but not yet entered any query data to filter on.
The Look-ML generated SQL contains joins on these tables, even though I am not actually querying these tables for any data.
Can I set Looker to only create these joins if their underlying filters have data? If not, how can I structure my dashboards to solve this problem, while still meeting the requirements of providing “long tail” self-serve analytics reporting capabilities?