As is probably common with relational databases, I am generating a report on a table with a structure that looks like this:
Event: Date Parameter Occurrences Site: Site_Id ---> Site_Id Name Section: Section_Id ---> Section_Id Name Group: Group_Id ---> Group_Id Name etc.
I want to allow users to filter by site, section and/or group but this is a statistical report (total number of occurrences by date and parameter) and the site, section and group do not actually appear in the report, so I am using templated filters in a derived table. The issue that I have is that the users will pick values for the site/section/group by name, not id (which is meaningless to them, of course) but this means that, to make the filters work, I will have to join the Site, Section and Group tables to the Event table to get the names that are used in the filter. There are two problems with this:
There are (literally) millions of entries in the unfiltered Event table so joining in other tables is costly.
The site, section and group names are filter-only fields so the joins to the Site, Section and Group tables are unnecessary when not filtering by those fields.
N.B. I tried using a templated filter with a “sql” parameter to add a WHERE clause to the query but, unfortunately, it did not work because the WHERE clause was applied to the result of the derived table whereas it needed to be embedded inside the derived table.
Our in-house reporting system (that we are replacing with Looker) shows the user the list of names but uses the id in the SQL select - e.g. “event.site_id IN (123,456,789)” - so, if there isn’t a way to do this in Looker, I would like to suggest a possible enhancement. You could add another “suggest_…” parameter (e.g. suggest_list) to be used with the suggest_explore and suggest_dimension parameters. For example:
- filter: site_name suggest_explore: site suggest_dimension: site.site_id suggest_list: site.name
In this example, the site name would appear in the filter drop-down list but the site id would be used in the SQL. This would make the join unnecessary and the report would run faster whether filtered or not.