Skip to Content
0
Oct 12, 2020 at 08:47 AM

Self Restricting Join - Is it really supposed to work like this now?

215 Views Last edit Oct 12, 2020 at 08:58 AM 6 rev

I am coming from Business Objects 6.5 Desktop Intelligence to SAP BI 4.2 Web Intelligence. I was able to bring across my old universes into 4.2 using the Universe Design Tool. I am now in the process of reviewing our reports and rebuilding those required reports on the new platform. In doing so I have now come across the following issue...

I have a universe where the main data table has a self restricting join in order to automatically exclude certain records from all queries/reports...

When you look at the SQL generated by the query in 6.5 you can see that this has added this condition to the WHERE clause...

This works as required with no problems. In this first report that I am looking at, this query completes in about 4 seconds.

The self restricting join is defined in exactly the same way within the BI 4.2 universe. However, when my report query would just run and run without completing I checked the SQL that was generated where I can see that instead of putting this condition in the WHERE clause it now appends it to every single join involving that table...

This is clearly having a massive performance impact.

Is it correct that this is how self restricting joins are now handled? Is there anyway to change how they are handled, to force it back into the WHERE clause?

If this is not possible, then I guess that the only way to handle this may be to add some new indexes specifically for this issue?