As BW developers, we expect users to show some patience when they are executing BEx queries. Even when the window turns to ‘Not Respondin’ for a while. If its a rare occurance then we disregard it attributing system performance or connection. However, sometimes BEx query execution frequently turns our browsers to ‘Not responding’ and that can do tricks on your patience and nerves 😛 . In this blog post we will look at some of the possible causes behind it and try to think about the solutions for same.
BEx query executing runs for a long time and then turns into ‘Not Responding’ window.
OLAP calculations: Sometimes due complex design of RKF’s and CKF’s or presence of Cell references the query execution result into not responding.
· Reduce the strain on OLAP calculation.
· Try to get as many calculation as you can at data load level itself.
· If possible limit usage of ‘Cell references’.
· Characteristics having high volume can be moved into free characteristics area. Initial display of query result will be with small set of data.
· Try to breakdown the query in simpler queries using Report-to-Report interface
· Instead executing a complex query, consider saving the result if that query in a DSO using APD and then in a simpler query reading those results directly.
· try to eliminate obvious performance hindering objects e.g. virtual characteristics/KFs
· guide users to apply the drill downs in such a way that they happen only after certain filters are applied. This will increase the responsibility of users but atleast they will be able to see the result set.
· Try the other tools/techniques that will help saving resources at query runtime.
Huge Data set in background: Sometimes due to heavy dataset in the infoprovider may result into query execution result into not responding.
· Try to provide the complete selection criteria for the query. This will limit the data getting selected for query processing.
· Make sure you have created Indices after data loads
· Try to check the need to setting Cube partitioning, compression or introduction of Aggregates.
· If BWA already exists for your server, then consider setting your cube for BWA
· If possible, consider moving the selection criteria applied at RKF/CKF or local level to move at global default filter tab
C.SID Generation at Reporting.
· If your report is based on a DSO set to SID generation at reporting then try to change this setting to SID generation at load time
· If possible, move your report on a infocube or Multiprovider.
D.System Performance: Sometime unavailability of system resources result into query execution result into not responding.
· If possible, increase the system processes available for query processing.
· Run a SQL trace using ST05 and check the objects causing delay (http://scn.sap.com/docs/DOC-51233)
· Make use of BW statistics to find a possible cause/solution
· Wait for system resources to become available and try running query again.
· Check for any Patch/Updates or Notes required to be applied to the system
Apart from these, generally running the ‘Generate Report’ option in RSRT works for our benefit too, to understand/resolve the issue.
These are the one’s that are curruntly top of my head. I will keep updating this post as and when newer causes and solutions make their apearance.