Merging Queries

Patterns
1

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 total_orders and total_products into one table
Merging total_orders and total_products into one table2078×408 17.7 KB

“Merging queries” like this is a common analytical pattern. There are a few ways to do it in Omni:

  1. Xlookup (easier)
  2. 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:

  1. Aggregate an orders table to orders per day and a products table to products per day
  2. Join the tables together in the UI or in the relationships file
  3. Query from the tables

Here’s how this looks in practice: