Best practices for excluding erroring fields from explores

[jyau]

I have run into the following situation a bunch of times, and it feels like a best practices recommendation of different approaches would be helpful.

When dealing with the problem described in the post below, and taking the second approach “Exclude the erroring field from explores”, what are the different methods of doing so?

Error: Unknown or inaccessible field Errors

What does this error mean? When you run the LookML validator, you might see an error like this: [image] 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: The field you are trying to refer to does not exist. The field is a dimension group without the timeframe appended. Th…

From what I gather, there are a couple of options for doing so:

1. Exclude the fields at the explore level
   a. Exclude individual fields - fields: [ALL_FIELDS*, -view.field1]
   b. Exclude sets - fields: [ALL_FIELDS*, -set1*]
2. Exclude the fields at the join level
   a. Include individual fields (excluding everything else) - fields: [field1]
   b. Include sets and exclude individual fields in those sets - fields: [set1*, -field1]

I know some of the syntax above is incorrect, because my LookML still isn’t validating.

• What is the correct syntax?
• In which cases does the view need to be scoped? Do sets need to be scoped to the view?
• Are there any other options I haven’t covered above?

My specific situation is that I have many fields in a single view that cause errors across many explores, so excluding sets at the explore level (1b, above) seems like the best solution, although I can’t get the syntax right.

[Brett_Sauve]

Hi @jyau, I think you have this just about right. Let me answer your questions:

What is the correct syntax?

If you’re doing this at the explore level (docs are here), it is:

- explore: view_name
  fields: [ALL_FIELDS*, -joined_view_name.unwanted_field, -joined_view_name.unwanted_set*]

If you’re doing this at the join level (docs are here), it is:

- explore: view_name
  joins:
    - join: joined_view_name
      sql_on: ${view_name.some_field} = ${joined_view_name.some_field}
      fields: [field_name, field_name, set_name*]

In which cases does the view need to be scoped? Do sets need to be scoped to the view?

This depends upon whether or not you’re doing this at the explore level or the join level.

At the explore level, if you’re trying to reference a field or set from the explore view (by which I mean - explore: this_one), you don’t need to scope. But if you’re trying to get at a field or set from a joined view, you do.

At the join level, you never need to scope. Just keep in mind you can only get at fields or sets from the specific joined view you’re working on. In other words:

- explore: order
  joins:
    - join: customer
      sql_on: ${order.customer_id} = ${customer.id}
      fields: [only_fields_from_customer_here, only_sets_from_customer_here*]
              # fields from product not allowed here

    - join: product
      sql_on: ${order.product_id} = ${product.id}

Are there any other options I haven’t covered above?

I don’t think so, this is the way to exclude fields if that is the approach you want to take. Per that other article that you linked to, you can also try to solve this by joining in additional views to different explores. If you are still getting errors after trying these approaches, feel free to contact chat and go through your specific LookML.

[lloydtabb]

An other option is to move the offending fields to bare joins.

suppose you have

- explore: orders
  joins:
  - join: users
    sql_on: ${orders.user_id} = ${users.id}
    relationship: many_to_one

- view: orders
  ...
  - measure: average_users_spend
    sql: ${total_spend} / ${users.count}

The problem with this is that every time you join orders, you are going to have to join users so the users.count will resolve.

An alternative is to move the joint fields into a bare join. A bare joins is a join with no sql logic. Think of it as just a list of field definitions…

- explore: orders
  joins:
  - join: users
    sql_on: ${orders.user_id} = ${users.id}
    relationship: many_to_one

  - join: orders_extra    # bare join, no sql_on

- view: orders_extra
  fields:
  - measure: orders.average_users_spend
    sql: ${orders.total_spend} / ${users.count}

When you include ‘orders.average_user_spend’ in your query, looker will figure out that you need to join users and do the right thing. You are free to use the orders view in other places without the ‘orders_extra’ fields.

[lindsey1]

You could also use the extends parameter to move the offending fields to extended views, and only join that extended view when desired. We have an overview of using extends here.