Question

Join table only if filtering

  • 18 February 2016
  • 6 replies
  • 230 views

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?


Thanks,

Andrew


6 replies

Userlevel 6
Badge

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:


- view: event_list
derived_table:
sql: |
SELECT
ev.event_date,
ev.description,
ev.occurrences
FROM
event ev,
detail dt,
site st,
section sc
WHERE
ev.detail_id = dt.detail_id AND
dt.site_id = st.site_id AND
dt.section_id = sc.section_id AND
{% condition site_filter %} st.name {% endcondition %} AND
{% condition section_filter %} sc.name {% endcondition %}

fields:

# Filters

- filter: site_filter
type: string
# suggest_explore: site_list
# suggest_dimension: site_list.site_name

- filter: section_filter
type: string
# suggest_explore: section_list
# suggest_dimension: section_list.section_name

# Fields

- dimension_group: event_date
type: time
timeframes: [date]
convert_tz: false
sql: ${TABLE}.EVENT_DATE

- dimension: description
type: string
sql: ${TABLE}.DESCRIPTION

- dimension: occurrences
type: number
value_format_name: decimal_0
sql: ${TABLE}.OCCURRENCES

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.


Andrew

Userlevel 6
Badge

Hi Andrew,


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.


- explore: event_list
joins:
- join: detail
sql_on: ${detail_id} = ${detail.detail_id}
relationship: many_to_one
- join: section
sql_on: ${section_id} = ${section.section_id}
relationship: many_to_one
- join: site
sql_on: ${detail.site_id} = ${site.site_id}
relationship: many_to_one

- view: event_list
fields:
- dimension_group: event_date
type: time
timeframes: [date]
convert_tz: false
sql: ${TABLE}.EVENT_DATE

- dimension: description
type: string
sql: ${TABLE}.DESCRIPTION

- dimension: occurrences
type: number
value_format_name: decimal_0
sql: ${TABLE}.OCCURRENCES
- dimension: detail_id
sql: ${TABLE}.detail_id
- dimension: section_id
sql: ${TABLE}.section_id

- view: detail
fields:
- dimension: detail_id
primary_key: true
sql: ${TABLE}.detail_id

- view: site
fields:
- dimension: site_id
primary_key: true
sql: ${TABLE}.site_id

- dimension: name
sql: ${TABLE}.name


- view: section
fields:
- dimension: section_id
primary_key: true
sql: ${TABLE}.detail_id

- dimension: name
sql: ${TABLE}.name

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:




  1. 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!)




  2. It is possible to change the source of the fields without anything changing from the user’s perspective.




Many thanks for your help.

Userlevel 6
Badge

Glad to Help!


You can hide the structure using ‘view_label’ for complete control of how fields are labeled and grouped.



Userlevel 3

Andrew, as @lloydtabb pointed out, you can use a combination of view_label and fields to abstract the underlying material table structure. For example,


- explore: event_list
fields: [event_list.event_date,event_list.description,event_list.occurrences,event_list.detail_id,detail.detail_id]
joins:
- join: detail
view_label: 'Event List'
sql_on: ${detail_id} = ${detail.detail_id}
relationship: many_to_one

This will make only the 5 fields available in the explore:



  • event_date

  • description

  • occurrences

  • detail_id

  • detail_id (belonging to detail view)


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

hidden: true

sql: ${TABLE}.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.

Reply