Manage Learn to apply best practices and optimize your operations.

Fan traps in business intelligence, Part 1

A "fan trap" is an age-old SQL problem with a relatively new moniker.

What is a "fan trap"?

A "fan trap" is an age-old SQL problem with a relatively new moniker. A fan trap refers to the linked one-to-many related tables involved in a SQL query join. The inclusion of such fanned-out one-to-many joins can cause incorrect results to be returned especially if the query includes attributes from multiple tables in the joins. The incorrect results are referred to as Cartesian products.

A typical business intelligence query is focused on performing aggregation of "fact(s)" or "measures" in a fact table. If the query involves a "fan trap", the returned Cartesian product result-set will cause the output "fact(s)" to be incorrect, i.e. overstated, many times over.

A "fan trap" example: customer, order, order detail:

Entity name Relationship Entity name
Customer One to many Order
Order One to many Order detail

For the example above if a query is summing total order value from order entity and total order lines from order detail for a "John Doe" customer, then the resulting query will produce incorrect "order value" totals as below.

Customer Order value Product name Order lines
John Doe $10000.00 Shampoo 50
John Doe $10000.00 $10000.00 300
John Doe $10000.00 Toothpaste 200
Totals $30000.00   550

For more information, check out's Best Web Links on Business Intelligence.

Dig Deeper on Contact center management

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.