Chasm and fan traps should be avoided when building your ElastiCube schemas.

Chasm Traps

A chasm trap occurs when two “Many-to-One” joins converge on a single table, and the query includes measures from both leaf tables. As a result multiple rows are returned from the tables when processing the query.

chasm1

If you were to calculate both measures (Qty and Value) simultaneously, like in the following example, the values for Customers will be multiplied due to the inner join between the leaf tables, and the results may be incorrect:

chasm2

Fan Traps

A fan trap occurs when two “many-to-one” joins follow one another in master-detail form (OrderDetails), and the query includes a measure from both the leaf table (OrderDetails) and its immediate master (Orders).

chasm3

If you try to aggregate both measures simultaneously (using the query below), you will probably get incorrect results:

chasm4

The “Qty” measure, corresponding to the leaf measure table (OrderDetails) is calculated correctly, but the “Value” measure, corresponding to the measure held in its master (Orders), is not. This is because we get the “Value” of every OrderID, which may inflate the expected results.

The web application translation module separates the calculations, and unions the results by generating a query for each of the measures’ paths (path in terms of tables to go by). Then the web application translation module will group all the measures with the same tables’ paths into one query and union it with all the other “same path measures” with different paths.

The described “Chasm Trap” can be prevented like this:

chasm5

And the “Fan Trap” will be prevented this way:

chasm6