An essential aspect of creating a BusinessObjects universe is to define joins among tables in ‘Universe Design Tool’. Even if the joins are correctly defined from relational point of view, the universe might not generate the correct results in certain cases. There are few pitfalls to avoid which are specific to the business of designing universe. These pitfalls, generally referred as join problems, come in three major varieties: loops, chasm traps and fan traps.
Loop
Loop is the existence of more than one join paths between two tables. In other words, if we move among tables following joins and we can reach back to the original table using a different path, then we have a loop.
Loops will cause less number of records to be returned than expected.
The Universe Design Tool can automatically detect loops and suggest solutions (aliases and contexts).
Chasm Trap
We are in Chasm Trap when a table joins with two others in one-to-may relationship. Learn more about them here.
Fan Trap
Fan trap is a less severe problem than other two. It does *not* affect the number of records returned from the query, rather it affects the aggregation of an attribute.
Consider, an invoice record with three lines. While summing invoice total if it gets accounted thrice (once for each invoice line), than we are in Fan Trap.
Techniques for resolving join problems
As universe designer, the toolbox consists of following to overcome the join problems:
Alias
A table can be duplicated in schema by giving it an alias (Both table and its alias refers to the same underlying table in database). This is the most common way to resolve join problems. Use of an alias can break loops or undesirable join paths.
Contexts
Context is a set of joins. It divides the universe in overlapping sets of tables and joins. If your table is part of more than one context, the report designer tool might ask the user to select the context if it can’t be inferred.
Self Restricting Join
Join can be defined as condition on a single table that limits the records returned from it. Such a join (which is not actually a join at all) is called Self Restricting Join.
Derived Table
Derived Table is like a database view. It allows us to create new tables in Universe based on a SQL query.
Shortcut Join
As the name says, it is shortcut to a longer join path. It is important to identify a shortcut join, otherwise we may have a loop.
Techniques to avoid
Joins could potentially cause issues which might motivate us to do without them, for instance, we can have sub-query in select clause to fetch data from another table. This is not recommended as query generated by universe might group by columns, in such a case query will not be valid.
So, the general rule is not to have sub-queries in select clause.
The above applies to ‘Universe Design Tool’ version 14.0.7. More at Official SAP BusinessObjects Universe Design Tool Guide
