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.
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.
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.
Universe Design Tool doesn’t detect chasm traps automatically. They can be identified by visually inspecting the ER diagram.
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.