SAP BusinessObjects Monitoring – Part 3

The Monitoring Application is a new application in SAP BusinessObjects 4.0. You will find the application in the CMC under Manage. This is the 3rd, and final, article in the series on Monitoring. In Part 1, we covered how to set up the application. Make sure you do this before trying to use the application. In Part 2, we covered how to use the application. Now, in Part 3, we will discuss the creation of the Monitoring universe, so you can track the health of the system over time.

By default, the monitoring trending data are stored in four tables in a Derby (Java) database. However, with SP04, we now have the option to transfer this data to the Audit database. This provides several advantages:

• The Derby tables will only store three months worth of data. The Audit DB tables will store as much data as you want.
• Connecting to the audit tables, to build a universe, is much easier than connecting to Java tables.

Therefore, it is recommended to migrate the data from the Derby tables, to the Audit database. Let’s talk about how to do that.

Migrating the Trending Data

The Trending database contains four tables:

• „MOT_MES_DETAILS
• „MOT_MES_METRICS
• „MOT_TREND_DATA
• „MOT_TREND_DETAILS

We’ll cover what’s in these tables later. Prior to migrating the trending data from the Derby database to the Audit database, these four tables need to be created in the Audit database. SAP provides the scripts to create the tables. They are on the BusinessObjects server in the following location:

<Install Dir>\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\Data\TrendingDB

Choose the script that matches your Audit database platform. Then, using a tool that can access the Audit database, log into the database with an account that has full control, and run the SQL script. It will create all four tables, as well as the needed indexes for those tables.

Next, you’ll need to export the data currently in the Derby tables, to CSV files. It’s pretty simple: Log into the CMC, and go to Applications –> Monitoring. Click on the button that says, “Export Data from Embedded database as CSV files”. This will output three CSV files, one for each table, to the following location:

<Install Dir>\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\Data\TrendingDB

Before you import the CSV files into the Audit database, you may need to turn IDENTITY_INSERT on for the four tables. The reason for this is that the tables have identity columns, with auto-generated values. Some database do not allow the insertion of values into those columns. To turn on IDENTITY_INSERT, run the following command against the database, for each of the four tables:

„SET IDENTITY_INSERT <TABLE NAME> ON

NOTE: IDENTITY_INSERT can only be turned on for one table at a time. You will need to turn it on for a table, then import the data to that table, then turn it off again before proceeding to the next table.

Once the data has been imported, make sure you turn IDENTITY_INSERT off again by running the following command:

„SET IDENTITY_INSERT <TABLE NAME> OFF

Proceed to import the CSV files, one at a time, into the Audit tables, using the appropriate tool for your database. It is recommended that the files be imported in the following order:

a. MOT_TREND_DETAILS
b. MOT_TREND_DATA
c. MOT_MES_DETAILS
d. MOT_MES_METRICS

Activating the Audit Database

The next step is to activate the Audit database for use with the Monitoring application. Start by editing the SBO file, adding the needed alias to it. You will find the SBO files in the following location on the server:

<Install_Dir>\dataAccess\connectionServer\odbc\<db Type>.sbo

Open the one that matches the Audit database platform that you are using. Here’s an example of what to add for SQL server:

<DataBase Active=”Yes” Name=”MS SQL Server 2008″>
<Libraries>
<Library>dbd_wmssql</Library>
<Library>dbd_mssql</Library>
</Libraries>
<Parameter Name=”Extensions”>sqlsrv2008,sqlsrv,odbc</Parameter>
<Parameter Name=”CharSet Table” Platform=”Unix”>datadirect</Parameter>
<Parameter Name=”Driver Name”>SQL (Server|Native Client)</Parameter>
<Parameter Name=”SSO Available” Platform=”MSWindows”>True</Parameter>
</DataBase>

Refer to the Data Access Guide for more information on editing the SBO file(s).

Finally, log into the CMC, and go to Applications –> Monitoring, and change the Trending database from Embedded to Audit. You will need to restart the Monitoring service after making this change. The Monitoring service is located in an Adaptive Processing Server.

OK. You are ready to start creating the Monitoring universe.

Creating the Monitoring Universe

BI4 doesn’t come with a universe for the Trending database. So, you’ll need to create one. Unfortunately, finding information on the schema, such as a data dictionary, can be very challenging. Here is the basic schema for the Trending tables:

Here’s a basic description of what is stored in each of these table:

• MOT_TREND_DETAILS: Dimension table; stores information about metrics, probes, and watches
• MOT_TREND_DATA: Fact table; collects trend data about metrics watches, and probes, including DateTime and value (By default, values are collected every 15 seconds)
• MOT_MES_DETAILS: Fact table; records information about threshold breaches and alert notifications
• MOT_MES_METRICS: Dimension table; stores information about Watches and the metrics that are part of each Watch

From these tables, following are the columns that you will most likely be interested in:

MOT_TREND_DETAILS

• MetricName: Obviously, this is simply the name of the metric. This will be a dimension object in the universe
• Type: There are three possible types of metrics: “Subscription”, “ManagedEntityStatus”, or “Probe”.
• Name: If the Type is “ManagedEntityStatus”, this will provide the name of the Watch. Otherwise, it will simply contain the same text as the Type, in caps.

MOT_TREND_DATA

• Time: This is the time at which the data was collected. It is a BIGINT or NUMBER or FIXED field.
• Value: This is the value of the metric at the time it was collected.
• MessageKey: If an error is thrown, this will contain the error message key. If it was successful, this will be null. For Watches, it can also contain either “watchEnabled” or
  “watchDisabled”.

MOT_MES_DETAILS

• Time: This is the time at which the data was collected. It is a BIGINT or NUMBER field.
• AlertType: This represents the notification delivery type. It is a SMALLINT or NUMBER data type.

MOT_MES_METRICS

• Name: This is the name of the watch.

As you can see, the universe will be fairly simple. Of course, you may want to create some filter objects, so that you can do reporting based on specific historical periods, such as current month, current year, etc.

I hope you have found this series as useful as I have, in researching it and learning it all. Let me know if you learn anything that I haven’t covered here.