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 |
Requires Free Membership to View
| John Doe | $10000.00 | $10000.00 | 300 |
| John Doe | $10000.00 | Toothpaste | 200 |
| Totals | $30000.00 | 550 |
For more information, check out SearchCRM.com's Best Web Links on Business Intelligence.
This was first published in September 2002
Join the conversationComment
Share
Comments
Results
Contribute to the conversation