User Guide Getting Started Help Center Documentation Community Training
New LookML
Old LookML
New LookML
Looker
  
English
Français
Deutsch
日本語
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, as described on the test parameter’s 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”
  }
}

Hierarchy

explore_source

Default Value

None

Accepts

The identifier of the Explore from which the native derived table (NDT) is derived, plus subparameters defining the NDT.

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 defined using LookML parameters and SQL-based derived tables defined using SQL query statements.

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

Examples

The following definitions provide basic examples of native derived tables.

Create a user_order_facts NDT:

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 NDT:

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: { field: order_items.created_date value: "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} }

Defining a Native Derived Table

You can use a variety of parameters in an NDT, many of them optional. The syntax for defining an NDT 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: field_name value: "string" } limit: number sort: { desc: boolean field: field_name } timezone: "string" }

Where:

Parameter Name Description Example
bind_filters Passes a filter from the Explore query into the NDT subquery. To set this up, use the from_field subparameter to specify a field defined in the NDT view or accessible in the Explore to which the NDT is joined. At runtime, any filters on the from_field in the Explore will be passed into the to_field in the NDT subquery. See this section below for an example.
With bind_filters, note the following:
The runtime filter must be in a view joined to the same Explore as the NDT.
The NDT cannot be made into a persistent derived table (PDT).
The explore_source parameter can have the bind_all_filters subparameter or the bind_filters subparameter, but not both.
bind_filters: {
  to_field: users.created_date
  from_field: user_dt.filter_date
}
bind_all_filters ADDED6.20 Passes all filters from the Explore query into the NDT subquery. To set this up, specify bind_all_filters: yes in the explore_source of the NDT. See this section below for an example.
With bind_all_filters: yes, note the following:
The runtime filter must be in a view joined to the same Explore as the NDT.
The NDT cannot be made in to a PDT.
The NDT can be joined only into the Explore specified in the NDT’s explore_source parameter. This is because bind_all_filters needs to map the Explore’s filtered fields to the fields in the NDT.
The explore_source parameter can have the bind_all_filters subparameter or the bind_filters subparameter, but not both.
bind_all_filters: yes
column Specifies a column to include in the explore_source. Has a field subparameter. column: cust_id {
  field: orders.customer_id
}
derived_column Specifies a column in the explore_source with an expression in the namespace of the inner columns. Aggregate SQL expressions will not work here, since there is no SQL grouping at this step. SQL window functions can be very useful in this parameter. Has a sql subparameter. derived_column: average_order {
  sql: order_amount / item_qty ;;
}
expression_custom_filter Optionally specifies a custom filter expression on an explore_source query. expression_custom_filter:
  ${orders.status} = "pending" ;;
filters Optionally adds a filter to an explore_source query. Filters are added to the WHERE clause of the SQL generated by the NDT. filters: {
  field: product.brand
  value: "Calvin Klein"
}
limit Optionally, specifies the row limit of the query. limit: 10
sort Optionally, specifies a sort for this explore_source. Has a desc subparameter to specify if the sort should be descending and a field subparameter to specify the field to sort. sort: {
  desc: no
  field: product.brand
}
timezone Sets the time zone for the explore_source query. For non-persistent derived tables, set the time zone to "query_timezone" to automatically use the time zone of the currently running query. If a time zone is not specified, the explore_source query will not perform any time zone conversion, and instead will use the database time zone. See the timezone values page for a list of the supported timezones. timezone: "America/Los_Angeles"

Passing Filters into an NDT

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

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

Using bind_all_filters

The easiest way to pass filters from an Explore to an NDT subquery is to specify bind_all_filters: yes in the NDT’s explore_source parameter. This will pass all of an Explore’s runtime filters into the NDT subquery.

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

Here is the LookML for an NDT 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 sort: { field: total_sale_price desc: yes} 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 NDT’s view above, the explore_source is order_items. Here is the LookML for the order_items Explore where the NDT 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 article.

Using bind_filters

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

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

The LookML below shows an NDT 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 NDT.

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

Common Challenges

Use Only One explore_source Parameter

Each NDT 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 an NDT, 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. In that case:

To add an Explore file:

  1. Navigate to your project files.
  2. Click the + at the top of the project file list in the Looker IDE and select Create View.
  3. Use the Explore’s name as the file name, and add the extension .explore.lkml to the end of the file name. For example, if your Explore’s name is order_items, name the new Explore file order_items.explore.lkml.

Looker displays the file in the Other section of the LookML IDE’s list of files.

Top