Technology Blogs by Members
Explore a vibrant mix of technical expertise, industry insights, and tech buzz in member blogs covering SAP products, technology, and events. Get in the mix!
cancel
Showing results for 
Search instead for 
Did you mean: 
Former Member

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


Description of tables in Monitoring Database

Table NameDescription
TREND_DETAILSThe table records
information about metrics, probes and managed entities
TREND_DATAThe table records
information on the metric values, timestamp (epoch time in milliseconds) when data was collected and error message key
MANAGED_ENTITY_STATUS_DETAILSThis 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_METRICSThis 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:


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.

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 :smile:

12 Comments
Labels in this area