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
Thanks for Sharing Ajay!
Hi,
Thanks for sharing!!
By any chance do you have any document with Oracle DB as Auditing DB.
Thanks
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
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
Ajay, Do we need to update the SBO file for the SQL 2012?
Juliet,
You need update the sqlsrv.sbo file for ALL MS SQL Server related db, that includes MS SQL 2008 / 2012 / 2014.
Regards,
Ajay
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!!
Thanks Brian,
Updated step 4 to include SQL 2008 & 2012 servers.
Ajay
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.