Those of you familiar with the BusinessObjects Auditor database and standard reports know that it can be a wealth of information about what is going on inside your BOBJ environment. It's great for telling who is logging on, what reports are being used, and which services in your cluster are working the hardest.
What's not so apparent is what all of this activity is doing to your infrastructure. What does 1000 report refreshes equate to in terms of CPU and Memory Usage? I got the idea for a correlated report from the 2008 SAP BusinessObjects User Conference (This year's conference is open for registration now) in a presentation by Meredith McLarty from Quorum Business Solutions (Also home to the estimable Dallas Marks). Meredith's presentation at that SBOUC was all about the Auditor database and cool things to do with it. While it has been a while since then, I decided to give this idea a try for myself.
First Things First
The first thing I had to get was performance data from our production servers. At SEI, we use a tool called TeamQuest to monitor our Solaris servers. TeamQuest, of course, has its own embedded reporting tools which are adequate, but nowhere near the ability of what BOBJ can do. So I talked to the folks that administer our TQ application to see if it was possible to extract data from it. After a little research I found out that I could not connect directly to the TQ database, but that TQ has the ability to export data to a flat file. I asked for a monthly dump of TQ data which gave CPU and Memory usage statistics hourly to be exported to a .CSV file.
Which Reporting Tool to Use?
The fact that I had flat files to work with instead of a direct database connection made this decision pretty easy. I decided to use Webi Rich Client since it has the ability to use Personal Data Files. These are a data analyst's best friend. It enables you to pull in data from Excel or a .CSV and make them usable Objects in your report.
Merged Dimensions are your Friend!
In order to make all of the data play nicely together, I either needed to get to work on some fancy ETL and start manipulating things all over the place, or I needed to figure out some Merged Dimensions. I chose the latter. I figured Date was a good place to start. I took inventory of where I was. I had 3 queries to work with. The first from BOBJ Auditor. The following 2 were personal data files, one from each node of my 2-node production BOBJ Cluster. So Date seemed to be a natural start. But, of course, the dates were all in different formats. I tried for a couple of hours to do some date transforms on the date dimensions until I realized I couldn't do a Merged Dimension using a variable. (You expert Webi developers will have to cut me some slack. I've been out of the Dev game for a few years now) So back to the flat files I went. I used Excel to reformat the dates into the same date model as the Auditor date, which was 'YYYY-MM-DD'. I refreshed my queries, merged my dimensions up and all was right with the world for a bit.
Putting the Pieces Together
A couple of measures later, I had a workable correlated data set tied together by day. I tossed them togehter in a Vertical Bar and Line graph just to see what it looked like. A few tweaks, a couple of resizes, and this is what I ended up with:
The BOBJ Auditor Actions are in represented in the Blue Bars. Yellow Line is Node 1 CPU. Green Line is Node 2 CPU. Orange Line is Node 1 Memory. Maroon line is Node 2 Memory. All of the Infrastructure data is expressed as a percentage used of the total available.
Of course, I wanted to see what each of those types of actions was. So I created a Details report as well, and broke out the actions by hour of the day. Here is a portion of that report:
I put a subtotal "Hourly Total" at the bottom of each section to try and give a little idea of what is going on in the system.
What's Next?
While I'm fairly happy with the result of this, it's not all that I want it to be. I can see a couple of months of tweaking before I make some moves to "productionize" it. I need to go back in and build a set of Merged Dimensions on Hour, then I'd like to build another Vertical Bar and Line graph with Day as a section, detailing usage on each hour througout the day. This would give me a better idea of how the system is being used during which hours of the day.
After that, I need to think about automating the data extract from TeamQuest, using a little ETL and putting the data into a database somewhere. This way I can just put a Webi report on a schedule to run each month without the need for me to manually intervene.
Once the whole process is automated, then I can put a hyperlink with an OpenDoc call on our SharePoint site so all of our department managers can use it.
Come and Find Your Next Great Idea at SBOUC!
The 2010 ASUG SAP BusinessObjects User Conference is October 5th - 7th in Orlando Florida at the Disney World Swan & Dolphin Hotels. There will be a great cast of experts there to help you find your next great idea, me included. I was just notified today that I was selected to speak at the event. I hope to see you there!
If you would like a copy of my report to use for yourself, I would be happy to share it with you. Just shoot me an e-mail. Contact details are in my Business Card.
3 Comments
