Fan-out and how Holistics resolves it

Holistics resolves fan-out automatically. Normally, a one-to-many join can duplicate rows and inflate your aggregates. However, in Holistics, we detect this risk from the relationship cardinality you already declared and rewrite the query so every entity counts exactly once. There is nothing to configure: measures defined in AQL and aggregations in the exploration UI are all fan-out-safe by default.

What fan-out does to your numbers

Fan-out happens when a join duplicates rows on the "one" side of a one-to-many relationship. Aggregates then count those duplicates as if they were real data.

Take an ecommerce dataset where one users record relates to many orders records. Ask a natural question: how many users do we have, grouped by order status? A naive query joins the two models, then aggregates:

SELECT
  orders.status,
  COUNT(users.id) AS total_users
FROM users
LEFT JOIN orders ON users.id = orders.user_id
GROUP BY 1

How fan-out double-counts users

• [HTML node] — The "one" side: each user exists once
• [HTML node] — The "many" side: one user can have several orders
• LEFT JOIN users.id = orders.user_id — One-to-many: each user row repeats once per matching order
• [HTML node] — User 54 appears twice under cancelled, so that group counts 2 users instead of 1. The overall COUNT returns 4 instead of 2.

• users-table → join (one)
• orders-table → join (many)
• join → result-table (one row per order)

The join repeats each user once per order they placed. A user with five delivered orders adds five to the "delivered" count instead of one. On real data, the inflated numbers often look plausible, so the error is easy to miss.

For how Holistics generates joins from relationships in general, see how joins work.

How the industry handles fan-out: symmetric aggregates

Most join-based BI tools run the fanned-out join anyway, then repair the aggregate with arithmetic. The technique is known as symmetric aggregates. It hashes each row's primary key into an enormous number and adds the measure value on top. It then sums the distinct results and subtracts the hashes back out:

SUM(DISTINCT hash(pk) * 1000000 + value) - SUM(DISTINCT hash(pk) * 1000000)

This works because duplicated rows of the same entity produce the same hash + value, so DISTINCT collapses them into one. Two entities that share a value stay separate because their hashes differ. After subtracting the hash sum, what remains is the true total.

This approach has several drawbacks:

• Every fanned-out aggregate computes a hash (typically MD5) per row, then runs big-decimal SUM DISTINCT arithmetic on the results.
• The generated SQL is filled with nested hash, cast, and coalesce calls, which makes it hard to read or debug.
• The hash and the measure value are packed into one DECIMAL(38,0), so measure values beyond roughly 14 digits overflow and fail the query.
• The results are only correct if the declared primary key is actually unique. If it isn't, the query still runs and silently returns wrong numbers.

How Holistics resolves fan-out instead

Instead of repairing the fan-out with arithmetic, Holistics restructures the query so the fan-out never happens. It relies on two pieces of information that already exist in your dataset:

1. Relationship cardinality. Every relationship in your dataset declares which side is "one" and which is "many". That tells Holistics exactly when a join path could duplicate rows.
2. The measure's aggregation structure. AQL measures and aggregations in the exploration UI all declare what they aggregate. Holistics can read that structure directly.

Using these, Holistics restructures the query: it aggregates the measure at its own model's grain, reduces the "many" side to distinct (join key, dimension) pairs, and joins the two results back together. For the users-by-order-status question above, the generated query becomes:

WITH user_status_pairs AS (
  SELECT user_id, status
  FROM orders
  GROUP BY 1, 2
)
SELECT
  s.status,
  COUNT(users.id) AS total_users
FROM users
LEFT JOIN user_status_pairs s ON users.id = s.user_id
GROUP BY 1

Here is the same rewrite with the data from the example above:

How Holistics resolves the same query without fan-out

• [HTML node] — The "one" side: each user exists once
• [HTML node] — The "many" side: one user can have several orders
• [HTML node] — User 54's two cancelled orders collapse into one pair, so nothing can be double-counted
• LEFT JOIN users.id = pairs.user_id — Each user matches at most one row per status, so no duplication is possible
• [HTML node] — Every group now counts each user exactly once

• orders-table → pairs-table (deduplicate first)
• users-table → join (one)
• pairs-table → join (join back)
• join → result-table (count once per user)

Each user now counts once per status they actually have orders in, not once per order. The exact rewrite varies with the query shape, but the principle is the same: deduplicate before aggregating.

Since this approach doesn't rely on hash arithmetic, the drawbacks of symmetric aggregates don't apply:

• There is no per-row hashing, and no risk of decimal overflow.
• The generated SQL stays readable. If you want to verify a number, open the query tab and follow the rewrite step by step.
• There is nothing to configure per query. Holistics only needs the relationships you already declared.

Why do you still see the fan-out error?

If Holistics resolves fan-out automatically, why does this error still appear?

It appears because your query uses a SQL-defined measure. Holistics can't see what a raw SQL snippet aggregates, so it can't guarantee a correct result across the join. Instead of returning wrong data, Holistics stops and shows this error.

When you'll see this error

Only custom measures written in raw SQL trigger it. Measures defined in AQL and aggregations added in the exploration UI are resolved automatically.

The fix: define the measure in AQL

Rewriting the measure in AQL makes its aggregation structure explicit. Once Holistics can see the aggregation, it applies fan-out resolution as usual.

For a simple aggregation, declare the measure with an AQL definition:

Model users {
  // ...
  measure total_users {
    label: 'Total Users'
    type: 'number'
    definition: @aql count(users.id) ;;
  }
}

Composite measures work the same way, because AQL expressions are built from aggregations Holistics understands. For example, a delivery rate that divides one conditional count by another:

Model orders {
  // ...
  measure delivery_rate {
    label: 'Delivery Rate'
    type: 'number'
    definition: @aql safe_divide(
      count(case(when: orders.status == 'delivered', then: orders.id)),
      count(orders.id)
    ) ;;
  }
}

Both measures combine freely with fields from related models. Holistics applies fan-out resolution wherever the join path needs it.