Dimension using other view that is joined many times

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!

Solved Solved
0 1 358
1 ACCEPTED SOLUTION

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 solution in original post

1 REPLY 1

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 🙂

Top Labels in this Space
Top Solution Authors