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.
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.
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
Helpful article, but would have been better with more screen shots.
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.
Chasm trap can be resolved by using Context or by using Multiple SQL statement for each measure depending upon the scenario. But why would you use Derived table to resolve the trap? Even though we need to write SQL query for that and on the top, it degrades the performance of the universe in SAP BO environment. I don’t think this would be the best practice.
Kindly give your point of view why would you prefer for using derived table.
I agree that Derived Table is not the preferred way if contexts can help.
But there are scenarios where the derived table can be useful. Consider a particular universe where differentiation between employee and contract staff (see above example) doesn’t matter and it will only require a unified list of all workers. In this case, the derived table will result in a universe which will be easy to use. Otherwise, we will end up with a universe where a simple listing of all workers will require generating multiple queries with different contexts and then combining them.
While designing a universe, we keep a particular use case and perspective in mind and then create an abstraction over the details of underlying schema. We don’t expose all the complexity of the schema into the universe.