Category Archives: Universe Designer

BusinessObjects Universe Designer

How to Replace a Table with a Derived Table

6 Replies

This post describes the detailed steps to replace a database table with a derived table. Also it describes how Universe Documenter can help to achieve the perfect quality even for such complex modification of a universe.

You may need to replace a table with a derived table if your universe is affected by the self-outer join issue.

We will consider the following schema:

The task is to replace BO_CUSTOMER with a derived table to avoid self join in the universe database structure.

0) Make a backup of the universe and the reports that use the universe

Also export a copy of the universe to CMS. This will be required on the step 9 for verification.

1) Create the derived table that contains SQL with the self join SQL in WHERE

Create table BO_CUSTOMER_ with the following SQL:

SELECT * FROM BO_CUSTOMER WHERE ACCESSUSERNAME=@VARIABLE('BOUSER')

2) Switch all joins from the original table to the derived table.

Edit the join expression. Do not change tables in the combo-box, this may screw up the select.

The self join BOEMPLOYEE.ACCESSUSERNAME=@VARIABLE(‘BOUSER’) should be removed. You will be prompted that it is used in contexts. Ignore this message.

The contexts do not need to be updated.

3) Update objects expression and the expression of the predefined conditions

  • Select first class in the universe.
  • Hit Ctrl+F.
  • Enter BO_CUSTOMER in the field “Find what”
  • Enter BO_CUSTOMER_ in the field “Replace”
  • Select “SQL” in the Look Also In
Sometimes Replace All works fine, but it would be more preferable to change objects one by one.

4) If the table is a fact table and aggregate awareness is used in the universe, check if you need to update aggregate navigation.

  • Open aggregate navigation
  • Select the fact table
  • Select option “Incompatibles only”
  • Check objects and predefined conditions.
  • If the list “Associated Incompatible Objects” is empty, you do not need to do anything.
  • If the list is not empty, the same incompatibilities must be defined for the derived table.
(This is not needed in our example)

3) Check if you are lucky

Try deleting the original table. If the table was removed without prompts, you finished with the table. If you see the prompt that the table is used in object definitions, do not remove the table.

  • The table can be used in index awareness.
  • The table can also be used to force join with another table.

6) Modify the objects that use join forcing

  • Click right button on BO_CUSTOMER
  • In pop-up menu select View associated objects
  • If no objects highlighted in class structure, go to the next step
  • Go through all highlighted objects
  • The select of such objects do not contain reference to the table BO_CUSTOMER because we replaced all BO_CUSTOMER to BO_CUSTOMER_ on the step 3.
  • Click Tables and change BO_CUSTOMER to BO_CUSTOMER_ holding Ctrl button.

7) Update objects for which index awareness is defined.

There is no easy way to find the objects that has index awareness in Universe Designer. You need to go through all objects and check Keys in properties:

The easy way is to build the list of index awareness using Universe Documenter

8) Save, export

9) Verify

The modification described here is very complex. It is easy to make a mistake. Luckily it is possible to use the tool Universe Documenter to identify the mistakes.

To reduce the number of differences, import the copy of the original universe and change the name of the replaced table to BO_CUSTOMER_ and export the universe back to CMS.

Import the modified copy and the corrected universes in Universe Documenter, perform diff (How to diff universes using Universe Documenter) and check all highlighted differences. The two universes must be almost identical with an exception that the BO_CUSTOMER_ in the corrected universe is a derived table.

Problem of using self joins in combination with outer joins

Leave a reply

Problem

(It is assumed that the parameter ANSI92 of the universe is set to Yes, the parameter SELFJOINS_IN_WHERE is either not set or is set to No.)

The table A is affected by the self/outer join issue if it has a self join and it is joined to a table B with the following outer join:

If a query use these two tables, the self join SQL will be put into the outer join SQL causing the generated SQL query to ignore the self join.

If the query contains at least one inner join of A with another table, the issue will not appear because the self join will be put into the inner join SQL, so the self join will be applied to the table A. Due to this, the issue appear rarely.

Example 1

The table structure contains two tables BOEMPLOYEE and EXVENDOR. BOEMPLOYEE has a field ACCESSUSERNAME which is used to implement access control to the table.

If the query uses these two tables, the self join will be put into the outer join SQL and therefore ignored:

SELECT
  BOEMPLOYEE.NAME,
  EXVENDOR.NAME
FROM EXVENDOR RIGHT OUTER JOIN BOEMPLOYEE
  ON (BOEMPLOYEE.VENDORNUMBER=EXVENDOR.VENDORNUMBER
      AND BOEMPLOYEE.ACCESSUSERNAME = @VARIABLE('BOUSER'))

Example 2

Here we have a table EXFINANCEENTRY that is joined with BOEMPLOYEE using inner join.

In this case, the self join the self join will be put both into the outer join SQL and inner join SQL. Because it is put in the inner join, the self join is applied to the BOEMPLOYEE and the issue will not appear:

SELECT
  EXFINANCEENTRY.TRANSACTIONNUMBER,
  BOEMPLOYEE.NAME,
  EXVENDOR.NAME
FROM
  EXVENDOR RIGHT OUTER JOIN BOEMPLOYEE
  ON (BOEMPLOYEE.VENDORNUMBER=EXVENDOR.VENDORNUMBER
      AND BOEMPLOYEE.ACCESSUSERNAME = @VARIABLE('BOUSER'))
  INNER JOIN EXFINANCEENTRY
  ON (EXFINANCEENTRY.EMPLOYEENUMBER=BOEMPLOYEE.EMPLOYEENUMBER
      AND BOEMPLOYEE.ACCESSUSERNAME = @VARIABLE('BOUSER'))

The issue in BO XI 3.1 FP3.7, FP3.8, FP4.2, FP5.2

In the mentioned FPs, the algorithm of generation of joins has changed: the self join is put only in one join SQL connected to the table. (This is error that tracked under the problem report id ADAPT01640966. SAP KB article 1743593 – Self join is ignored by SQL query generator in WebIntelligence Rich Client and Universe Designer in SAP BusinessObjects XI 3.1)

Example 3

The self join is ignored even though there is an inner join:

SELECT
  EXFINANCEENTRY.TRANSACTIONNUMBER,
  BOEMPLOYEE.NAME,
  EXVENDOR.NAME
FROM
  EXVENDOR RIGHT OUTER JOIN BOEMPLOYEE
  ON (BOEMPLOYEE.VENDORNUMBER=EXVENDOR.VENDORNUMBER
      AND BOEMPLOYEE.ACCESSUSERNAME = @VARIABLE('BOUSER'))
  INNER JOIN EXFINANCEENTRY
  ON (EXFINANCEENTRY.EMPLOYEENUMBER=BOEMPLOYEE.EMPLOYEENUMBER)

Solution

Either you using the mentioned FPs or not, the described situation is a design flaw of the universes. In such situations, the self join should be embedded into the derived table.

For the above examples, the derived table can be following

SELECT * FROM  BOEMPLOYEE WHERE ACCESSUSERNAME = @VARIABLE('BOUSER'))

The table BOEMPLOYEE should be replaced with this derived table.

See also

Use of Outer Joins in Combination with Self Restricting Joins
How to Replace a Table with a Derived Table

Use of Outer Joins in Combination with Self Restricting Joins

6 Replies

Problem

BusinessObjects use some algorithm for SQL generation based on objects selected in the query and the universe. There are a number of parameters that control SQL generation. The problem is that in some cases BO generates SQL that is not intuitively expected.

What we would expect is that the self restricting joins are applied to the tables and then the tables are joined.

When SELFJOINS_IN_WHERE=No, the self restricting joins are put in the ON clause of the SQL. The generated SQL ignores the self restricting join on the table FACT.

SELECT DISTINCT
  FACT.A,
  DIM.B
FROM
  DIM  RIGHT OUTER JOIN  FACT
  ON  (FACT.DIM_ID=DIM.DIM_ID  AND  FACT.TYPE=0  AND  DIM.TYPE=0)

When SELFJOINS_IN_WHERE=Yes, the self restricting joins are put in the WHERE clause.  The effect of this is that the outer join turns into inner join in the generated SQL.

SELECT DISTINCT
  FACT.A,
  DIM.B
FROM
  DIM  RIGHT OUTER JOIN  FACT
  ON  (FACT.DIM_ID=DIM.DIM_ID)
WHERE
  ( FACT.TYPE=0  )
  AND  ( DIM.TYPE=0  )

In fact, we expect the following SQL:

SELECT DISTINCT
  FACT.A,
  DIM.B
FROM
  DIM  RIGHT OUTER JOIN  FACT
  ON  (FACT.DIM_ID=DIM.DIM_ID  AND  DIM.TYPE=0)
WHERE
  ( FACT.TYPE=0 )

BusinessObjects cannot place self restricting joins conditionally depending on the join structure. (Probably this will make the algorithm too complex, so this is not a bad thing)

A workaround is to avoid using of self joins using derived tables.

A Solution when SELFJOINS_IN_WHERE=No

Replace FACT with a derived table FACT_D

SELECT DIM_ID, A FROM FACT WHERE FACT.TYPE=0

The generated SQL will be:

SELECT DISTINCT
  FACT_D.A,
  DIM.B
FROM
  DIM RIGHT OUTER JOIN (
  SELECT DIM_ID, A FROM FACT WHERE FACT.TYPE=0
  ) FACT_D
  ON (DIM.TYPE=0  AND  FACT_D.DIM_ID=DIM.DIM_ID)

A Solution when SELFJOINS_IN_WHERE=Yes

Replace DIM with a derived table DIM_D

SELECT DIM_ID, B FROM DIM WHERE DIM.TYPE=0

The generated SQL will be:

SELECT DISTINCT
  FACT.A,
  DIM_D.B
FROM
  (
  SELECT DIM_ID, B FROM DIM WHERE DIM.TYPE=0
  ) DIM_D RIGHT OUTER JOIN FACT
  ON (FACT.DIM_ID=DIM_D.DIM_ID)
WHERE
  ( FACT.TYPE=0 )