Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Question

Inaccessible view error when joining a view with a "from" parameter

  • 16 November 2022
  • 5 replies
  • 241 views
jpuris-1630428268
Userlevel 1

The problem is that I have a measure that is defined in a refined view that then is joined into an explore. This refined view borrows a field from another view, which is fine, however when I join the final view in with different name than the original (this is where “from” parameter comes in), it the validator will throw an error..

The error

Inaccessible view "pricespec__base" referenced in "first_pricespec__price.num__ppw__incl_vat__loc". "pricespec__base" is not accessible in explore "project". Check for missing joins in explore "project".

LookML for refined views

view: +pricespec__base {}

view: +pricespec__price {

  # PPW incl. VAT
  measure: num__ppw__incl_vat__loc {
    group_label: "Price Summary"
    group_item_label: "AVG PPW (LOC) incl. VAT"
    label: "Average Price Per Watt (LOC) incl. VAT"
    type: number
    sql: ${sum__gross_price__incl_vat__loc} / nullif(${pricespec__base.sum__nominal_panel_power}, 0 ;;
  }

}

LookML for the explore (this works)

  join: pricespec__base {
    view_label: "Project's Contracts"
    type: left_outer
    sql_on: ${contract.dim__pricespec_id} = ${pricespec__base.dim__id} ;;
    relationship: one_to_one
  }

  join: pricespec__price {
    view_label: "Project's Contracts"
    type: left_outer
    sql_on: ${pricespec__base.dim__id} = ${pricespec__price.dim__pricespec_id} ;;
    relationship: one_to_many
  }

LookML for the explore (this throws an error)

  join: first_pricespec__base {
    from: pricespec__base
    view_label: "Project's Contracts"
    type: left_outer
    sql_on: ${contract.dim__pricespec_id} = ${first_pricespec__base.dim__id} ;;
    relationship: one_to_one
  }

  join: first_pricespec__price {
    from: pricespec__price
    view_label: "Project's Contracts"
    type: left_outer
    sql_on: ${first_pricespec__base.dim__id} = ${first_pricespec__price.dim__pricespec_id} ;;
    relationship: one_to_many
  }

 

The problem I’m solving with usage of “from” parameter in the joins is that I need to join in the same refined view multiple times with different “sql_on” configuration. It seems LookML is trying to find a view by “first_pricespec__base” and not what is specified in the `from` parameter “pricespec__base”

 

How would someone join a refined view that uses a “calculated” field from multiple views and then needs to join it in same explore multiple times?

 

Surely there must be a way! :(

This topic has been closed for comments

5 replies

jpuris-1630428268
Userlevel 1

Below is a minimally reproducible example of the problem. The model will work on any ANSI SQL compliant database connection.

 

my_model.model

connection: "!!!CHANGE-ME!!!"

label: "Sandbox"

explore: project {
  view_label: "Project"

  join: contract {
    view_label: "Project's Contract"
    sql_on: ${project.id} = ${contract.project_id} ;;
    relationship: one_to_many
  }

  join: spec {
    view_label: "Project's Contract Spec"
    sql_on: ${contract.spec_id} = ${spec.id} ;;
    relationship: one_to_one
  }

  join: spec__price {
    view_label: "Project's Contract Spec Price"
    sql_on: ${spec.id} = ${spec__price.spec_id} ;;
    relationship: one_to_one
  }

}

view: project {
  label: "Project"

  derived_table: {
    sql: SELECT 1 AS id
      UNION ALL SELECT 2
      UNION ALL SELECT 3;;
  }

  dimension: id {
    primary_key: yes
    label: "Project ID"
    type: number
    sql: ${TABLE}."id" ;;
  }
}

view: contract {
  label: "Contract"

  derived_table: {
    sql: SELECT 11 AS id, 21 AS spec_id, 1 AS project_id
      UNION ALL SELECT 12, 22, 2
      UNION ALL SELECT 13, 23, 3
      UNION ALL SELECT 14, 24, 2
      UNION ALL SELECT 15, 25, 2
      UNION ALL SELECT 16, 26, 3
       ;;
  }

  dimension: id {
    primary_key: yes
    label: "Contract ID"
    type: number
    sql: ${TABLE}."id" ;;
  }

  dimension: spec_id {
    hidden: yes # foreign key
    label: "Spec ID"
    type: number
    sql: ${TABLE}."spec_id" ;;
  }

  dimension: project_id {
    hidden: yes # foreign key
    label: "Project ID"
    type: number
    sql: ${TABLE}."project_id" ;;
  }
}

view: spec {
  label: "Spec"

  derived_table: {
    sql: SELECT 21 AS id, 101 AS quantity
      UNION ALL SELECT 22, 102
      UNION ALL SELECT 23, 103
      UNION ALL SELECT 24, 104
      UNION ALL SELECT 25, 105
      UNION ALL SELECT 26, 106;;
  }

  dimension: id {
    primary_key: yes
    label: "Spec ID"
    type: number
    sql: ${TABLE}."id" ;;
  }

  dimension: quantity {
    label: "Quantity"
    type: number
    sql: ${TABLE}."quantity" ;;
  }
}

view: spec__price {
  label: "Spec Price"

  derived_table: {
    sql: SELECT 31 AS id, 21 AS spec_id, 202 AS price
      UNION ALL SELECT 32, 22, 202
      UNION ALL SELECT 33, 23, 203
      UNION ALL SELECT 34, 24, 204
      UNION ALL SELECT 35, 25, 205
      UNION ALL SELECT 36, 26, 206
       ;;
  }

  dimension: id {
    primary_key: yes
    label: "Price ID"
    type: number
    sql: ${TABLE}."id" ;;
  }

  dimension: spec_id {
    hidden: yes # foreign key
    label: "Spec ID"
    type: number
    sql: ${TABLE}."spec_id" ;;
  }

  dimension: price {
    label: "Price"
    type: number
    sql: ${TABLE}."price" ;;
  }
}

view: +spec__price {

  dimension: total_price {
    label: "Total Price"
    description: "Quantity multiplied with single unit price"
    type: number
    sql: ${price} * ${spec.quantity} ;;
  }
}

It works fine, however, if we change

  join: spec {
    view_label: "Project's Contract Spec"
    sql_on: ${contract.spec_id} = ${spec.id} ;;
    relationship: one_to_one
  }

to

  join: contract_spec {
    from: spec
    view_label: "Project's Contract Spec"
    sql_on: ${contract.spec_id} = ${contract_spec.id} ;;
    relationship: one_to_one
  }

one will see both a validation error and a explore error stating

Inaccessible view "spec" referenced in "spec__price.total_price". "spec" is not accessible in explore "project". Check for missing joins in explore "project". (In field "spec__price.total_price")


How does one work around this? We absolutely need to join the refined view multiple times to same explore, hence need to give it an unique alias, but Looker seems to not allow that.

K
Userlevel 2

The price view will need to be: 

sql: ${price} * ${contract_spec.quantity} ;;

If got a second contract view:

join: contract_spec_1 { from: spec….

Then will need a second price view:

sql: ${price} * ${contract_spec_1.quantity} ;;

 

Though may have misunderstood the issue.

 

jpuris-1630428268
Userlevel 1

@kuopazare you suggesting to have a view for every custom named join that has a composite field in it? That would be 20+ extra views for us.

 

LookML is praising the DRY, which is exactly opposite of this and will create a maintenance nightmare :(

 

edit: I’m now trying to get something similar together with extends as I’m running out of options and time..

K
Userlevel 2

Yes would be a maintenance nightmare if 20 odd. All I can say is I use from: if have more than one column in a table that are FKs to the same referenced table. So something like columns PrimaryContract, SecondaryContract. I think your use case must be different to this.

jpuris-1630428268
Userlevel 1

Indeed. My use case is that the PK in the problematic view can be referenced by multiple views (a fk) in the explore.

 

I will try to put this together with extends / refinements the best I can and update the thread on how it was solved (worked around).

Terms of Service