About
Fan Trap is a semantic data layer modelling issue. It is a join path problem between three tables when a “one-to-many” join links a table which is in turn linked by another “one-to-many” join. This type of schema is commonly use to define a Many-to-many relationship.
The fanning out effect of “one-to-many” joins can cause incorrect results (due to duplicate) to be returned when a query includes objects based on both tables.
The issue derives its name from the way the model looks when you draw it in an entity-relationship diagram: the linked tables 'fan out' from the master table.
This type of model looks similar to a star schema, a type of model used in data warehouses. When trying to calculate sums over aggregates using standard SQL over the master table, you get unexpected (and incorrect) results.
Articles Related
Example
On the Data Model
A simple example of a fan trap is shown below:
The many-to-many relationship For one sale_id, you have more than one model_id and for one model_id, you can have more than one saled_id. This rule is also true for the colour.
Within a report
| Report without sale model column |  |
| Report with sale model column |  |
Solution
The solution is to either adjust:
In a semantic layer (such as in OBIEE of BO), you will give a context in order to produce the adequate SQL.