Cross-referenced dimensions lose track of their joined table relationships. In the following example, I want to present total_price as a measure on the orders view, but the cross reference to the dimension order_items.price triggers symmetric aggregation around the primary key of the orders view and causes an error. The cross reference to the pre-aggregated value does work but seems less convenient. Am I doing this right?
view: order_items {
dimension: order_id {
type: number
sql: ${TABLE}."ORDER_ID" ;;
}
dimension: order_item_id {
type: number
sql: ${TABLE}."ORDER_ITEM_ID" ;;
primary_key: yes
}
dimension: price {
type: number
sql: ${TABLE}."PRICE" ;;
value_format_name: usd
}
measure: total_price {
type: sum
sql: ${price} ;;
value_format_name: usd
}
}
view: orders {
dimension: order_id {
type: number
sql: ${TABLE}."ORDER_ID"
primary_key: yes
}
# does not work
measure: total_price_dimension_reference {
type: sum
sql: ${order_items.price}
value_format_name: usd
}
# does work
measure: total_price_aggregate_reference {
type: number
sql: ${order_items.total_price}
value_format_name: usd
}
}
explore: orders {
join: order_items {
sql_on: ${orders.order_id} = ${order_items.order_id} ;;
relationship: one_to_many
type: inner
}
}