Views with more than 1 view mentioned in dimensions/measures

[_Noah]

Hi,

With the introduction of 23.6 we’ve encountered an issue with something @lloyd_tabb suggested in 2015- bare joins...

https://community.looker.com/lookml-5/best-practices-for-excluding-erroring-fields-from-explores-127...

The issue now is that we can’t define primary keys for views outside the view that is going to be joined (as necessary for bare join’s with measures). Problem illustrated below. Take Llyoyd’s example:

explore: orders {
    join: users
    relationship: one_to_many
    sql_on: ${orders.user_id} = ${users.id} ;;

    join: orders_extra {}
}

view: orders {
   dimension: id {
        primary_key: yes
        sql: ${TABLE}.id;;
        }

    dimension: spend {
        type: number
        sql: ${TABLE}.spend
    }

    dimension: user_id {
        type: number
        sql: ${TABLE}.user_id
    }

    measure: total_spend {
        type: sum
        sql: ${spend}
    }
}


view: orders_extra {
    dimension: id {   # This primary key is required b/c code validation will error saying measure below requires Primary Key
        primary_key: yes
        sql: ${orders.id} ;;   # Invalid syntax as of version 23.6
    }

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

I expanded a bit so we can see that with a bare join that references a view’s primary key we can still give Looker enough of a hint to help it understand the relationship of the view measures being referenced in the `average_users_spend` measure. Why are we taking this away with 23.6?

I can imagine most are going to say to create an NDT and substitute that but let’s not forget that there is a not so insignificant burden on LookML developers to even have to create extra views to avoid errors like ...

https://community.looker.com/lookml-5/elegantly-handling-unknown-or-inaccessible-field-scenarios-911...

So to add the additional burden of having to add the NDTs (modifying the logic as new dims/measures are added) as well AND define a new primary key there as well (which, let’s be honest, seems overkill when Looker should already have an awareness of the PK on a table it created from its explores. This is another missed opportunity but I’ll save that for another post.)

I’m hoping that Looker can continue to improve its table relational intelligence considering how much work LookML developers spend making sure explores are built correctly to help the tool understand how things relate. I’m really hoping the reason for dropping support for the specific primary key functionality (or maybe there wasn’t any there all along?) is more than, “we didn’t want to have to deal with the additional complexity”. Can someone from Looker please help with providing a work-around to the above (other than new derived tables, i.e. native and non-native)?

[kyoji_k]

I am also facing issues due to the release of "References to fields in another view in the SQL for a field marked with primary_key: yes will now return an error in the New LookML Runtime."

Although my case is slightly different, I had implemented a view following the best practices mentioned in the following link, but now this method cannot be used as it also creates a PK by referencing another view.

https://cloud.google.com/looker/docs/best-practices/how-to-model-nested-bigquery-data-in-looker?hl=j...

In my environment, the 23.6 release has not yet been made, but I feel anxious about tomorrow's release if an error occurs.

I have contacted Looker support, but have not found a good solution.

Since it is said that there is no problem with the actual operation even if only a validation error occurs, I am making the following feature request as an option for validation: "Allow PK to reference another view."

I cannot understand why something that was presented as a best practice is now being treated as an error for unclear reasons.

Additionally, I would like to avoid making LookML more complex just to avoid this error.

Although this is not a helpful comment, I posted this to share my situation which is similar to yours.

I hope this request will reach the Looker product team.

[masutaka-pay2]

@_Noah Do you really get the error? Your code seems to be missing some “;;”.

This LookML passed validation on Looker 23.6.51:

explore: orders {
  join: users {
    relationship: one_to_many
    sql_on: ${orders.user_id} = ${users.id} ;;
  }

  join: orders_extra {}
}

view: orders {
  dimension: id {
    primary_key: yes
    sql: ${TABLE}.id;;
  }

  dimension: spend {
    type: number
    sql: ${TABLE}.spend ;;
  }

  dimension: user_id {
    type: number
    sql: ${TABLE}.user_id ;;
  }

  measure: total_spend {
    type: sum
    sql: ${spend} ;;
  }
}

view: users {
  dimension: id {
    primary_key: yes
    sql: ${TABLE}.id ;;
    hidden: yes
  }

  measure: count {
    type: count
  }
}

view: orders_extra {
  dimension: id {
    primary_key: yes
    sql: ${orders.id} ;;   # No problem
  }

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

[_Noah]

@masutaka-pay2 - I am going based on the breaking change noted for the 23.6 release that says “…  references to fields in another view in the SQL for a field marked with primary_key: yes will now return an error ...”. Note that as per the docs it says it is for the “New LookML Runtime”. So I’d be curious if you’re not getting it with the New LookML Runtime activated.

And yes, there were some typos in the code (omitting correct syntax) but hopefully my example still provides some idea of where I was going with the issue as I can no longer edit the original post.

[masutaka-pay2]

@_Noah Of cource, I am aware of the change in Looker 23.6 release. Just to be sure, I checked the Looker admin setting and the new LookML Runtime is enabled. "Use Legacy LookML Runtime" on /admin/legacy_features is disabled.

[_Noah]

Thanks for bringing up your findings, @masutaka-pay2 . I also noticed I was not able to replicate the New LookML runtime error for primary keys in our instance.

I spoke to Looker support to get clarity on what the the breaking change was referring to and after a little bit of them researching it turns out the breaking change was quickly reverted. You’ll find this in version 23.6.53+ (at least for the time being).

I didn’t get a reason why it was reverted. I’m also not sure if they plan to include the validation check that was reverted in a future release so I have followed up with them to get clarity on that. Will do my best to update here when I have information.

[_Noah]

I asked if there were current plans to revert the change and restore the primary key breaking change in an upcoming release.

TL;DR: There are no current plans but that could change.

[masutaka-pay2]

Thanks for your information. Our Looker instance version is 23.6.53.