User Guide Getting Started Help Center Documentation Community Training
New LookML
Old LookML
New LookML
Looker
  
English
Français
Deutsch
日本語
explore_source

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 sub-parameters defining the NDT.

Definition

There are two ways to make a derived table, which you can use as if it was 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 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: { 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 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_filters: { from_field: field_name to_field: field_name } 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 Specifies a filter on the field specified in its to_field subparameter. The value is from the runtime filter specified by the from_field subparameter. The runtime filter must be in a view joined to the same Explore. bind_filters: {
  to_field: users.created_date
  from_field: user_dt.filter_date
}
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 whether 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 time zone is not specified, by default the explore_source query will perform no time zone conversion using the database time zone. timezone: "America/Los_Angeles"

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 will override previous explore_source parameters.

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

Use Includes 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. In that case:

To add an Explore file:

  1. Navigate to your project files.
  2. Click the + 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