SAP BusinessObjects Monitoring – Part 3
January 10, 2013 15 Comments
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:
b. MOT_TREND_DATA
c. MOT_MES_DETAILS
d. MOT_MES_METRICS
<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>
- 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
- 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.
Thanks Michael for the wonderful post. I was eagerly waiting for help on this topic. This is exactly what I wanted.
Cheers,
Vikram
My pleasure, Vikram. Thanks for your kind words.
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
Thanks, Dallas. They’re only two years late in publishing that. LOL.
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.
Balram;
Thank you. I don’t have the exact steps with me. I would suggest checking the Data Access Guide. It has exact steps there.
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.
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?
Hi, Gabe. If you have created new APS servers, with different names as the default APS, you will need to edit the existing Watch to monitor the new APS servers.
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?).
Yes, if you don’t need the historical data, you don’t need to migrate anything.
I am in love with this post!
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.
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..