Data Modeling Issue - Fan Trap
Table of Contents
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.
2 - Articles Related
3 - Example
3.1 - On the Data Model
A simple example of a fan trap is shown below:
3.2 - Within a report
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.