Templated Filters and Derived Tables - Variable not found "parameter"

  • 17 May 2022
  • 0 replies
  • 1066 views

Userlevel 5

We will not be updating Knowledge Drop content, nor do we guarantee that everything is up-to-date. The timestamp published on each Knowledge Drop denotes when it was last verified.

Author: @Zam 

 

Derived tables are a common use case for applying templated filters. This is because templated filters allow us to restrict tables to certain criteria prior to building them.

This approach is particularly useful in columnar databases, as they have a single sort order. Thus, it can be more efficient to isolate a subset of data from a large table as a joined subquery, and transform the subset on the fly. With this method, we can avoid joining in the entire large table.

A great example use case of templated filters in derived tables is performing a dynamic cohort analysis, as detailed in the Help Center article [Analytic Block] Creating Custom Cohort Analysis.
 

Referencing Dynamic Derived Table Views Correctly


In Looker, Explores and joins must reference the view name that is defined in the underlying view file's view parameter.

This is especially important when creating Explores or joins with derived tables that leverage templated filters.

If the correct view name is not referenced, an error message will appear, claiming that the templated filter variable {% condition filter_name %} does not exist (even when it is defined properly in LookML):
 

The most common cause of this error is improper aliasing of Explores with the from parameter. This is because from changes not only the Explore name, but the fields in the underlying view as well. Fields are recognized as belonging to the aliased Explore, rather than the underlying view.
 

Why Exactly Does this Error Occur?


Although a derived table takes the name in the view parameter, this name does not affect how the SQL definition of the table is processed.

When Looker builds a derived table, it first parses the templated filters into SQL, and then builds the table using appropriate filter values. This occurs prior to checking the name of the view.

Because of this, it's possible for the view name at the time it's checked not to match the view name at the time the SQL was parsed (for example, when the Explore/join is aliased). In such a case, Looker may believe that templated filters exist under one view name when they actually exist under another — in this example, a view name that doesn't exist in this Explore.

Let's use the following as an example:

Note: Best practice is to separate the view into a separate view file, and place the explore into a model file.
###### EXPLORE ######

explore: test {
from: derived_table_test
}

###### VIEW ######

view: derived_table_test {
derived_table: {
sql:
SELECT id_
, status
, created_at
FROM orders
WHERE {% condition status %} orders.status {% endcondition %}
AND {% condition created_date %} orders.created_at {% endcondition %} ;;
}

filter: status {
type: string
suggestions: ["Complete", "Pending", "Cancelled"]
}

dimension_group: created {
type: time
timeframes: [date, week, month, year]
sql: ${TABLE}.created_at ;;
}
}

When Looker parses the SQL for the derived table, it assumes the fields derived_table_test.status and derived_table_test.created_at are front-end filters in the UI. Looker assumes this because of the {% condition filter_name %} variables referencing these fields in the WHERE clause.

However, since the from parameter is used to alias the Explore as test, all fields in derived_table_test will be referenced from test. For example, test.status and test.created_date.

Thus, the conditions {% condition status %} and {% condition created_date %}, which Looker believes are a part of the derived_table_test view, become floating conditions; they have no home.
 

Solution 1: extends


There are two scenarios where you may want to use from in an Explore or join:

  1. To change the name of the underlying view when it is used as an Explore.
  2. To alias the name of the view when it is used in a join (most common use case is joining one table more than once in an Explore).

For changing the name of the underlying view used in an Explore, the recommended method for aliasing is to use a combination of view_name and label. This will allow us to completely avoid the variable not found error:

explore: some_name {
view_name: actual_view_name
label: "Pretty Name"
}

If we absolutely must use from to alias an Explore or a view at the join level (for example, when joining the same table more than once in an Explore), we can resolve the variable not found error with an extends parameter. In this case, extends can be leveraged to copy the underlying view definitions and be renamed the aliased join name.

For example, let's say we have named our dynamic derived table view derived_table_test and want to join it more than once in an Explore. We can create a new view file (called derived below) and create a copy of derived_table_test with the new view name using extends for views:

view: derived {
extends: [derived_table_test]
}

Next, join the extended view derived:

join: derived_table_test {
sql_on: ${derived_table_test.id} = ${test.id} ;;
}

join: derived {
sql_on: ${derived.id_2} = ${test.id} ;;
}

Here is everything together:

Note: Best practice is to separate the view and extended view into separate view files, and place the Explore into a model file.
###### EXPLORE ######
explore: test {

join: derived_table_test {
sql_on: ${derived_table_test.id} = ${test.id} ;;
}

join: derived {
sql_on: ${derived.id_2} = ${test.id} ;;
}
}

###### EXTENDED VIEW ######

view: derived {
extends: [derived_table_test]
}

###### VIEW ######

view: derived_table_test {
derived_table: {
sql:
SELECT id
, status
, created_at
FROM orders
WHERE {% condition status %} orders.status {% endcondition %}
AND {% condition created_date %} orders.created_at {% endcondition %} ;;
}

filter: status {
type: string
suggestions: ["Complete", "Pending", "Cancelled"]
}

dimension_group: created {
type: time
timeframes: [date, week, month, year]
sql: ${TABLE}.created_at ;;
}
}
}

Solution 2: Scope Templated Filters with Aliased Explore Name


Although using extends is the recommended resolution, another solution is to scope the templated filters referenced within the {% condition filter_name %} variables with the aliased Explore name. For example:

###### EXPLORE ######

explore: derived {
from: derived_table_test
}

###### VIEW ######

view: derived_table_test {
derived_table: {
sql:
SELECT id
, status
, created_at
FROM orders
WHERE {% condition derived.status %} orders.status {% endcondition %}
AND {% condition derived.created_date %} created_at {% endcondition %} ;;
}

filter: status {
type: string
suggestions: ["Complete", "Pending", "Cancelled"]
}

dimension_group: created {
type: time
timeframes: [date, week, month, year]
sql: ${TABLE}.created_at ;;
}
}

Notice the explore name, derived, in front of the status and created_date conditions. This is not recommended because any Explore or join that uses derived_table_test not aliased as derived will break.

If the unaliased view name derived_table_test will never be used in favor of the alias derived, the true fix is to rename the view derived, since that is the only name it will be referenced across the model.


0 replies

Be the first to reply!

Reply