BOBJ - Fan Trap (serial many to one joins)

Card Puncher Data Processing


The fan trap in a BOBJ context.

A fan trap is a join path problem returning too many rows.

Solved by

The original query which returned the Cartesian product for Wendy Craig, must return the following table when run with the above solution:

Bobj Fan Trap Good Result

Using an alias and context

The fan trap using dimension objects in the query is solved by using an BOBJ - Alias and BOBJ - Context.

Bobj Fan Trap First Context

Bobj Fan Trap Second Context

And multiple SQL Statements for Each Context checked (File > Parameters > SQl Tab)

Bobj Univers Parameters Multiple Path

Using Multiple SQL Statements for each measure

If you have only measure objects defined for both tables at the many end of the serial one-to-many joins, then you can use the Universe Parameters option Multiple SQL Statements for Each Measure.

Multiple SQL Statements for Each Measure checked (File > Parameters > SQl Tab)

Bobj Univers Parameters Multiple Path

This forces the generation of separate SQL queries for each measure that appears in the Query pane.

You cannot use this method to generate multiple queries for dimensions. If an end user can include dimensions from any of the tables that reference the measure objects in the query, then you must use an alias and context to resolve the fan trap.

Detected by

  • Visual analysis of table schema.

You cannot automatically detect fan traps. You need to visually examine the direction of the cardinalities displayed in the table schema.

Discover More
Card Puncher Data Processing
BOBJ - Alias

Aliases are references to existing tables in a schema. An Alias is a table that is an exact duplicate of the original table (base table), with a different name. You use aliases for two main reasons:...
Bobj Context Structure Pane
BOBJ - Context

Contexts are a collection of joins which provide a valid query path for to generate SQL. You use contexts to resolve join problems that can return too many or too few rows because of the way that...
Card Puncher Data Processing
BOBJ - Joins (Home)

The Joins in BOBJ. You have several approaches to creating joins in : Tracing joins manually in the schema (Drag and drop) Defining join properties directly ( Menu Select Insert > Join ) ....

Share this page:
Follow us:
Task Runner