Question

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

  • 16 November 2022
  • 5 replies
  • 69 views

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! :(


5 replies

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.

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.

 

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..

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.

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).

Reply