Skip to Content

How to migrate the Trending Database from Apache Derby to MS SQL Server 2008 (ODBC)

This blog is an implementation of the official SAP documentation :

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.

19-10-2014 16-50-33.png

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_Name Type Key Description



Primary Key


DetailsId INTEGER Foreign key (from MOT_TREND_DETAILS)



Unix Epoch date

NA Time at which data was collected
AlertType SMALLINT or NUMBER NA Subscription 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_Name Type Key Description



Primary Key


DetailsId INTEGER Foreign key (from MOT_TREND_DETAILS)
CUID VARCHAR(64) NA CUID of the watch
Name VARCHAR(255) NA Name of the watch
  • MOT_TREND_DATA: This table records the trending data from metrics, watches, and probes. For example, metric value and time.
Column_Name Type Key Description





DetailsId INTEGER Foreign key (from MOT_TREND_DETAILS)
Time or TimeT


Unix Epoch date

NA Time at which data was collected
Value FLOAT or DOUBLE or NUMBER NA Value of the metric / subscription
MessageKey VARCHAR(32) NA Error 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.

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_Name Type Key Description





CUID VARCHAR(64) NA CUID of the InfoObject that exposes the metric or is related to the metric
MetricName VARCHAR(255) NA Name of the Metric

One of “Subscription”, “ManagedEntityStatus”, or “Probe”

Name VARCHAR(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.

19-10-2014 16-49-28.png

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

19-10-2014 17-42-54.png


      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.

19-10-2014 17-48-06.png

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.

19-10-2014 18-01-58.png

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.

19-10-2014 18-09-37.png

    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:


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

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


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 :
19-10-2014 18-59-19.png
19-10-2014 19-08-27.png
19-10-2014 19-16-35.png
19-10-2014 19-20-45.png

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:

19-10-2014 20-04-20.png

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.

19-10-2014 19-29-48.png

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:

19-10-2014 19-57-23.png

You must be Logged on to comment or reply to a post.
  • Have you come across any limitation with the database password when switching over the monitoring database to use Auditing database? Our password has all these special characters &%*#$ which is causing the switch over an issue. We are currently on BO 4.1 SP2 Patch 5


  • hi, its very helpfull, thanks for sharing, and i have a question how can I log in to the default database to check the watch health status?  Because I just find some data under DATA\TrendingDB, but I donot know how to connect to this database. Can you help? Thanks very much.

  • Hi All,
    After the data is migrated to the auditing database, does the auditing data management settings in the CMC also apply here? So if for example in the auditing section of the CMC,  you had  the setting to delete data after 100 days, would that be applied here as well? Or do you have to manage the data contained in these fields via a custom method such as a stored procedure or job on the database? I was just wondering how the data would then be managed.

    Thanks for any information provided.

    • Good question Michael.

      If we capture the monitoring trend as well along with the Audit events, It should not delete the Audit detail table content based on event_date. I also assume it in the same way as If we combine the Commentary in Audit db it should not remove the comments on a particular report as we may required to keep the commentary independent of the time.


      • Hi Mani,

        Sorry, but I don't understand. Are you saying the auditing parameters will not affect the trending data when it is added to the auditing database?

        Thanks in advance for the clarification.


        • Per my Observation whenever the retention period is applied on the Audit data it always removes the rows from the Audit_Detail table leaving all other Dimension tables. If you configure the monitoring in Audit DB the scenario would be same as Monitoring data is captured in MOT_* tables.