Chasm and Fan Traps
Note: The image on this page were taken in the desktop version of Sisense, however, the same principles described on this page also apply to the web-based ElastiCube Manager.
Chasm and fan traps should be avoided when building your ElastiCube schemas.
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.
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:
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).
If you try to aggregate both measures simultaneously (using the query below), you will probably get incorrect results:
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:
And the “Fan Trap” will be prevented this way: