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:
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
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) |
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 |
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 |
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:
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.
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:
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
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:
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:
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:
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
6 | |
6 | |
5 | |
4 | |
3 | |
3 | |
3 | |
3 | |
3 | |
2 |