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
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.
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
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.
Quick question about step 2). I didn’t get why you shouldn’t change the table in the combo box? What select could it screw up, where have you seen this happen? I’ve substituted tables several times, and haven’t seen this issue. I thought you would see more of a problem with typing instead of choosing from combo box, isn’t this a bit of a hack?
A sidetrack: A problem I’ve encountered with derived tables in BOXI (as opposed to in older versions) is when the developer has typed the select (column names being the problem here) using lower case letters, as BO then doesn’t always recognise lower case column names as columns if connected to SQL Server (at least 2008). We lost a lot of joins to derived tables because of this when we upgraded to BOXI (plus to some reporting. Not seen this problem in versions before BOXI, nor Oracle, it seems Oracle “ups” the case. The problem isn’t specific to derived tables, but the database schema is usually created in upper case anyway.
Changing the table works fine for simple equi-joins. But it will screw up the complex joins. E.g. if you change B to B1 in the combo-box for the join
A.x=B.x and A.y=B.y
the join will changed to
B1.x between A.x and A.y
Isn’t it better practise to split the join in two equi-joins? We have a lot of joins like the one you are describing, and I’ve decided to split them. We are thinking of moving to ANSI92, and so all my (+) outer joins in Oracle will still be (+) outer if they are typed and not chosen from the combo-box.
Hm, very interesting idea.
Regarding the case of characters. I believe BO does not parse the SQL. It always use DBMS engine to get the list of fields of a derived table.
So maybe you used different settings of SQL Server databases? By default SQL Server creates case sensitive database, but you can also create case insensitive database.
I am not really sure, but the only thing we changed was to upgrade from BO 6.5.1 to BOXI, and it used to work before, so assumed it was BOXI that was the problem, combined with the fact that SQL Server seemed to “remember” that we wrote the sql in lower case when viewed in Designer (even if our DB/SQL editor tools showed everything in upper case) where Oracle seemed to store the column names as upper case no matter how we coded it.