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 SearchCRM.com's Best Web Links on Business Intelligence.


This was first published in September 2002

Dig deeper on Call center VoIP software

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataManagement

SearchSAP

SearchOracle

SearchAWS

SearchContentManagement

Close