You have two options:

Options 1: (Exiting BI 4.0 w/ SP4+ Install, migrate Derby Database)

If you have existing data in your Derby trending database, you will need to migrate the Derby database to the auditing database, and then configure the BI platform to record monitoring information in the auditing database. These are the high-level steps you’ll need to follow. For details, see the related topics.

Migrate the Derby database.
Verify that the auditing database is working, and auditing is running properly.
Create the monitoring tables in the ADS (optional).
Configure the SBO files and add alias names.
Switch to the auditing database.
Restart the Adaptive Processing Server that hosts the Monitoring Service.
On the Monitoring Dashboard, ensure that everything works as expected. Verify that these monitoring tables have been created in the database:
- MOT_MES_DETAILS
- MOT_MES_METRICS
- MOT_TREND_DATA
- MOT_TREND_DETAILS
Restore Derby Database

Option 2: (New BI 4.0 w/ SP4 Install, No Data in Derby Database)

If you do not have data in your trending database, that is, you have a fresh installation, you do not need to migrate the database; you only need to configure the BI platform to record monitoring information in the auditing database. These are the high-level steps you’ll need to follow.

Verify that the auditing database is working, and auditing is running properly.
Create the monitoring tables in the ADS (optional).
Configure the SBO files and add alias names.
Switch to the auditing database.
Restart the Adaptive Processing Server that hosts the Monitoring Service.
On the Monitoring Dashboard, ensure that everything works as expected. Verify that these monitoring tables have been created in the database:
- MOT_MES_DETAILS
- MOT_MES_METRICS
- MOT_TREND_DATA
- MOT_TREND_DETAILS

Step 1: Migrate existing Derby Database

Logon into CMC > Application > Monitoring Application Properties > Export

For best results Export Data to CSV. If you a cluster BI Platform environment then export data from most heavy used server environment and you should only import the data from one Derby instance. Importing data from multiple Derby instances will result in data inconsistency and is therefore not recommended.

Date will be export in CSV format to the default location of the Derby Trending Database folder, located in \SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\Data\TrendingDB. This is also the same location of all the database scripts to create the tables manually (optional).

BEFORE EXPORT:

AFTER EXPORT:

The following four CSV files are generated in the default Trending Database location; each CSV file is a representation of each table to be created in the monitoring DB:

DerbyMot_Mes_Details.csv
DerbyMot_Trend_Data.csv
DerbyMot_Trend_Details.csv
DerbyMot_Mes_Metrics.csv

Step 2: Verify Auditing Database Working Properly

Launch MS SQL Server Management Studio to validate Auditing database have been created properly

Step 3: – Create Monitoring Tables manually (Optional)

Use the SQL Scripts provided by BI Platform install to create the monitoring tables manually, location

\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\Data\TrendingDB\

\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\Data\TrendingDB

Step 4: – Configure 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, file location is <Install_Dir>\dataAccess\connectionServer\odbc\<dbType>.sbo

If the database used for auditing is SAP HANA, the file where the driver needs to be configured is: newdb.sbo
If the database used for auditing is MS SQL Server, the file where the driver needs to be configured is: sqlsrv.sbo
For more details on other database type refer to The Platform Administration Guide, location http://help.sap.com/businessobject/product_guides/sbo41/en/sbo41sp4_bip_admin_en.pdf
Below is an example of changes MUST be made to ALL MS SQL 2008 / 2012 / 2014 database

BEFORE:

AFTER:

Step 5: – Switch Monitoring to Auditing Database

Logon into CMC > Application > Monitoring Application Properties > Select Use Audit Database

Select Use Audit Database
Save & Close

Step 6: – Restart the APS Server used by Monitoring or SIA

Restart the APS Server being used by Monitoring
- If you are NOT sure, restart the ALL servers using SIA

Step 7: – Validate the creation of NEW Monitoring Tables

Validate the creation of Monitoring tables within Auditing Database, if you did NOT create the tables manually using the sql scripts
- You have 4 – NEW tables created within Auditing Database schema
- See below

Step 8: – Restore exported content to target Audit database

Restore matching CSV file to matching tables crated in Audit database
For more details please refer to your Database Administrator

9 Comments

You must be Logged on to comment or reply to a post.

Manikandan Elumalai August 26, 2014 at 2:39 am

Thanks for Sharing Ajay!

like (0)
Former Member September 24, 2014 at 6:57 pm

Hi,

Thanks for sharing!!

By any chance do you have any document with Oracle DB as Auditing DB.

Thanks

like (0)
1. Ajay Gupta Post authorOctober 15, 2014 at 5:37 pm
  
  For Oracle you need to use JDBC driver. I don’t have document but the information is fully explained in the BI 4 Platform Admin Guide
  
  http://help.sap.com/businessobject/product_guides/sbo41/en/sbo41sp4_bip_admin_en.pdf
  
  Regards,
  
  Ajay
  
  like (0)
  1. Yves KERVADEC November 27, 2014 at 3:09 pm
    
    Hi
    
    The admin guide does not provide detailed/accurate information for using Oracle DB but this note does 1768678 – BI 4.0 HOWTO – Configure Oracle Audit database for Monitoring
    
    Regards
    
    like (0)
Former Member September 16, 2015 at 3:51 pm

Ajay, Do we need to update the SBO file for the SQL 2012?

like (0)
1. Ajay Gupta Post authorSeptember 16, 2015 at 5:29 pm
  
  Juliet,
  
  You need update the sqlsrv.sbo file for ALL MS SQL Server related db, that includes MS SQL 2008 / 2012 / 2014.
  
  Regards,
  
  Ajay
  
  like (0)
  1. Brian Kudera November 5, 2015 at 4:11 pm
    
    Thank you, yes… it does seem that you do indeed need to update the alias for all SQL Server entries in the sbo file. I tripped over this for quite awhile as I only made the changes per the screenshot above included in this document (was only highlighting changes made to the SQL Server 2008 section).
    
    To prevent others from making this same mistake, perhaps your “After” screenshot in Step 4 could be updated?
    
    Thank you!!
    
    like (0)
    1. Ajay Gupta Post authorNovember 10, 2015 at 1:52 pm
      
      Thanks Brian,
      
      Updated step 4 to include SQL 2008 & 2012 servers.
      
      Ajay
      
      like (0)
Brian Kudera December 10, 2015 at 4:04 pm

We’ve recently moved from SQL Server 2012 to SQL Server 2014.

Needless to say, my monitoring application has seem to become fubared!

Before I submit an incident to SAP, any recommendations? Has anyone got this working on SQL Server 2014 and if so, what does your sqlsrv.sbo config file look like?

I’ve confirmed that the alias as mentioned above is listed in ALL databases, including the section for SQL Server 2014.

like (0)