/plushcap/analysis/fivetran/how-to-use-measures-with-one-to-many-joins

How to Use Measures With One-to-Many Joins

What's this blog post about?

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.


By Matt Makai. 2021-2024.