Join table only if filtering

(Andrew Paterson) #1

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

0 Likes

(lloyd tabb) #2

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)?

0 Likes

(Andrew Paterson) #3

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

0 Likes

(lloyd tabb) #4

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

1 Like

(Andrew Paterson) #5

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.

0 Likes

(lloyd tabb) #6

Glad to Help!

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

0 Likes

(Segah A. Mir) #7

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.

0 Likes