SAP BusinessObjects Universe: Using ANSI92 SQL syntax

When writing SQL queries, I prefer the JOIN clause syntax over (+) in where clause for creating table joins. It is more readable and maintainable as join logic is separated from the where clause.

BusinessObjects Universe can be configured to generate JOIN clause (ANSI92) syntax by setting a parameter in ‘Universe Parameters’ dialog available from File menu.

 

image

 

We get some extra benefits by using ANSI92 syntax, for instance:

Full Outer Join

The functionality to define Full Outer join is available with ANSI92 syntax only.

image

Advanced Join Properties

The Advanced Join Properties give additional control over the generation of SQL queries from the universe. It determines whether a conditional expression should be included in where clause or join/on clause. These options are only supported with ANSI92 syntax.

image

 

The above applies to ‘Universe Design Tool’ version 14.0.7.  More at Official SAP BusinessObjects Universe Design Tool Guide.

BusinessObjects Universe: Defining Complex Outer Join

A complex join contains more than one condition in the join expression. Consider the following expression where one line of sales order joins with a line on invoice,

INVOICE_LINE.ORDER_NUM=ORDER_LINE.ORDER_NUM
and INVOICE_LINE.ORDER_LINE_NUM=ORDER_LINE.ORDERLINENUM

If we define a complex join in BusinessObjects Universe Design Tool, than the option to make it outer becomes disabled.

clip_image002

There are three possible ways to get around this problem (that I found), which are described below.

 

1) Create two separate joins

Create two separate joins to split the complex expression into simple ones. This will enable the ‘Outer join’ check box. While creating the query, the universe will automatically combine the two joins into one complex expression.

clip_image006

 

2) Set the Outer Join first, then specify complex expression (ANSI92 only)

Change the expression to simple temporarily; this will enable the ‘Outer join’ checkbox. Select the ‘Outer join’ and change the join expression back to the designed complex expression. Universe Designer will not only remember that ‘Outer join’ flag but also generate the right query at runtime with outer join clause.

Note: This solution only works if ANSI92 parameter is set to ‘Yes’. That is, join/on clause syntax is used for creating SQL query.

clip_image004

The universe will generate SQL similar to the following:


SELECT DISTINCT
INVOICE_LINE.INVOICE_NUM,
INVOICE_LINE.INVOICE_LINE_NUM,
ORDER_LINE.ORDER_NUM,
ORDER_LINE.ORDERLINENUM
FROM
ORDER_LINE RIGHT
OUTER JOIN INVOICE_LINE
ON (INVOICE_LINE.ORDER_NUM=ORDER_LINE.ORDER_NUM(+)
and INVOICE_LINE.ORDER_LINE_NUM=ORDER_LINE.ORDERLINENUM(+))

 

3) Directly specifying the complex expression with (+) syntax

We can directly specify the complex join expression and make it outer by using (+) syntax. Off course, this will only work when ANSI92 parameter is set to ‘No’.

clip_image008

Generated SQL will be something like:


SELECT DISTINCT
INVOICE_LINE.INVOICE_NUM,
INVOICE_LINE.INVOICE_LINE_NUM,
ORDER_LINE.ORDER_NUM,
ORDER_LINE.ORDERLINENUM
FROM
INVOICE_LINE,
ORDER_LINE
WHERE
( INVOICE_LINE.ORDER_NUM=ORDER_LINE.ORDER_NUM(+)
and INVOICE_LINE.ORDER_LINE_NUM=ORDER_LINE.ORDERLINENUM(+) )

 

The above applies to ‘Universe Design Tool’ version 14.0.7.  More at Official SAP BusinessObjects Universe Design Tool Guide

Designing BusinessObjects Universe: Join problems in schema and resolution techniques

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.

BO Universe Loop

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.

Chasm Trap

 

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.

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