Semantic Layer Modeling - Fan Trap Issue

Star Schema


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.


On the Data Model

A simple example of a fan trap is shown below:

Bobj Fan Trap

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 Bobj Fan Trap Example 1
Report with sale model column Bobj Fan Trap Example 2


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.

Documentation / Reference

Discover More
Bobj Fan Trap Good Result
BOBJ - Fan Trap (serial many to one joins)

The fan trap in a BOBJ context. A fan trap is a join path problem returning too many rows. The original query which returned the Cartesian product for Wendy Craig, must return the following table...
Data System Architecture
SQL - Joins (Home)

in SQL. A join is a SQL clause statement which define the set operation such as: intersection, union, that the database must perform between two or more relation (table, view, query, ...). The...
Star Schema
Semantic Layer - Join Path (or Query Path)

In a semantic layer, a join path is a series of joins that a semantic query can use to access data in the tables linked by the joins. Join Path problems can arise from the limited way that lookup...
Star Schema
Semantic Layer Modeling - Issues (SQL Trap)

When modeling a semantic layer, you can encounter issues that impact the results obtained from semantic queries. You may need to resolve join problems such as : loops, missing data traps, chasm...

Share this page:
Follow us:
Task Runner