All of us universe designers, when creating a universe for the first time, learned one way or another that that the default limit of 5000 rows is often impractical:
What then, is the best practice for this setting, as well as the execution time limit? I won't give you 2 numbers to just drop in there, because you and I can do better than that. The purpose of this setting is to prevent unreasonable queries from consuming database resources - you know, the ones that run for hours or retrieve millions of rows and trigger a call from your DBA.
So what then is an "unreasonable query"? Obviously this depends on a number of factors such as the size of your overall data set and the performance of your database. But more importantly, it depends on the application. If you are supporting dashboards that pull in aggregate data, you may never see more than 500 rows. On the other hand, I have seen reasonable report requirements to pull over 100,000 rows in a query. The same applies to the time limit - ad-hoc analysis users may give up or hit a portal/client timeout after 45 minutes, but there may be legitimate scheduled reports that take 2-3 hours to run overnight.
So how do you support such activity (big/long running queries) while still imposing reasonable limits on ad-hoc analysis, all in the same universe? Create access restriction sets (found in Tools, Manager Security in Designer), and map them to user groups. You may need to work with your Business Objects administrator to define these groups in the security model.
Now different users (belonging to different groups) will experience different controls on their universe query.
2 Comments