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|
For more information, check out SearchCRM.com's Best Web Links on Business Intelligence.
This was first published in September 2002