Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Question

Best practices for excluding erroring fields from explores

  • 21 August 2015
  • 3 replies
  • 1691 views
J

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?



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.

3 replies

Brett_Sauve
Rank
Userlevel 3

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
Rank
Userlevel 6
Badge

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.

L
Rank
Userlevel 5
Badge

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.

Reply


Terms of Service