Solved

Dimension using other view that is joined many times

  • 2 April 2021
  • 1 reply
  • 45 views

I have got a database with a very strange order architecture in which any order might have a parent. Sometimes, we need information from the parent or even the grandparent order. Because of this, I have created the following…

view: orders {
sql_table_name: ... ;;
dimension: key { ... }
dimension: product_key { ... }
dimension: parent_key { ... }
dimension: grandparent_key { ... }
}

view: products {
sql_table_name: ... ;;
dimension: key { ... }
dimension_group: start {
type: time
timeframes: [...]
sql: ${TABLE}.start ;;
}
... other stuff like product price and name ...
}

explore: order {
from: orders

join: parent_order {
from: orders
sql_on: ${order.parent_key} = ${parent_order.key} ;;
}

join: grandparent_order {
from: orders
sql_on: ${order.parent_key} = ${grandparent_order.key} ;;
}

join: order_product {
from: products
sql_on: ${order.product_key} = ${order_product.key} ;;
}

join: parent_order_product {
from: products
sql_on: ${parent_order.product_key} = ${parent_order_product.key} ;;
}

join: grandparent_order_product{
from: products
sql_on: ${grandparent_order.product_key} = ${grandparent_order_product.key} ;;
}
}

This allows me to Explore as I’d expect where I can filter via the Parent Order’s Product or the Grandparent Order’s Product, and all the Joins work out perfectly.

 

But now I would like to add a new dimension “days after product launch” to my “orders” view.  LookML doesn’t seem to want to allow my dimension to exist, no matter how I try to refer to the product’s start date…

This is what I had attempted, first:

  dimension: days_since_product_launch {
type: number
required_fields: [order_product.start_raw]
sql: DATE_DIFF(${created_raw}, ${order_product.start_raw}, DAY) ;;
}

But that causes an error:

Unknown view "order_product" referenced in "order.days_since_product_launch". View "order_product" does not exist in model "ZZZ". Check for typos or missing include statements in "ZZZ".

 

I figure that I need to specify using the name “order_product” because otherwise how will it know which of the joins it should be using? But for some reason, this is invalid.

Can anyone help? Thank you in advance!

icon

Best answer by humbletyler 14 April 2021, 18:12

Basically through any Explore<->View Join that is possible, you need to also have the same name defined.

For example, in my “Orders” explore I have “parent_order” and “grandparent_order,” so in order for my example to validate and work, I had to add “parent_order” and “grandparent_order” to every other view to which my “orders” explore was joined.

Then it all validated :)

View original

This topic has been closed for comments

1 reply

Basically through any Explore<->View Join that is possible, you need to also have the same name defined.

For example, in my “Orders” explore I have “parent_order” and “grandparent_order,” so in order for my example to validate and work, I had to add “parent_order” and “grandparent_order” to every other view to which my “orders” explore was joined.

Then it all validated :)