≡ Menu

Types of Joins in Business Objects Universe

In Previous post we have covered cardinalities in BO universe. In this post we would be learning types of join in SAP Business Objects Universe and how to set up joins.

Following join types are available in Business Objects Universe Designer which can be used to join two tables.


Equi-join is join which uses “=” equal operator to join two tables. Generally equi join is used to join primary table using primary key with foreign tale using foreign key

When two tables are used using equi-join it returns all those rows from selected table which matches the equality condition.

Outer Join

Outer join links two tables using a join operator. When tables are linked using outer joins the select query returns all the records which matches the join condition and returns all the records from one table even though do not match the join condition.

There are two types of outer join

Left Outer join: This outer join returns all the records from left table even when they do match the join condition.

Right Outer join: This outer join returns all the records from right table even when they do match the join condition.


You should avoid using outer joins as it may cause query to run slower. Outer joins should be placed at the end of a join path otherwise it maybe causes other queries to match a NULL equality condition which might give an error.

Theta Join

A theta join is a between-type join that’s joins tables based on a relationship other than between two columns. It is generally used to demonstrate ranges. A theta join can use any operator other than equality operator.

Lets see how to create a theta join

  1. From Insert menu click on join to create a new join
  2. Select the table1 which should be joined to another table using between operator.
  3. Select the table2
  4. Now select the two columns from table two which should represent the range.
  5. Set the cardinality to N-1
  6. Click Ok


This theta joins uses between operators to join two tables

Shortcut Join

A shortcut join is a join which provides shorter way to join two tables by avoiding intermediate tables between join paths of tables. It is very helpful to improve the performance of a query as it reduces number of joins in a query.

Shortcut joins are also useful to solve loops in a Universe.

Lets take an example of Shortcut join.

Shop_facts, Article_lookup and product promotion facts are joined through Article id. Now if we want to see Duration and amount sold the query will have un-necessary join of shop_fact and Article_lookup table as there is no join between shop_facts and product_promotion_fact.

However if we join shop_facts and product_promotion_fact, it will create a circular loop which might confuse universe to decide on which join path to take. This can be avoided by using shortcut join instead of using normal join. Shortcut join is represented by dotted line in designer.

Self Restricting Join

Self restricting join is not actually a join but a restriction on a table. Generally it is used to restrict the data returned by a table.

To create a self restricting join

  1. Click on Insert menu and click on join to create new join
  2. Select the table on which you want to create self restricting join.
  3. Select the same table name from table 2 combo box also.
  4. Select the column which should be used for self-restricting join.
  5. Edit the expression and put the restriction condition
  6. Click OK.


Cardinality of self restricting join should be set to 1:1 otherwise it gives error while detecting contexts.

Try to create each type of join in BO universe and practice it and test it using Query Panel.

{ 5 comments… add one }

  • C.Mani August 26, 2011, 11:48 am

    exllnt porformance

  • sree May 19, 2012, 5:19 am

    Excellent stuff.

  • premsagar July 18, 2012, 4:12 am

    May i knw how to write the sql queries in sap bo and where i want to write it.
    concepts of joins

  • BIDW Team July 18, 2012, 4:25 am

    You can write queries while creating universe. Ideally you should use derived tables to put your custom logic in SQL.

  • Michele July 22, 2013, 10:18 pm

    We have a Business Objects Universe with an outer join between positions and people. When I write a report in Web Intelligence, if there is no criteria, I see the expected (full) dataset. However, as soon as I add criteria to the Query, it acts like a regular inner join. I have not been able to find a work-around other than writing custom SQL (i.e. moving the criteria into the where clause myself.)

    My boss is not pleased, because this is not something our average user will be capable of doing and everyone here seems to think thsi should be easy.

    Any thoughts on something I can do differently in Web Intelligence? (I can request access to the rich client, but am not even certain if this will help)

    Our example problem:
    We have some positions which are vacant. We have outer-joined the person fact to the position fact. This works great, until I add a criteria to only show Active people. When I add the Active person criteria, Web Intelligence displays only the filled positions. (I cannot say Active or blank, most of the time the people I am trying to not see are terminated)

    Is this possibly a modeling issue or a universe design problem? Am I going to need a different universe to get a full position/person report without custom SQL?

Leave a Comment