When you are working on your LookML files and are satisfied with your updates, the next step in deploying your LookML changes is to run the LookML Validator to perform a full model validation.

What Does This Error Mean?

Occasionally, you might see this error:

In this example, the error refers to the lifetime_orders field in the users view. The error indicates that users.lifetime_orders cannot access the user_order_facts.lifetime_orders field that it is referencing.

Why Is This Error Happening?

There are a few reasons this error could be occurring:

1. The field you are trying to reference does not exist.
2. The field is a dimension_group that is being referenced without a timeframe appended.
3. There is a missing join, making the field inaccessible by some Explores.

Option 1: The Field Does Not Exist

If the field user_order_facts.lifetime_orders is being referenced in LookML fields but does not exist as a field itself, you will receive this error.

We can resolve the error by adding the field that is triggering the error, user_order_facts.lifetime_orders, to the view prepended to the field in question. In this case, we can make sure the field is defined in the user_order_facts view, and add it if it does not exist.

Option 2: The Field Is a Dimension Group

Dimension groups of type: time represent a group of time-period dimensions that are defined in the timeframe parameter. When referencing dimension groups in LookML, we must append the appropriate timeframe dimension to the name of the dimension group.

To solve this error: Make sure you are appropriately appending the timeframe to reference a specific time dimension from the dimension group.

For example, consider this dimension_group:

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

If you want to reference one of these dimensions in another LookML field, you must reference a specific dimension within the group, such as ${created_date}, ${created_week}, or ${created_month}. If you try to use just the name of the dimension_group, ${created}, Looker will not know which timeframe you are referring to and will generate the above error.

Option 3: There Is a Missing Join

Here is the LookML for the field users.lifetime_orders:

dimension: lifetime_orders {
  type: number
  sql: ${user_order_facts.lifetime_orders};;
}

(Note the use of the substitution operators ${} to reference the LookML field user_order_facts.lifetime_orders.)

The lifetime_orders dimension in the users view references the lifetime_orders field from the user_order_facts view. In this case, the error is triggered because there are instances in the model file where the users view is joined to an Explore without having the user_order_facts view joined as well. To see which Explores are causing the problem, you can expand the two occurrences of the error:

These messages show that the order_items and orders Explores in the ecommerce model are causing the error. These Explores have their joins declared in the model file, like so:

explore: orders {
  join: users { # users joined without user_order_facts
    relationship: many_to_one
    sql_on: ${orders.user_id} = ${users.id}
  }
}

explore: order_items {
  join: inventory_items {
    relationship: many_to_one 
    sql_on: ${order_items.inventory_item_id} = ${inventory_items.id} 
  }
  join: orders {
    relationship: many_to_one 
    sql_on: ${order_items.order_id} = ${orders.id}
  }
  join: users { # users joined without user_order_facts
    relationship: many_to_one
    sql_on: ${orders.user_id} = ${users.id}
  }
}

Note that, in both of these Explores, users is joined in without also joining in the user_order_facts view.

This means that in these Explores, if you tried to query the users.lifetime_orders field, it would cause an error, because the Explores cannot access user_order_facts.lifetime_orders. The validator is warning you ahead of time that this will happen from the LookML file. This also means that users.lifetime_orders will not cause an error in another Explore where user_order_facts is joined in. For example, consider the users Explore:

explore: users { 
  join: user_order_facts {
    sql_on: ${users.id} = ${user_order_facts.users_id}
  }
}

Here user_order_facts is joined, so querying users.lifetime_orders will not lead to an error. However, it will cause an error in the orders and order_items Explores.

How Can I Fix This Error?

If it's caused by a missing join, we can fix this error in a couple of ways:

1. Join the missing view in all cases. In this case, we'll need to make sure the user_order_facts view is joined wherever the users view is used in an Explore.
2. Exclude the field causing the error from Explores that you do not want to join in the missing view.

Join the Missing View

In our example above, this error can be solved by joining user_order_facts to all the Explores where users is also joined. This will ensure that the users.lifetime_orders field will always be able to access user_order_facts.lifetime_orders to reference it.

Starting in Looker 7.20, you can use the metadata panel in the IDE to see all Explores that use the users view.

We can add those joins, like this:

explore: order_items { 
  join: inventory_items {
    relationship: many_to_one 
    sql_on: ${inventory_items.id} = ${order_items.inventory_item_id} 
  }
  join: orders { 
    relationship: many_to_one 
    sql_on: ${order_items.order_id} = ${orders.id} 
  }
  join: users { 
    relationship: many_to_one
    sql_on: ${orders.user_id} = ${users.id} 
  }
  join: user_order_facts { # join user_order_facts through users
    relationship: many_to_one
    sql_on: ${users.id} = ${user_order_facts.users_id} 
  }
}

Now, if you rerun the LookML validator, this error should go away.

Exclude the Field Causing the Error from Explores

You may not want to join user_order_facts to all Explores where users is joined. For example, maybe you don't want to see the fields from user_order_facts in the orders Explore, but you do want to see the usable fields from the users view without errors. You can do this by excluding the field causing the error, users.lifetime_orders, from the orders Explore, using the fields parameter.

The fields parameter for an Explore enables you to include or exclude specific fields from that Explore. In this case, we can exclude users.lifetime_orders from the orders Explore like this:

explore: orders {
  fields: [ALL_FIELDS*, -users.lifetime_orders] # exclude users.lifetime_orders
  join: users {
    relationship: many_to_one
    sql_on: ${orders.user_id} = ${users.id}
  }
}