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"
  }
}

Hierarchy

View File

view

derived_table

explore_source

Default Value

None

Accepts

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

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 which are optional. The syntax for defining a native derived table is as follows:

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"
}

The following table provides information about each parameter that you can use to define a native derived table:

Parameter Name	Description	Example
`bind_filters`	Passes a filter from the Explore query into the native derived table subquery. To set up this parameter, use the `from_field` subparameter to specify a field defined in the native derived table view or accessible in the Explore to which the native derived table is joined. At runtime, any filters on the `from_field` in the Explore will be passed into the `to_field` in the native derived table subquery. See the Using `bind_filters` section for an example. With `bind_filters`, note the following: The runtime filter must be in a view joined to the same Explore as the native derived table. The native derived table 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`	Passes all filters from the Explore query into the native derived table subquery. To set up this parameter, specify `bind_all_filters: yes` in the `explore_source` of the native derived table. See the Using `bind_filters` section 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 native derived table. The native derived table cannot be made in to a PDT. The native derived table can be joined only into the Explore specified in the native derived table's `explore_source` parameter. This is because `bind_all_filters` needs to map the Explore's filtered fields to the fields in the native derived table. 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 won't 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 ;; }
`dev_filters`	Added 21.12 Specifies filters that Looker applies only to development versions of the derived table. This is useful for LookML developers when they test derived tables in Development Mode. The `dev_filters` parameter lets Looker build smaller, filtered versions of the table so that a LookML developer can iterate and test the table without waiting for the full table to build after each change. Looker applies the `dev_filters` only to the development versions of the derived table, not to the production version of the table that is queried by your users. See the Derived tables in Looker documentation page for more information on working with derived tables in Development Mode and the Creating filters for Development Mode section on this page for an example.	`dev_filters: [orders.created_date: "90 days", orders.products: "sweaters"]`
`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. Enclose in square brackets; include the field name to filter, using `view_name.field_name` format, followed by `:` and the value(s) on which the field should be filtered. Filters are added to the `WHERE` clause of the SQL generated by the native derived table.	`filters: [products.department: "sweaters"]`
`limit`	Optionally, specifies the row limit of the query.	`limit: 10`
`sorts`	Optionally, specifies a sort for this `explore_source`. Enclose in square brackets; include the field name to sort, using `view_name.field_name` format, followed by `:` and `asc` or `desc` to indicate whether the field should be sorted in ascending or descending. You can sort on multiple fields by adding multiple field name and keyword pairs separated by commas.	`sorts: [products.brand: asc, products.name: asc]`
`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 won't perform any time zone conversion but will use the database time zone instead. See the `timezone` values page for a list of the supported time zones. The IDE autosuggests the time zone value when you type the `timezone` parameter in the IDE. The IDE also displays the list of supported time zone values in the Quick Help panel.	`timezone: "America/Los_Angeles"`

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