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.
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.
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.