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 : 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_Name | Type | Key | Description |
|---|---|---|---|
|
StatusDetailsId |
INTEGER |
Primary Key Autogenerated |
|
| DetailsId | INTEGER | Foreign key (from MOT_TREND_DETAILS) | |
|
Time |
BIGINT or NUMBER 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 |
|---|---|---|---|
|
DataId |
INTEGER |
Primary Key Autogenerated |
|
| 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 |
|---|---|---|---|
|
DataId |
INTEGER |
PrimaryKey Autogenerated |
|
| DetailsId | INTEGER | Foreign key (from MOT_TREND_DETAILS) | |
| Time or TimeT |
BIGINT or NUMBER or FIXED 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. |
| Ts | DATETIME or TIMESTAMP | NA |
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 |
|---|---|---|---|
|
DetailsId |
INTEGER |
PrimaryKey Autogenerated |
|
| 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 |
| Type | VARCHAR(32) | NA |
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.
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.
 |
 |
 |
 |
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:
I had published something very similar
How to Move / Migrate Derby Monitoring Db to Auditing Db
Ajay
I've not seen it and my blog is more detailled. Moreover, this blog is specific to MS SQL Server 2008. Your comment is not appropriate.
Regards
PL
The combination of knowledge gained by the TWO articles will befit the customer. My comment was not to be negative in any way.
Ajay
Sorry but I find the term "very" pretty negative.
PL
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
Mohan
Good blog MAURIN Pierre-Louis
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.
Thanks
Mani
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.
Regards.
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.
Thanks
Mani