TL;DR: How to combine queries across tables on a foreign key
Sometimes the join between two tables is easy, like joining orders
to products
on product_id
.
But sometimes it’s more complex. For example, let’s say you want to make a daily rollup table with the count of orders and distinct products per day. You would need to “merge” two aggregate queries — one for order
per day, and one for products
per day — by joining them on a date field, which would let you plot both metrics in a single chart.
“Merging queries” like this is a common analytical pattern. There are a few ways to do it in Omni:
- Xlookup (easier)
- Saved views (if you prefer to formally model)
1. Xlookup
Xlookup in Omni makes this pattern very easy to do. Simply create your two queries you’d like to merge, and then use Xlookup to bring data from one query to the other.
Here’s how this looks in practice:
**Note:**This works very well when you know the join is one-to-one; for example, a daily rollup table → another daily rollup table = one date per table. But if your join is one-to-many, Xlookup will only pull the first result it encounters, so the results may not be what you expect. In that case, use the Saved Views method below.
2. Saved Views
To “merge” using Omni’s modeling structure, we can create separate Saved Views and join them on a common key. For example:
- Aggregate an
orders
table to orders per day and aproducts
table to products per day - Join the tables together in the UI or in the
relationships
file - Query from the tables
Here’s how this looks in practice: