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


This blog is an implementation of the official SAP documentation : http://help.sap.com/businessobject/product_guides/sbo41/en/sbo41sp4_bip_admin_en.pdf


1. Introduction


The Monitoring Application is a new application in SAP BusinessObjects 4.x. You will find the application in the CMC under Manage. By default, the monitoring trending data are stored in four tables in a Derby (Java) database and we have the option to transfer this data to the Audit database.

If you want to use the auditing database for your monitoring data, and you have existing data in your Derby trending database, you will need to migrate the Derby database to the auditing 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.

2. Trending database schema


The following Trending database diagram and table explanations show you the tables where the metric, probe, and watch data will be recorded and how these tables are related.


3. Trending database tables


  • MOT_MES_DETAILS: This table records the information about subscription breaches and alert delivery information. For example, breach time and alert delivery time.
Column_NameTypeKeyDescription

StatusDetailsId

INTEGER

Primary Key

Autogenerated

DetailsIdINTEGERForeign key (from MOT_TREND_DETAILS)

Time

BIGINT or NUMBER

Unix Epoch date

NATime at which data was collected
AlertTypeSMALLINT or NUMBERNASubscription notification delivery type (for example, email)

  • MOT_MES_METRICS: This table records information about watches and the metrics belonging to the watch equations. Every metric belonging to the watch will have one entry in this table.
Column_NameTypeKeyDescription

DataId

INTEGER

Primary Key

Autogenerated

DetailsIdINTEGERForeign key (from MOT_TREND_DETAILS)
CUIDVARCHAR(64)NACUID of the watch
NameVARCHAR(255)NAName of the watch

  • MOT_TREND_DATA: This table records the trending data from metrics, watches, and probes. For example, metric value and time.
Column_NameTypeKeyDescription

DataId

INTEGER

PrimaryKey

Autogenerated

DetailsIdINTEGERForeign key (from MOT_TREND_DETAILS)
Time or TimeT

BIGINT or NUMBER or FIXED

Unix Epoch date

NATime at which data was collected
ValueFLOAT or DOUBLE or NUMBERNAValue of the metric / subscription
MessageKeyVARCHAR(32)NAError message key or null if successful. For Watch, it can also be either "watchEnabled" or "watchDisabled". It is a "key" because it is ultimately used to fetch localized messages before displaying the UI.
TsDATETIME or TIMESTAMPNA

Time at which data is written to the databaseMOT

  • MOT_TREND_DETAILS: This table records information about managed entities, probes, and watches. For example, CUID and metric names.
Column_NameTypeKeyDescription

DetailsId

INTEGER

PrimaryKey

Autogenerated

CUIDVARCHAR(64)NACUID of the InfoObject that exposes the metric or is related to the metric
MetricNameVARCHAR(255)NAName of the Metric
TypeVARCHAR(32)NA

One of "Subscription", "ManagedEntityStatus", or "Probe"

NameVARCHAR(255)NA

Name of the watch when the type is "ManagedEntityStatus". Otherwise, default to the same string as in Type, except in all capital letters; for example, "PROBE" or "SUBSCRIPTION".

Therefore, it is recommended to migrate the data from the Derby tables, to the Audit database. Let’s talk about how to do that.




4. Migrating the Trending Data


Before you start migrating your data, verify these prerequisites:

  • The auditing database is working, and auditing is running properly.
  • You have sufficient authorizations and database client applications on the target database to create new tables, import CSV dumps, and so on.
  • The auditing database supports the import of comma-separated values (CSV) files.


    4.1 To export the data into CSV files


This section explains how to generate the CSV dump files required for migration. The CSV files contain comma-separated values of the embedded Derby database data content.

  • In the Manage area on the CMC home page, click Applications.
  • Double-click Monitoring Application to open the properties page.
  • In the Trending Database Settings area, beside Export Data from Embedded database as CSV files, click Export.

The following four CSV files are generated in the default Trending Database location, which is:

<BOE_Install_Dir>\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0/Data/TrendingDB



   

      4.2 To create the monitoring tables in the MS SQL Server Audit DB


Follow these steps to prepare the target auditing database:

After installing the BI platform, DDLs related to all the supported CMS auditing databases are available in the <Install Dir>\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\Data\TrendingDB location. You will find seven different (.sql extension) files with the respective database name.

Go to the MS SQL Audit DB (in this case, the target database is the database where CMS auditing has been configured) and run the .sql file. The following four Monitoring tables are created: MOT_TREND_DETAILS, MOT_TREND_DATA, MOT_MES_DETAILS, and MOT_MES_METRICS. The required indexes are also created, along with the tables.

If all the tables are created with correct data types as mentioned in the .sql file, the database schema required for the Monitoring application is created.

    4.3 To restore contents to the target database


The following steps need to be performed in order to restore the content to the target database:


  • Enable Identity Insert

The Monitoring tables contain a number of IDENTITY columns. These are columns that auto-generate their values. MS SQL Server do not allow explicit insertion of values to these columns.

During data migration, even these identity column values need to be migrated however. Users therefore have to enable the explicit insertion of these values using the following SQL command:

SET IDENTITY_INSERT <TABLE NAME> ON

  • Import the CSV dump file to the target table

All software provided by database clients enables users to import the data from CSV to the table using either a menu option or a command. The user needs to use this option to import the data from the CSV file to the corresponding table. Import the data files into the new tables in the following order:


    • MOT_TREND_DETAILS
    • MOT_TREND_DATA
    • MOT_MES_DETAILS
    • MOT_MES_METRICS

  • Disable Identity Insert

Once the data has been imported, the user needs to disable the identity insert on the table using the following SQL command:

SET IDENTITY_INSERT<TABLE NAME> OFF


Users have to disable the identity insert on a table after the data import in order to enable the identity insert on the next table. This is because the identity insert operation can be enabled on only one table at a time.

MSSQLServer scripts list :

4.4 To configuring SBO files

Internally, the Monitoring application uses Connection Server libraries, and the SBO configuration is required for the Connection Server to establish connectivity to the database driver. You need to specify the database driver and its location in the SBO file to establish this connectivity.

The connection name field configured in the CMC Auditing page is an ODBC DSN, so the driver should be configured in: <Install_Dir>\dataAccess\connectionServer\odbc\sqlsrv.sbo

Typically, the ODBC libraries are already configured in the SBO files and you just need to add the alias names. If this is not the case, follow this example to perform the configuration in the SBO file:

4.5 To switch to the auditing database

Switch the database so that Monitoring trending information will be stored in the auditing database:

  • In the Manage area on the CMC home page, click Applications.
  • Double-click Monitoring Application to open the properties page.
  • In the Trending Database Settings area, select Use Audit Database.

Restart your Monitoring APS.

4.6 To check the data entered into the audit DB


Here is a sample query to see if your monitoring entries are added:

11 Comments
Labels in this area