Troubleshooting Long Running Reports

I caught a tweetstream the other day that Diversified Semantic Layer’s friend and past guest, Michael Welter, had a question on diagnosing and solving for long running queries in Web Intelligence (Webi)…at least I think it was Webi. I saw it fly by and really wanted to weigh in, but it got away from me. I did not necessarily see answers to Michael’s question in that time frame, so I wanted to take some things that occurred to me and that I use in my debugging process.

I may be overly sensitive to this topic, now that I think of it. My first employer had a very very large data warehouse (at the time, largest in the world every other month). While full of rich data, it was painfully slow and as a result BusinessObjects was the perceived as slow by the business. Let’s work on that. I’m going to keep this post within the confines of Webi. There may be similar settings and techniques that can apply to Crystal Reports, Xcelsius, Explorer, etc. (even Deski…sorry Jamie).

When trying to figure out exactly where performance bottlenecks lie, I go in one of two routes:

1. I look at my BOBJ environment performance and tuning
2. I look at my database environment performance and tuning

Server Side
First thing’s first. Is the Webi server (or servers) over saturated? What is your environment’s concurrency? Let’s start building out some things to look at in no particular order.

• Measure concurrency/scale up – Each server can maintain a mixture of BOBJ servers that allow for a maximum threshold of users and concurrent reports. Have you hit that peak for the number of allocated Webi Processing Servers? If not, consider scaling vertically by adding additional Webi Processing Servers, assuming there is sufficient memory and CPU at your system’s reported peak by your system’s monitoring technology. IMPORTANT: If you aren’t already monitoring your system for utilization, you should be. The Diversified Semantic Layer concurs.
• Maximum connections per server – Each server has a threshold for the number of concurrent reports it can process. I believe there is a tipping point in which the number of concurrent reports being handled by Webi Processing Servers is greater than the CPU/memory your box has available. If during batch, for example, this threshold is being crossed check your SLA to see if you can reduce that max number of connections.
• Cycle time – Some times Webi Processing Servers get stale. XI 3.x introduced this setting to allow us to auto-cycle Webi. Ensure that based on your concurrency, this is set low enough based on activity that it can actually find a time to cycle itself.
• Average query runtimes – While arguably related to database tuning, think big picture. What does auditor reflect that all other jobs are doing? If 60 concurrent reports are taking 10 minutes each to run and are triggered at 5am, that is 600 minutes of BOBJ and database think time. Can that load be spread out at all to reduce concurrency on the database, making things more efficient in terms of how much is running at the same time? This can only be determined by actually analyzing database performance in parallel with the system and audit log.
• Scale out – Every system has to anticipate growth. At some point, it just makes sense to add servers. That’s for your admin/leadership to decide, but when you have used up all the resources a server has to offer while trying to maintain the commitment to an SLA, grow it.
  

Database Side
So taking off my system admin’s hat and putting on my developer hat, there’s a lot to be done to maximize performance on the database side too.

• Functions in the where clause– Really, to my knowledge, you can impact performance if you try and do any type of function based joins in the where clause. Oracle, for example, comes with the handy option to make a function based index (arguably with its own overhead). But in any case, figure out whether these are causing performance issues for you. A function based index might be helpful on a where clause like:SUBSTR(SOME_COLUMN,5,15) LIKE ‘STRING%’
• Indexes – Obviously, right? I’ve actually worked with DBAs that claim you can over-index a database. While I put a lot of faith in my modelers and DBAs that the model is engineered properly, ensure that indexes are correct and then ultimately, this leads into….
• Explains – There was a time that I could just start to read explain plans. However, this is where a good, close relationship with a DBA can come in handy. But don’t look at it from an individual report perspective, think big picture with the rest of the universe. If a change based on an explain is impacting to a universe, test, test, and test more.
• Database performance – While mentioned above, the only people that know if the bottleneck is your query or the database is the DBAs. Consult with them to compare logs on their side to see how heavily the system is being utilized if the problem isn’t necessarily isolated to a single report.
• Co-located databases – This one is simple. Don’t put the BOBJ CMS and Auditor databases on the same server as your warehouse, or at a minimum, not on the same instance as other high utilization databases.

• Because that is how the universe works – I don’t like this one. Now and then you’ll come across a universe that follows a longer join path than necessary just because that is how it was designed. This is a great reason to ensure you understand when and where to use aggregate awareness, contexts, and shortcut joins. Just make sure all tables and views that are out there are needed in your query.
• Complex SQL – Some days you just find a complex SQL statement that is what it is. If performance is a must, consider condensing that SQL into a table via ETL or using concepts like materialized views to enhance performance.
• Date processing – Most queries to need to limit to a date range. Don’t convert your dates just for the sake of passing a simple string to the database. Leave the date as a date, and work with strings in your system date. In Oracle, for example, should perform better if it only plays with the strong bsaed on SYSDATE vs. changing the format for a date. Instead of: TO_CHAR(SOME_DATE,’YYYYMM’) = ‘201103’Consider…
  
  SOME_DATE BETWEEN ‘1-‘||TO_CHAR(SYSDATE,’MAR-11’)
  AND ’31-‘||TO_CHAR(SYSDATE,’MAR-11’)

Special Consideration for Xcelsius
Too often, you see Xcelsius misused as a reporting tool. “Dashboard”. Ensure that if the opportunity exists, use an aggregate layer for your dashboards to maximize performance. Unless you are using something awesome like BW Accelerated or the almost-on-our-doorstep HANA, don’t make Xcelsius chew through millions of rows on demand, keep it to less than a few thousand when possible.

I’m sure there is more to this and everybody has their own tools and tricks. Care to share?