explore_source

This page refers to the explore_source parameter that is a subparameter of derived_table.

explore_source can also be a subparameter of test, described on the test parameter documentation page.

Usage

derived_table: customer_order_facts {
  explore_source:  orders {
    column:  customer_id {
      field:  orders.customer_id
    }
    column:  order_amount {
      field:  orders.sale_price
    }
    column:  item_qty {
      field:  orders.number_items
    }
    derived_column:  average_item_price {
      sql:  order_amount / item_qty  ;;
    }
    timezone:  "America/Los_Angeles"
  }
}

Definition

There are two ways to make a derived table, which you can use as if it were a normal table in your database — native derived tables, which are defined using LookML parameters, and SQL-based derived tables, which are defined using SQL query statements.

The explore_source parameter is used for native derived tables. In this parameter you define which columns will be included in a native derived table, any filters to be applied to the native derived table, whether to limit or sort the native derived table rows, and whether to convert the native derived table's time-based fields to a different time zone.

Defining a native derived table

You can use a variety of parameters in a native derived table, many of them optional. The syntax for defining a native derived table is shown below, followed by additional information about each parameter.

explore_source: identifier {
  bind_all_filters: yes
  column: identifier {
    field: field_name
  }
  derived_column: identifier {
    sql: SQL expression ;;
  }
  expression_custom_filter: [custom filter expression]
  filters: [field_name_1: "string", field_name_2: "string", ...]
  limit: number
  sorts: [field_name_1: asc | desc, field_name_2: asc | desc, ...]
  timezone: "string"
}

Where:

bind_filters: {
  to_field: users.created_date
  from_field: user_dt.filter_date
}

column: cust_id {
  field: orders.customer_id
}

derived_column: average_order {
  sql: order_amount / item_qty ;;
}

Examples

The following definitions provide basic examples of native derived tables.

Create a user_order_facts native derived table:

view: user_order_facts {
  derived_table: {
    explore_source: order_items {
      column: user_id {
        field: order_items.user_id
      }
      column: lifetime_number_of_orders {
        field: order_items.order_count
      }
      column: lifetime_customer_value {
        field: order_items.total_revenue
      }
    }
  }
  # Define the view's fields as desired
  dimension: user_id {
    hidden: yes
  }
  dimension: lifetime_number_of_orders {
    type: number
  }
  dimension: lifetime_customer_value {
    type: number
  }
}

You can add filters to create a user_90_day_facts native derived table:

view: user_90_day_facts {
  derived_table: {
    explore_source: order_items {
      column: user_id {
        field: order_items.user_id
      }
      column: number_of_orders_90_day {
        field: order_items.order_count
      }
      column: customer_value_90_day {
        field: order_items.total_revenue
      }
      filters: [order_items.created_date: "90 days"]
    }
  }
  # Add define view's fields as desired
  dimension: user_id {
    hidden: yes
  }
  dimension: number_of_orders_90_day {
    type: number
  }
  dimension: customer_value_90_day {
    type: number
  }
}

Creating filters for Development Mode

There are situations when the native derived table you're creating takes a long time to generate, which can be time-consuming if you are testing lots of changes in Development Mode. For these cases, you can use dev_filters to create smaller development versions of a native derived table:

view: e_faa_pdt {
  derived_table: {
  ...
    datagroup_trigger: e_faa_shared_datagroup
    explore_source: flights {
      dev_filters: [flights.event_date: "90 days"]
      filters: [flights.event_date: "2 years", flights.airport_name: "Yucca Valley Airport"]
      column: id {}
      column: airport_name {}
      column: event_date {}
    }
  }
...
}

This example includes a dev_filters parameter that filters the data to the last 90 days and a filters parameter that filters the data to the last 2 years and to the Yucca Valley Airport. The dev_filters parameter acts in conjunction with the filters parameter so that all filters are applied to the development version of the table. If both dev_filters and filters specify filters for the same column, the dev_filters take precedence for the development version of the table. In this example, the development version of the table will filter the data to the last 90 days for the Yucca Valley Airport.

If a native derived table has the dev_filters parameter, the development table cannot be used as the production version, since the development version has an abbreviated dataset. If this is the case, after you've finished developing the table and before you deploy your changes, you can comment out the dev_filters parameter and then query the table in Development Mode. Looker will then build a full version of the table that can be used for production when you deploy your changes. See the Derived tables in Looker documentation page for more details about using development tables in production.

Note that the converse situation is true: If you have a native derived table with the dev_filters parameter and you query it in Development Mode, Looker can use the production table to answer your Development Mode query. This is true unless you change the definition of the table and then query the table in Development Mode, at which point Looker will build a development table to answer the query.

Passing filters into a native derived table

When you include a native derived table in an Explore, you can take runtime filters from the Explore and apply them to the native derived table query. You do this by adding either the bind_all_filters or the bind_filters parameter to the explore_source of the native derived table.

When passing runtime filters from an Explore to a native derived table subquery, the runtime filter must be in a view joined to the same Explore as the native derived table. Also, because the native derived table must regenerate at runtime in order to incorporate the runtime filters from the Explore, the native derived table can't be a persistent derived table (PDT).

Using bind_all_filters

The easiest way to pass filters from an Explore to a native derived table subquery is to specify bind_all_filters: yes in the native derived table's explore_source parameter. This will pass all of an Explore's runtime filters into the native derived table subquery.

A native derived table with bind_all_filters: yes must be joined into the same Explore that is specified in the native derived table's explore_source parameter. If you want to use the native derived table in a different Explore, use the bind_filters parameter instead, as described in this section.

Here is the LookML for a native derived table with bind_all_filters: yes:

view: top_10_simple_item_names {
  view_label: "Top 10s"
  derived_table: {
    explore_source: order_items {
      column: total_sale_price {
        field: order_items.total_sale_price
      }
      column: item_name {
        field: products.item_name
      }
      derived_column: rank {
        sql: RANK() OVER (ORDER BY total_sale_price DESC) ;;
      }
      bind_all_filters: yes
      sorts: [order_items.total_sale_price: desc]
      timezone: "query_timezone"
      limit: 10
    }
  }
  dimension: item_name {
    group_label: "Simple Example"
  }
  dimension: rank {
    type: number
    group_label: "Simple Example"
  }
  dimension: item_name_ranked {
    group_label: "Simple Example"
    order_by_field: rank
    type: string
    sql: ${rank} || ') ' || ${item_name} ;;
  }
}

In the native derived table's view above, the explore_source is order_items. Here is the LookML for the order_items Explore where the native derived table is joined into the Explore:

explore: order_items {
  ...
  join: top_10_simple_item_names {
    type: inner
    relationship: many_to_one
    sql_on: ${products.item_name} = ${top_10_simple_item_names.item_name} ;;
  }
}

To see this example in action, check out the [Analytic Block] – Pivot by Top X – Introducing bind_all_filters: yes Community post.

Using bind_filters

The bind_filters subparameter of explore_source passes a specific filter from the Explore query into the native derived table subquery:

• The to_field is the field in the native derived table to which the filter is applied. The to_field must be a field from the underlying explore_source.
• The from_field specifies the field in the Explore from which to get the filter, if the user specifies a filter at runtime.

At runtime, any filters on the from_field in the Explore will be passed into the to_field in the native derived table subquery.

The LookML below shows a native derived table with bind_filters. With this setup, Looker will take any filter applied to the filtered_lookml_dt.filter_date field in the Explore and apply the filter to the users.created_date field in the native derived table.

derived_table: {
  explore_source: order_items {
    bind_filters: {
      to_field: users.created_date
      from_field: filtered_lookml_dt.filter_date
    . . .
    }
  }
}

Things to consider

Use only one explore_source parameter

Each native derived table accepts only one explore_source parameter. Subsequent explore_source parameters will not raise LookML validation errors, but they will override previous explore_source parameters.

To create columns from fields in different views, first join together the different views in the same Explore. Then use that Explore for explore_source.

Use include statements to enable referencing fields

When creating a native derived table, you must include the file containing the Explore's definition. The best way to do that is to define the Explore in a separate Explore file, as described in the documentation for Creating Explore Files.

If you create a separate Explore file:

• The native derived table's view file should include the Explore's file. For example:
  • include: "/explores/order_items.explore.lkml"
• The Explore's file should include the view files that it needs. For example:
  • include: "/views/order_items.view.lkml"
  • include: "/views/users.view.lkml"
• The model should include the Explore's file. For example:
  • include: "/explores/order_items.explore.lkml"