Error: Unknown or inaccessible field

#1

What does this error mean?

When you run the LookML validator, you might see an error like this:

This error is referring to the field lifetime_orders in the users view. It is saying that users.lifetime_orders cannot access the user_order_facts.lifetime_orders field that it refers to.

Why is this error happening?

There are a few reasons this error could be occurring:

  1. The field you are trying to refer to does not exist.
  2. The field is a dimension group without the timeframe appended.
  3. There is a missing join causing the field to be inaccessible in some explores.

Field does not exist

If the field user_order_facts.lifetime_orders does not actually exist in the user_order_facts view, then you will see this error.

To solve this error: Add the field to the user_order_facts view.

Field is a dimension group

Dimension groups of type: time are usually based on dates or timestamps. Dimension groups must have the appropriate timeframe appended to the name when you refer to them. Read about timeframes here.

To solve this error: Ensure you are appropriately appending the timeframe.

Missing joins

This is the main reason for seeing this error. Here is the LookML for users.lifetime_orders:

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

(Note the use of substitution operators ${} to refer to user_order_facts.lifetime_orders.)

In this case, the error is happening 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:

Or, you can hover over the red X next to the LookML for this field:

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
  joins:
    - join: users                # users joined without user_order_facts
      relationship: many_to_one
      foreign_key: orders.user_id

- explore: order_items   
  joins:                                             
    - join: inventory_items  
      relationship: many_to_one                        
      foreign_key: order_items.inventory_item_id        

    - join: orders    
      relationship: many_to_one                                
      foreign_key: order_items.order_id               

    - join: users                # users joined without user_order_facts
      relationship: many_to_one
      foreign_key: orders.user_id

Note that in both of these explores, users is joined in without also joining in user_order_facts. This means that in these explores, if you tried to query the users.lifetime_orders field, it would cause an error because it cannot access user_order_facts.lifetime_orders. The validator is warning you ahead of time that this will happen.

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                          
  joins:
  - join: user_order_facts
    foreign_key: users.id

Here user_order_facts is joined in, so querying users.lifetime_orders will not error in this explore. However, it will error in the orders and order_items explores.

How can I fix this error?

This error can be fixed in a couple of ways:

  • Join the missing view in all cases.
  • Exclude the field causing the error from explores where 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 users.lifetime_orders will always be able to access user_order_facts.lifetime_orders.

We can add those joins like this:

- explore: orders
  joins:
    - join: users
      relationship: many_to_one
      foreign_key: orders.user_id

    - join: user_order_facts    # join user_order_facts through users
      relationship: many_to_one
      foreign_key: users.id 

- explore: order_items   
  joins:                                             
    - join: inventory_items  
      relationship: many_to_one                        
      foreign_key: order_items.inventory_item_id        

    - join: orders    
      relationship: many_to_one                                
      foreign_key: order_items.order_id               

    - join: users                
      relationship: many_to_one
      foreign_key: orders.user_id

    - join: user_order_facts    # join user_order_facts through users
      relationship: many_to_one
      foreign_key: users.id 

Now if you re-run the LookML validator, this error should go away.

Exclude the field causing the error from explores

You may not want 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 users 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
  joins:
    - join: users
      relationship: many_to_one
      foreign_key: orders.user_id

Learn more about fields here.

3 Likes

Best practices for excluding erroring fields from explores
Error: Unknown field substitution
Elegantly Handling 'Unknown or Inaccessible Field' Scenarios
Using Fields Parameters in LookML
(Carter Moar) #2

In short, Looker is only going to see the fields you tell it to see in the explore declaration. If, in a view, you ask for fields that the explore doesn’t know about, things get fouled up.

Great examples!
A+
:apple:

0 Likes