Skip to main content

Resolve Join fans out data without primary key errors

If you receive the Join fans out data without primary key error while you’re building a workbook query, you may be trying to calculate an aggregation (sum, count, average, etc.) on a table that’s being “fanned out” by a join.

Why this happens

This happens when you’re joining two tables and one row in the first table is joined onto multiple rows in the other table. For example, you have an orders table and an order_items table. Joining these tables on order_id will fan out the orders table since multiple items in order_items will match the same order in orders: In this example, a single row in orders with total: $50 is duplicated three times in the joined result. If you summed total without accounting for the fan out, you’d incorrectly get $150 instead of $50. Omni uses a method called symmetric aggregates to prevent inadvertently miscalculating aggregations - such as sums, counts, and averages - when a table is fanned out. Before Omni calculates an aggregation on a fanned out table, it’ll look at that table’s primary key — its unique ID per row — to ensure values aren’t double-counted in the aggregation. Omni surfaces this error when a fanned out table doesn’t have a defined primary key. Without a primary key, Omni won’t know how to correctly calculate the aggregation.

How to fix it

Defining a primary key for a view will resolve this error. A primary key is a column that contains a unique identifier per row. This is usually an ID column. Primary keys can be defined in a workbook or the model IDE.
Remember to promote your changes to the shared model if you want to make them generally available.

Workbook

  1. In the workbook field browser, right click on the field you want to define as the primary key.
  2. Click Modeling > Primary Key.

Model IDE

  1. Open the model IDE.
  2. Locate the view and click to open its YAML definition.
  3. Add the primary_key parameter to the appropriate dimension: 
    id:
  primary_key: true
If your table has a compound (composite) primary key, add the custom_compound_primary_key_sql parameter to the view file: 
custom_compound_primary_key_sql: [ id, date ]