Dave's Adventures in Business Intelligence

The actual origin of the concept of a “red herring” is unknown, but that doesn’t stop it from causing grief while trying to diagnose a performance issue. If you are not familiar with the concept, a red herring is something that initially appears to be relevant but ultimately is proved to have nothing to do with the actual issue. It’s a popular technique for mystery novels… and in tech support calls.

Case in point: Today I had to help someone who was wondering why their report took over thirty seconds to display a prompt window when there was only one prompt in the document. Clearly it was a prompt issue, right? Or something related to the list of values definition for that object?

The object in question was Customer Number. Typically for unique values like this I would turn off the list of values feature, especially given the volume of data in this particular table. Therefore that was the first thing I checked. It turned out that the LOV was already turned off for this object.

Hmm. What next?

I looked at the way the prompt was defined. There wasn’t anything particularly difficult or complicated about it, other than the same prompt was used in six different data providers. Since the prompt was limited to one selected customer, I decided to leave only the first prompt in place. I updated the remaining five data providers so that they retrieved the customer number from the results from the first query. (This is a very powerful technique, and one that many report developers are not aware of.) That meant that the prompt only appeared once, and should improve the performance, right?

Wrong again. The performance was just as bad after that change as it was before. 😕

Big Universe + Security Rules = SQL Generation Performance Issue

At this point I remembered something that I learned last year (and have been meaning to write about ever since). The universe used for this document is one of the most complex that I have ever had to work with. It has over 10,000 objects (and over 1,000 contexts) and has a number of security rules in place as well. Based on your group membership you can either see / not see a selected list of parent classes. We defined the security rules at the class and group level (rather than at the object or user level) in order to keep them as simple as possible.

What we determined last year was that despite the security rules being defined at the class level, when a user attempts to run a query every object (in every data provider) is checked individually. The more objects you have in the query, the slower the process goes. So in this case it wasn’t the prompt that was the problem at all, it was the fact that several of the data providers had several dozen objects! Thus, the red herring.

During our testing last year, we made a copy of the universe that had no security rules at all. To confirm my suspicion, I re-pointed this problem document to the stripped version of the universe and refreshed the document. After several trials, here are the results:

• Document using secured universe takes an average of 32 seconds just to display the prompt window, then another 25 seconds to execute the query.
• Document using the non-secured universe takes an average of 1 second to display the prompt window, then another 13 seconds to execute the query.

So it appears that not only are the security rules applied prior to the prompt being displayed, but perhaps they are re-checked prior to the execution of the query. I can’t explain why the execution time would be so different between the two versions of the universe otherwise.

As a final test, I went back to the original document and removed most of the objects (thereby breaking the report, but that wasn’t the point of the test) and ran the query. The prompt came up in about 3-4 seconds rather than the 32 seconds it was taking earlier. Clearly the more objects I have on each query, the more work the security engine has to do even if just to present the prompt screen to the user.

Conclusion

One obvious solution would be to try to reduce the size of the universe, but that won’t happen any time soon. Another solution would be to remove the security rules from the universe, and for the expected reasons (duh, it’s security) that won’t happen either. So while I have managed to finally diagnose the true issue (not prompt related at all) I still don’t have a solution. I know we asked SAP about this last year, and we didn’t get any resolution from their end.

Bottom line: I don’t have a fix for you. 🙂 But if you’re wondering why a prompt is taking a long time to render, check to see if it’s related to security profiles instead of the prompt. It might save you some time chasing a smelly fish.

The scenario outlined in this post was based on experiences with BI4.0. I don’t know if the same behavior is present in BI 4.1.