Metric View measure on joined table

[alxsbn]

On the Databricks documentation they take the TPCH classical tables to create some example https://docs.databricks.com/aws/en/metric-views/create/

It's pretty easy to create a metric view using orders and do a measure like SUM(totalprice). Now let's say I want to join it with lineitem do a measure LIKE SUM(lineitem.quantity) it's not working on my side.

Am I missing something ?

[NandiniN · Accepted solution]

Hi @alxsbn , 

The Metric View joins are designed for "Many-to-One" relationships.

Because orders and lineitem have a One-to-Many relationship (one order has multiple line items), you cannot join lineitem onto an orders-based Metric View and aggregate the line items correctly.

Thanks!