Skip to Content
Author's profile photo Saikat Das

Deep dive into the BI 4.x Monitoring Database data model


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

Monitoring Data Model in Apache Derby


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


Data Dictionary for Monitoring Database

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


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:

where t.schemaid = s.schemaid
and s.schemaname='APP';


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


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
  • 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


where td.TYPE=’Metric’;


  • List of watches

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


where td.CUID = w.CUID;


  • List of watches associated with metrics

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



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

order by w.NAME;


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


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





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 🙂

Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      Great piece of work..thanks alot

      Author's profile photo Former Member
      Former Member

      Helpful note

      Author's profile photo Former Member
      Former Member

      Why SAP Host Agent metrics are not saved in Monitoring Trend DB ?

      Author's profile photo Saikat Das
      Saikat Das
      Blog 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.

      Author's profile photo Joe Peters
      Joe Peters

      Is it possible to identify when watches are in a yellow or red state? 

      Author's profile photo Manikandan Elumalai
      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.



      Author's profile photo Joe Peters
      Joe Peters

      Thanks.  That's too bad -- seems it would be a basic function to report on the duration that a watch was in warning or danger status.

      Author's profile photo Saikat Das
      Saikat Das
      Blog 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).

      Author's profile photo Joe Peters
      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. 

      Author's profile photo Calvin Shipman
      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)


      Author's profile photo Manikandan Elumalai
      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.




      Author's profile photo Joe Peters
      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