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:
Schema
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.

Advertisements

15 Responses to SAP BusinessObjects Monitoring – Part 3

  1. Vikram says:

    Thanks Michael for the wonderful post. I was eagerly waiting for help on this topic. This is exactly what I wanted.

    Cheers,
    Vikram

  2. Dallas Marks says:

    Michael,

    Thanks for this helpful article. I found this SAP Note 1805495 which was just released on January 18, 2013. My guess is we’ll see it in the appendix of the SP6 edition of the BI4 admin guide.

    http://service.sap.com/sap/support/notes/1805495

  3. Balram Komati says:

    This is an excellent topic that you provided us. Thank you.
    Can you please provide how to change .sbo file for oracle 11g database? becuase I am uisng oracle 11g DB for monitoring as well as auditing BI4.0 SP4 Patch 2. I have created Monitoring tables in Auditing DB. I tried different ways, I am still unable to activate monitoring with Oracle 11g Audit db. Thanks in advance.

  4. john ripka says:

    Very interesting : What about reinitializing the monitoring database. Do you know the procedure ? Noticed that when reinitialising the cms database using the ccm, the monitoring coul not work any more…

    • Hi, John. I haven’t tried that. But it makes sense, since the connection information is stored in the CMS database. Try going into the CMC and setting the Monitoring application to point to the Audit database again.

  5. Gabe Mensching says:

    This is great information. Thank you. I have a questions about watches. When I split out my APS servers creating new ones the health is not being reported on them. The watch is in a failed state. Is there config that needs to take place to get the health to be reported?

  6. Nag Chevula says:

    Can we just create trending database without migrating the data? I just set up the trending database but I don;t see any values in the table ( Is it because I didn’t migrate anything into tables?).

  7. Roberto says:

    I am in love with this post!

  8. Patrick Delage says:

    Hello Michael’s,

    I’m in the process to map these tables to universe, and later to Explorer (I fix the issue of JDBC oracle…). I have two simple questions. How do we link dataid columns, so far I didn’t find perfectly.

    Second question , is the “value” column have the metric value or the watch value. I mean, If I watch trend for CPU by example, hope I will see CPU value every minutes like 0,23, 0,45 etc… And not see watch value which could be 0 or 1 depending of the threshold ?

    Hope I am clear !

    Thanks in advance.

  9. Patrick Delage says:

    Oh! Maybe I just answer my second question. Well, I just realize that “CPU Usage _%_” is actually 0 or 1. Don’t know what it mean but it’s like this in CMC too. It the column “CPU Usage Percentage _last 5 Minutes_” which have decimal valu which are probably the pourcentage of usge of CPU’s..

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: