Feb 26 2014

Big Universe + Security Profiles = Slow Query Generation

Categories: Rants, Report Techniques, Web Intelligence Dave Rathbun @ 6:01 pm

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.

9 Responses to “Big Universe + Security Profiles = Slow Query Generation”

  1. Comment by Andreas J.A. (Xeradox)

    Hello Dave,

    Thanks for the post & hint.
    I am wondering could you not use security on the DBMS side, such as VPD for Oracle, etc. to get rid of any security sretting in the universe?

  2. Comment by pradeep putty

    Hi Dave,
    We recently had same issue, the starting point is same, a simple prompt is taking long time ,
    later we figured out that due to aggregate awareness , the sql query generation itself taking time,
    if we use more objects the APS with dsl bridge is going down. As it is a UNX universe.
    sap note for the same
    1962705 – Universe Query “View Script” hangs when viewing script on aggregate_aware objects

    Can you please write a blog post on Aggregate awareness.
    Thanks,
    Pradeep.

  3. Comment by Dave Rathbun

    Andreas, it’s not row level security (which could be handled by the database) but instead is class security. There are some parent classes (which have a substantial number of sub-classes as well) that are invisible except for a selected group of users. That’s what is causing the problem for loading the query panel. Database security (row level security) would not have the same impact.

  4. Comment by Judy Mulders

    Dave, nice post. I wonder if the customer considered creating separate universes which were restricted by user group? Of course that approach has its own advantages and disadvantages.

  5. Comment by Dave Rathbun

    They did not, primarily because of the amount of duplicated effort that would require for universe updates.

  6. Comment by Sarah Phillips

    Hi – loving your site. Can you do something on the Security model in 4.1 SP2? i always find this a nightmare!!

  7. Comment by Dave Rathbun

    Hi, Sarah, there’s a reason I don’t write much about security. I probably find it just as confusing as you. ;)

  8. Comment by Hans Rens

    Reading this article, I thought: “Too bad that LINKED UNIVERSES are gone in the unx ones”
    There SHOULD be another option than having “two universes needing double updates” just for this kind of security.
    In BOXI 3.(6 now) we still have universes using a “common kernel”.
    Yes, they are messy in promotion. Yes, they need extra attention when the include/kernel universe changes. But they DO permit just that: give a BASIC universe to “most users” to use, and then have an extended version for the “super users”.
    All existing reports can then be duplicated, the copy gets renamed (for the select group of super users), and those renamed ones are re-pointed to the “extended universe”.
    In the kernel one, classes and objects can now be hidden, no better: removed. And webi will happily remove the stale objects from its queries ONLY in the restricted report version.

    From now on, double universe maintenance is heavily reduced.
    a) new objects “for everyone” go in the kernel, new objects only for the power users go in the extended version. Of course, the extended version needs SOME attention at each update of the kernel, but if NO classes of the kernel are hidden, that attention is minimal.
    b) Report queries are now against separate universes, and will be updated automatically when the universe changes … or when its kernel has changed. Because at a kernel change, the dependent extended universe will get refreshed too. It’s as simple as a new “import universe” in developmen. The import asks “there is a newer (kernel) in the repository, do you want to overwrite your local copy?”
    I answer “yes”, save the extended universe, re-export it … done.
    Promotion tools do the rest. And reports “will know” that their universe has changed.

    But the user selection / security filtering is now done outside the report.

    Why was the idea of kernel universes dropped in BI4 ?

  9. Comment by Dave Rathbun

    Hi, Hans, thanks for your comment. First, you probably should know if you don’t already that linked universes are alive and well in BI4 if you use Universe Designer. It’s just in Information Design Tool (IDT) that they’re missing. From what I understand, the concept of Business Layers on top of a shared data foundation was supposed to replicate some (but not all) of the linked universe features in IDT. But they didn’t solve for all of the scenarios, obviously.

Leave a Reply

If you want to include formulas or code in your comment, please read my Tips for formatting comments first. Tags you can use are listed below.

XHTML: You can use these tags: <a href="" title=""> <acronym title=""> <blockquote cite=""> <code> <em> <strike> <strong> <sup> <sub> <u>

Confirm submission by clicking only the marked checkbox:

     **         

Please remember that comments that are not related to this blog post may be ignored or deleted without notice. If you're looking for help on a topic you have already posted on BOB then please do not repost your question here.