How to Replace a Table with a Derived Table

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.