Demolition Derby, Part 2: Making Order from Chaos

Earlier, I discussed the inclusion of Derby into the SAP BusinessObjects Business Intelligence 4.0 (BI4) platform as more of a philosophical debate (see related article, Demolition Derby: Part One).

Now I’d like to share my experience with getting Derby off my platform and moving my Monitoring Trend Database over to the Auditing Data Store, which in my case is Oracle 11g.

Let’s get started with “Demolition Derby“!

Detailed instructions are in the SAP BusinessObjects Platform Administrator Guide. What follows is a summary based on my experience, but the best details are in the administrators guide. If you’re using Oracle as your auditing/monitoring database, you’ll want to refer to SAP KB 1768676 for important additional details that do not appear in the administrators guide.

The first thing you’ll want to do is to create a backup of your Derby DB.

To do this, go into the CMC > Applications > Monitoring Application

1. Specify a location where you want the Derby backup to go.

2. Mash down on the “Now” button to make the CMC start the backup now.

3. Punch the “Export” button to create .csv data dumps you’ll need if you have data in Derby that you want to keep. It will drop 4 .csv files out in the following directory \SAP BusinessObjects Enterprise XI 4.0\Data\TrendingDB

4. Run the DDL for your chosen new platform to create the requisite tables. DDL’s are found in \SAP BusinessObjects Enterprise XI 4.0\Data\TrendingDB . Pick the one for your target platform, execute and commit. Four tables are created: MOT_MES_DETAILS, MOT_MES_METRICS, MOT_TREND_DATA, and MOT_TREND_DETAILS.

5. Go to \SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer\odbc

Alter the .sbo file for your new platform and add the needed Alias. See the Admin Guide for details.

Save it, close it.

6. Go to Applications, Monitoring Application, choose “Use Audit Database”

7. Restart APS with monitoring service.

8. If required, create an ODBC DSN to the Monitoring DB.

9. Open IDT, open Connections, edit Monitoring Trend DB connection, change driver to point to Audit DB. Save Close.

Here’s what the provided universe looks like. You’ll notice first off that it is a UNV and not a UNX . Second, the table names don’t match what is in the DDL you just used to create your new trend schema. Finally, there is a loop in the universe and no context to resolve it.

Rather than try to patch up this travesty, I just started from scratch with a new universe.

And for an added measure of awesomeness, you can link in your Auditing data because thankfully there were some CUIDs included in the Monitoring system that you can use to get server names and whatnot. Don’t be afraid to experiment here. Now that your data is all in the same data store, the world is your oyster.

If you want to see a list of your triggered alerts from the Auditing Data Store, this simple query will spit out the list. Let your creative juices run wild from here:

SELECT *

from ADS_EVENT

WHERE EVENT_ID in (

SELECT EVENT_ID

from ADS_EVENT_DETAIL

WHERE EVENT_DETAIL_TYPE_ID in (75, 92, 93)

)

So as you can see, this is a lot of effort on the part of the BI4 administrator to get things set to right. But now that you have made that effort, you have a monitoring operational data store that you can use for real-time reporting AND correlate with your Auditing Data Store. How ’bout them biscuits?

It is obvious- with all of the effort that went into Feature Pack 3 to add the ability to move your Monitoring Trend data into the Auditing Data Store- that someone at SAP really felt this was the right way to go. Derby is fine for small little developer apps, but, in my opinion, has no place in an Enterprise platform like BI4. For an application that is as memory-intensive as BI4 is, I still can’t understand why something that uses more memory, like Derby, was allowed to make the cut and end up in the platform. To me, I would want every bit of memory on the host to go to processing reports. Don’t get me wrong, monitoring is great and is a feature that is sorely needed. I’ll also admit that at this point, my argument is entirely speculative. I have not actually, specifically measured the memory consumption of the Derby DB used by the Monitoring engine in a BI4 system. I’m usually one to argue against “hunches”, but I really have a strong one on this topic. But I digress. The bottom line is that SAP didn’t put the ability to move Monitoring trend data into the ADS by accident. They did it because it is the right thing to do. I certainly recommend you go on your own “Demolition Derby” adventure.

In my final installment of this blog series, I’ll discuss my wish list for the Monitoring Application in BI4.1 (see related article, Demolition Derby: Part Three). Stay Tuned!