Data Modeling Issue - Fan Trap

1 - About

Fan Trap is a data 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.

3 - Example

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

3.2 - Within a report

Report without sale model column
Report with sale model column

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

5 - Documentation / Reference

data/modeling/fan_trap.txt · Last modified: 2017/09/14 13:31 by gerardnico