Skip to Content

Overview:

Monitoring is an out of the box solution in BI 4.x, to display the live server metrics exposed via BOE SDK on CMC. ‘Monitoring Service’ (part of APS container) captures the monitoring data and passes it on to the Monitoring Application within CMC. Monitoring application extends the functionality of default server metrics to configure watches, custom metrics, alerts, KPIs and probes.
Server metrics are collected for individual Process IDs (PIDs) of each BOE service type. Essentially the metrics visible in ‘Servers’ menu of CMC –>Service Categories –> Right click on a <server name> –> select ‘Metrics’, is same as what is visible in ‘Monitoring’ menu –> Metrics –> Servers –> Expand a specific server. Example screenshots given below:
ServerMetrics_Servers.JPG
ServerMetrics_Monitoring.JPG
Monitoring or Trending database comes into play, if the option is selected on a specific watch to ‘write to trending database’. Unless the trending database is used, historical trend of monitoring data will not be available.
WatchEdit_Settings.JPG
Monitoring data is relevant from an administration perspective to keep a track on the health of the BOE system and get automated alerts when the configured caution or danger threshold is breached. Reporting can be done on the Monitoring database using the default ‘Monitoring TrendData Universe.unv’ universe provided with BI 4.x installation or a custom universe can be built.
The starting points of understanding how monitoring works and how it is configured, refer to the relevant chapter in the BI Platform admin guide, downloadable at: http://help.sap.com/boall_en/. E.g. In ‘sbo41sp3_bip_admin_en.pdf’, chapters 20, 31 and 34 talk about monitoring and metrics. There are also several insightful blog posts on monitoring e.g. by ‘Manikandan Elumalai’ and ‘Toby Johnston’ on SCN. Any SQL examples shown in this blog post are based on trending database hosted in Apache Derby. However, the same can easily be adapted to any other query language syntax, as the table structures remain same.

Choice of Monitoring (Trending) database:

Two choices are offered in terms of monitoring database in BI 4.x:
  • Using the embedded java database requiring minimal administration: Apache Derby (installed along with BI 4.x)
  • Re-using the Audit data store for storing monitoring data
These options can be set in the properties of ‘Monitoring Application’ in the ‘Application’ menu of CMC. If the retention duration of monitoring data is few hours or until it reaches few GBs of file space, it is best to use Apache Derby. For longer retention and handling large volume of data, using audit data store is advisable. The default ‘Monitoring TrendData Universe.unv’ is based on trending database hosted in Derby. Steps for migrating from Derby to Audit Data Store are described in BI Platform Admin guide.

Connecting to Monitoring database (Apache Derby) with SQuirrel Client

The best way to analyze monitoring database hosted in Apache Derby, is to use a GUI based database client like SQuirrel. Derby natively provides command line sql client tool: ‘ij’. Steps for installing SQuirrel and Derby client is described in:
For connecting SQuirrel client with Monitoring database in Derby, following should be used for defining the alias:
Driver: Apache Embedded
URL: jdbc:derby:\\<FQDN for the remote server>\TrendingDB\Derby;create=false
                     
Blue Underline Font: Alias URL (Path) for the Monitoring Database
**Note:
  • Trending DB is installed in BI 4.x in the following location:
         <drive>:\<Parent directory of BI 4.x>\SAP BusinessObjects Enterprise XI 4.0/Data/TrendingDB/Derby
         **Derby: Name of the Monitoring / Trending Database)
  • To shorten the path for defining Alias URL in SQuirrel, the path ‘<drive>:\<Install path of BI 4.x>\SAP BusinessObjects Enterprise  XI 4.0/Data/TrendingDB’ can be shared with the network user who will be accessing it remotely via SQuirrel client.
  • The path ‘<drive>:\<Install path of BI 4.x>\SAP BusinessObjects Enterprise XI 4.0/Data/TrendingDB’ also contains DDL for table creation for other database platforms like Oracle, SQL Server, DB2 etc.

Monitoring Data Model

The table names vary if the trending database is implemented in Derby vs. Audit data store. However the table structures are identical. Refer screenshots
below:

Monitoring Data Model in Apache Derby

MonitoringDataModel_Derby.jpg

Description of tables in Monitoring Database

Table Name Description
TREND_DETAILS The table records
information about metrics, probes and managed entities
TREND_DATA The table records
information on the metric values, timestamp (epoch time in milliseconds) when data was collected and error message key
MANAGED_ENTITY_STATUS_DETAILS This table contains information of configured thresholds (caution & danger) – subscription
breaches and alerts. Subscription check timestamp (epoch time in milliseconds) is also stored
MANAGED_ENTITY_STATUS_METRICS This is a lookup table for watches

Monitoring Data Model in Audit Data Store

MonitoringDataModel_ADS.jpg

Data Dictionary for Monitoring Database

For analyzing data dictionary in SQuirrel client, the create table scripts can be generated along with all constraints / indexes:

Generate_DDL_Derby.JPG

Refer to the attached file ‘create_table_trendingdb_derby.sql‘ for the generated DDL.

Alternatively following queries can be used to extract the data dictionary:


select t.TABLENAME, t.TABLETYPE, s.SCHEMANAME
from SYS.SYSTABLES t, SYS.SYSSCHEMAS s
where t.schemaid = s.schemaid
and s.schemaname='APP';







----

select t.TABLENAME, c.CONSTRAINTNAME, c.TYPE, s.SCHEMANAME
from SYS.SYSCONSTRAINTS c, SYS.SYSTABLES t, SYS.SYSSCHEMAS s
where c.schemaid = s.schemaid
and c.tableid = t.tableid
and s.schemaname='APP';








---

select s.SCHEMANAME, t.TABLENAME, g.conglomeratename, g.isindex, g.isconstraint
from SYS.SYSTABLES t, SYS.SYSSCHEMAS s, SYS.SYSCONGLOMERATES g
where g.schemaid = s.schemaid
and g.tableid = t.tableid
and s.schemaname='APP'
and (g.isindex = 'true' or g.isconstraint='true')
order by t.TABLENAME;








---

**Note: Default row limit in SQuirrel client is 100. This limit is configurable or the setting can be turned off altogether (no limits). The setting is present in the
SQuirrel client on the SQL tab towards top right.

Rowlimit_SQuirrel.jpg

A clear trend which comes up based on the output of the above queries / script:

  • Only tables, indexes and constraints are present in monitoring database. No views, procedures, materialized views etc. exists
  • Auto-generated sequence keys are used as Primary Keys for all the four tables
  • Enforced referential integrity i.e. PK-FK relationship exists between
    • TREND_DETAILS (PK) and MANAGED_ENTITY_STATUS_DETAILS (FK)
    • TREND_DETAILS (PK) and TREND_DATA (FK)
  • Index type is either unique or non-unique
  • Timestamp is stored in BIGINT format (epoch time) in TREND_DATA and MANAGED_ENTITY_STATUS_DETAILS table

Building Monitoring Report Queries

Some common monitoring reporting scenarios are listed below:

Example scenarios:

  • List of different metrics available in the BOE system:

select distinct td.METRICNAME, td.TYPE

from TREND_DETAILS td

where td.TYPE=’Metric’;

——

  • List of watches

select distinct w.CUID, w.NAME, td.METRICNAME, td.TYPE

from TREND_DETAILS td, MANAGED_ENTITY_STATUS_METRICS w

where td.CUID = w.CUID;

—-

  • List of watches associated with metrics

select distinct w.NAME, td.METRICNAME, td.TYPE

from TREND_DETAILS td, MANAGED_ENTITY_STATUS_METRICS w

where td.DETAILSID = w.DETAILSID

–and td.TYPE=’Metric’ –Optional filter

order by w.NAME;

—-

  • Trend values of metrics for a specific watch since 09-Feb-2015

select w.NAME, td.METRICNAME, t.MESSAGEKEY, t.TIME,

{fn TIMESTAMPADD( SQL_TSI_SECOND, t.TIME/1000, timestamp(‘1970-01-01-00.00.00.000000’))} UTC ,

t.VALUE

from TREND_DETAILS td, TREND_DATA t, MANAGED_ENTITY_STATUS_METRICS w

where td.DETAILSID = t.DETAILSID

and td.DETAILSID = w.DETAILSID

and w.NAME='<Node>. InputFileRepository Watch’  —This is an example

and t.TIME >= 1423440000000; —equivalent epoch time in milliseconds for 09-Feb-2015 00:00:00 UTC

**The above query converts epoch time to regular time in UTC.

Concluding Remarks

The above write-up is not an exhaustive reference on monitoring database or monitoring functionality. The readers are encouraged to validate the above contents in line with standard BI Platform admin guide. Comments are welcome to further enhance the contents of this blog post. Thanks for your time 🙂

To report this post you need to login first.

12 Comments

You must be Logged on to comment or reply to a post.

    1. Saikat Das Post author

      As per the BI Platform Administration guide, it should be possible to integrate monitoring application with SAP Solution Manager, IBM Tivoli, Wily etc. However, it is best to use fit for purpose monitoring solution offered by SAP. If you are looking for day to day health check monitoring of BOE, then default monitoring application in CMC and trending db should solve the purpose. For detailed troubleshooting, E2E monitoring of SAP ecosystem – solman and its integration with wily metrics are best suited.

      (0) 
    1. Manikandan Elumalai

      Based on the current system metrics, you cannot simply capture the exact time when the watch status changes.There is a polling mechanisms which periodically polls the status of your object of interest in your watch and gets the snapshot of the objects current status and updates the same in monitoring application.

      Thanks

      Mani

      (0) 
    2. Saikat Das Post author

      If the watch metrics are being stored in trending database (configured in settings), then the alert messages (for caution or danger thresholds) should get stored in MANAGED_ENTITY_STATUS_DETAILS and trend data of metrics included in the watch should get stored in TREND_DATA. Timestamp should also get captured. However as Manikandan indicated, it will be based on polling mechanism and exact time may not get captured. However if throttling criteria is lesser than the polling interval, chances of capturing a alert scenario is higher in trending database. Keeping the throttling criteria too low also has its disadvantages of creating false alerts (or threshold breaches lasting for very small interval).

      (0) 
      1. Joe Peters

        Thank you, Saikat.

        That would tell me when alerts are fired, but I’m looking for the start and end of the period when the watch was in caution or danger status.

        I have the raw readings, as you mentioned, but I don’t see any way of determining if a particular reading was within the caution or danger range.

        Ultimately, I’m trying to get to a total availability metric.  So I want to get the percentage of readings that were normal, caution, or danger. 

        (0) 
  1. Calvin Shipman

    We have seen the Monitoring database running on the Audit space consume the table space and fill the trending.  Auditing has a function to limit the # of days of retention.  IS there a way to do this in the app or code, without creating a script to do it on a periodic basis to match auditing retention?

    What is the best method for truncating the audit and monitoring tables  for 12 month data retention?

    (suggested script would help) I’m not a DBA so I have to hunt and peck these solutions.  (we are on Oracle)

     

    (0) 
    1. Manikandan Elumalai

      It depends on your organisational usage pattern. In general we will archive the data from Audit db and  push it an archival database with a universe created on top of it which might be used only when you really need it.

       

      Thanks

      Mani

      (0) 
    2. Joe Peters

      If you’re only interested in pruning and not archiving, then the following will do it:

      delete from mot_trend_data where ts < sysdate - 365
      (0) 

Leave a Reply