I have a derived table from a set of tables structured like the following:
Event --> Detail --> Site, Section, …
All of the details that I need are in the Event table and I have templated filters on some of those fields, but I also want to allow the users to filter on site, section, etc. The issue is that, if there is no filtering, the join to the Detail table (and possibly the Site, Section, etc. tables) is unnecessary. As there are (literally) millions of entries in the Event table, the superfluous joins are costly if there is no filtering, but I cannot see a way to do a join only if filtering on the relevant fields is requested. Is this possible in Looker?
Looker is pretty good at keeping you from having to join in tables when you don’t need them. If you’re not referencing anything in the Detail table, Looker shouldn’t be writing a join for you. Can you paste the relevant LookML here (the explore, the views with the templated filters)?
The view is:
The explore is simply:
- explore: event_list
If there isn’t a simple fix, I have just thought of a possible alternative way to do this. Perhaps if I defined two new derived table views, one of which joined the Detail table to Site and the other which joined the Detail table to Section, then I could join to those views in the event_list view derived table instead of the explicit joins to Detail, Site and Section. The potential downside is that, if they filter on both site and section, it will join to the Detail table twice (once per filter) but that may be a price worth paying to avoid the join when there is no filtering.
Many thanks for your help.
I don’t think you need a derived table here or templated filters. LookML lets you express this all very simply and the joins will only happen when the fitlers on site.name or section.name are set.
I had simplified my example for the sake of clarity but I thought that my specific situation was too complicated to do without derived tables; having looked at your answer, however, I think that I was wrong about that, so I will follow your suggestion and see how far I get.
Note: There is one advantage of derived tables, though: it is possible to hide the underlying database structure from end-users. This is useful for two reasons:
It should not be necessary for users to know which field is in which table (in fact, exposing this level of detail can be dangerous!)
It is possible to change the source of the fields without anything changing from the user’s perspective.
Many thanks for your help.
Glad to Help!
You can hide the structure using ‘view_label’ for complete control of how fields are labeled and grouped.
@lloydtabb pointed out, you can use a combination of
fieldsto abstract the underlying material table structure. For example,
This will make only the 5 fields available in the explore:
In addition, we probably don’t need to surface detail_id from the event_list table since whenever we end up using it, we also pull data from details table. So, let’s hide it:
``- dimension: detail_id
This will still make it available for us to use in the model (for joins, and what-not), but now we won’t unnecessarily expose it to the end users.