Our Technical Operations team provides front-line support for our production systems 24x7x365. They’re the ones that monitor everything and receive alerts when things go wrong. A couple of weeks ago, the Tech Ops manager came to me with a question. She wanted to know if I could tell her when she could have her team kill a report if it was running too long. Quasi-unfairly, I answered her with another question. I asked her what a normal run time was? Once we thought about it for a bit, we realized that the answer was going to be a bit more complex than we had initially thought. We have hundreds of reports, and each one is different. Different queries, different Universes, different sets of data, different intervals of refresh, etc. This was going to require a report.
Overall Report Goal
The over-arching goal of this report was to provide the Ops team with a snapshot of what “normal” is for every report in our BusinessObjects Enterprise production environment. As we discussed requirements, the Ops Manager stated that the report should contain summary statistics for each report, such as Average, Minimum, and Maximum run times. She also wanted to see the last 10 actual refresh times for every report. Auditor to the rescue!
Summary first – Low Hanging Fruit
I set down to work and figured that the summary part would be the easiest. I pulled out the good old Activity Universe and within 30 minutes had a working draft summary page worked out. Each report name is listed, with the Average, Min, and Max runtimes. I also put on that same table the last actual runtime, and a count of the number of refreshes from the time period specified. The end product looks like this:
This gives the operator a pretty good snapshot of how each report performs. And yes, that is a spraycan effect from Paint and not some cool Webi ninja trick I know how to do.
Now for the Hard Part – The Last 10 runs
I puzzled around a while trying to figure out how to get the last 10 refreshes on the report. First I just tried putting all of the refreshes in a table, created a section on Report Name, and tried applying a report Ranking on the Action Time (date and time). That went over about as well as expected, and crashed Webi after it tried to think about it for 45 minutes. Next I tried to play a trick on Webi. I put that same table together with all of the report refreshes, section on Report Name, and then created a new Measure variable using the =LineNumber() function. I added that Measure to a column on the end, and it had the desired effect. It listed the number of the row it was on. It always started at 2, but each line down it increased by 1. I thought I was in the clear. I applied the ranking to the Line Number column, but Webi wasn’t buying it. It just didn’t work. No error, but it did not restrict any data from the table whatsoever.
I scratched my head a little while longer then started bugging some folks for some info. Each ended up giving me a piece of the puzzle, but ultimately the kicker came from one of our Universe developers from the Development team upstairs.
Here’s the solution we came up with. We went down to the good old Universe, and created a Derived Table. We gave it the name “Ranked Refreshes”, and created an extra column called Rank Number which really was this in the SELECT:
DENSE_RANK() OVER (PARTITION BY DERIVED_DOCUMENT_NAME.Detail_Text ORDER BY AUDIT_EVENT.Start_Timestamp) RANK_NUMBER
That forced the Database do to the ranking for me. From there we were almost home free. I then created a predefined filter in the Universe called “Top 10” and it’s WHERE clause specified that RANK_NUMBER had to be less than or equal to 10.
I added a new query to my report from the Derived Table, added in the Filter, did a quick merge of my dimensions between the 2 queries, and this is what I got:
I also added the User Name and the Universe Name on this report as well to help the operators identify who might be having run time issues.
So after a couple days worth of work, and bugging a handful of ASUG members for ideas, the result has ended up being a useful tool for the Operations team already. Now they know what “normal” is for each report and when something is running in anomaly and can be terminated with extreme prejudice.
If you’d like a copy of it for yourself, please shoot me an e-mail and I’ll be happy to send you a purged, unprotected .wid file and the query I used to create the derived table in the Activity Universe.