How to Use Measures With One-to-Many Joins
The text discusses the problem of implementing measures with one-to-many joins in dimensional modeling. It presents an example where two measures are used - sum(orders.total_cost) and sum(order_items.quantity). The author explains that a naive SQL query would produce incorrect results due to the one-to-many join from orders to order_items, resulting in duplicate aggregated values. To solve this problem, Looker uses symmetric aggregates, while an alternative approach involves using subqueries for multiple levels of aggregation. This method avoids the use of distinct aggregates and can generate efficient plans in modern data warehouses.
Company
Fivetran
Date published
May 19, 2021
Author(s)
George Fraser
Word count
533
Language
English
Hacker News points
None found.