cancel
Showing results for 
Search instead for 
Did you mean: 

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

silentbob
Explorer
0 Kudos

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?

Accepted Solutions (1)

Accepted Solutions (1)

silentbob
Explorer
0 Kudos

So whilst investigating this issue further I found, within an article about the FILTER_IN_FROM parameter (https://launchpad.support.sap.com/#/notes/0001438045), a workaround described as "Create a pre-defined condition in the universe that includes a table join, set as a mandatory filter".

So I went back to my universe and checked the properties of one of my existing conditions. I hadn't previously noticed, but there are options for making a condition mandatory. So I added the following condition...

I don't know exactly what the "Apply on List of Values" option does, but I would guess that it refers to the LOV for the dimension based on Branch_WG_Call_Handling. So this condition when created shows in italic, so is obviously hidden from within the query panel. I then removed the previously defined Self Restricting Join.

Going back to my report, I wasn't sure how to get the existing query to refresh in accordance with the new logic, so I rebuilt as a new query before removing the original. When I checked the generated SQL this is now as desired. I.e. this condition is in the WHERE clause and not in the FROM clause. The query ran successfully in just a few seconds.

So it seems that how you restrict data in BI4.2 comes down to how you want the SQL statement to be structured. Add a Self Restricting Join to include the restriction in the join(s) or alternatively create a mandatory condition to include it within the WHERE clause.

Answers (1)

Answers (1)

Joe_Peters
Active Contributor
0 Kudos

I created a simple mock-up in BI4.2 SP06 UDT but could not re-create the problem you're having (the complex self-join appeared only once). It's possible that either it's a bug, or it's some artifact from being converted from 6.5. If the latter, you could try deleting the join and re-creating it, but I don't have a high confidence that will work.

I noticed that you have SQL92 syntax enabled in 4.2. I did too in my test, but what if you set that to "No"?

It's not a perfect solution but should resolve this issue -- create a derived table in place of that table, and include the condition in the derived table's SQL.

silentbob
Explorer
0 Kudos

Hi joe.peters2 and thanks for the suggestions.

Regarding your first point, this is a very simply universe that has not gone under any transformation since originally created. As such I don't think it is likely that there are any hidden artifacts causing problems. The conversion from 6.5 to BI4.2 completed without any issues.

I tried setting the ANSI92 parameter to No, but this didn't have any impact. I also noticed a parameter named FILTER_IN_FROM, but this was already set as No.

The derived table sounds like it would work, but is perhaps not the most elegant way of dealing with this issue. I thought I would hold onto this as a last resort.

However, I think that I have now found the solution. I will post this as an answer myself. However, I will not accept it straight away as I am still waiting to hear back from our SAP Support partner, so it would be good to first see what they have to say on the issue.