Chasm Trap: Designing SAP BusinessObjects Universe

While designing SAP BusinessObjects universe with Universe Design Tool, Chasm Trap is one of the major join problems that one can come across.

We are in Chasm Trap when a table joins with two others in one-to-many relationship. Chasm Trap produces more data than expected.

Example

Consider the following ER diagram where DEPARTMENT is having 1-to-many relationships with EMPLOYEE and CONTRACT_STAFF tables.

Let’s assume we have following data in these tables.

image

BusinessObjects universe will produce something similar to the following query to join the above 3 tables:

select depart_name, employee_name, name "contract staff"
from department d
  inner join employee e on d.department_no = e.department_no
  inner join contract_staff c on c.department_no = d.department_no

Result of the query will have more rows than expected.

image

Resolution

Universe Design Tool doesn’t detect chasm traps automatically. They can be identified by visually inspecting the ER diagram.

Using Contexts

The recommended way of resolving Chasm Trap is to use contexts which will separate the two tables into separate contexts (employee and contract_stafff in our case). If both tables are used in the report, separate queries will be generated resulting in two tables in Web Intelligence report.

Using Derived Tables

Another approach which I have found handy in many cases is to use Derived Table functionality. Derived Table in universe designer can merge the two tables into one. Additional column containing record type can be introduced to still be able to tell the records apart (in our case, record type would be employee or contract_staff). This simplifies the design and avoids the Chasm Trap.

More at Official SAP BusinessObjects Universe Design Tool Guide

6 thoughts on “Chasm Trap: Designing SAP BusinessObjects Universe

  1. I understand the concept of Chasm and Fan trap, my questions are:

    if there is scenario like A – < B -< C in this one table A is an aliased table – so does this still a Fan trap? If yes how do I solve it ?

    Now next scenario A -< B -< C — E in this table A and D are aliased tables so does this join path creates a trap – its understood that B -< C -< D is Fan trap and how do I solve this scene?

    Appreciate the help,

    Regards,
    DA

    • Dear DA,

      Just creating alias will not have any impact on Fan trap. You will have to create an alias and a context.

      The basic idea for resolving fan trap is to return the measure with fan trap as a separate query. So, if you start with 3 tables, after alias you are going to have 4. And then context will be used to serve measure in a separate query.

      To see detailed explanation of this, checkout section ‘5.7.3.1 Using aliases and contexts to resolve fan traps’ on page 263 of ‘Universe Design Tool User Guide’.

      • Thanks Br. Umar, I haven’t created an alias in our universe to solve trap issue, we have quite some aliases we use to get values for our codes, so my question – is it considered as a trap (chasm or fan) if there is an alias table/s involved in the join path.

        • Yes.

          If you have two one-to-many joins in a row among three tables (like A –< B -< C), then it can cause fan trap. It doesn't matter whether the tables are actual physical tables or aliases or derived tables. They all are the same from joining perspective.

          Please note it can cause fan trap but not necessarily. The other condition is that you have to have a measure on the middle table (B in A –< B -< C) which you are aggregating in your query.

Leave a Reply

Your email address will not be published. Required fields are marked *