How to Change BusinessObjects BI CMS Database to SAP HANA
While installing BusinessObjects BI Platform 4.x you do not have an option to choose SAP HANA as CMS database even from 4.0 SP4 it already supported SAP HANA as CMS database.
But it is possible to change or migrate CMS from one database to another one after installation, the Administrator Guide has a section that describes what you need to do to select SAP HANA as the CMS database.
This Step by Step Guide demonstrates how to change/migrate CMS from default database (MS SQL Server or SAP Sybase SQL Anywhere) to SAP HANA. Note that below procedure is only for Windows and applies to BusinessObjects BI Platforms 4.1 SP1.
In addition to BusinessObjects BI Platform 4.x, SAP HANA database you also need SAP HANA client and SAP HANA Studio.
There is guide Step by Step Installation SAP BusinessObjects BI Platform 4.x available here if you need.
You can download client and Studio from the HANA Developer Center. Please ensure you use same version of client, studio and database.
Step 1: Create HANA users and add systems
Assuming that you have administrative rights on your HANA database, create two new users BOE141 and BOE141_Audit which will own the CMS and Auditing tables. Also add two systems using these two users on SAP HANA Studio.
- Enter the host name, instance number of your HANA database also description then click on Next to continue
- Enter the user name and password for the CMS database you just created then click on Next to continue
- Click on Finish. Now you successfully add a new system
4. Add second system with user for Auditing database, on SAP HANA Studio will look like this
Step 2: Create a System ODBC Data Source Name for HANA
You don’t have to create ODBC Data Source now since you can create later when you choose ODBC Data Source
- Launch ODBC Data Source Administrator. Ensure you launch the 64Bit version ODBCad32.exe from location %Windir%\System32\ if you installed 64Bit version SAP HANA Client. Otherwise you can just launch 32Bit version one form Control Panel -> Administrative Tools
- Select System DSN tab then click on Add… to create ODBC Data Source. Select HDBODBC driver from the driver list then click on Finish to continue
- Enter Data Source Name, Description and Server: Port for CMS then click on OK
You can click on Connect then enter user name and password to test connection
- Create ODBC Data Source for Auditing database following the same steps. ODBC Data Source Administrator window will look like this
Step 3: Stop SIA from CCM
In order to change CMS database you have to stop Server Intelligence Agent(SIA) from Central Configuration Manager(CCM) first.
- Launch CCM from Start > All Programs > SAP BusinessObjects Intelligence > SAP BusinessObjects BI platform 4 > Central Configuration Manager
- Select Server Intelligence Agent (SIA) then click on Stop icon (black square) on toolbar, or right click on SIA then select Stop from the popup menu to stop the service
Step 4: Change CMS database
- Once the SIA service is stopped, select SIA then click on Specify CMS Data Source icon on the toolbar ( the gold cylinder)
- CMS Database Setup window pop up, you can choose desire setup method then click on OK to continue
- Update Data Source Settings
Choose this option if you would like to point to a different data source. This option will simply change your data source without copying over existing data.
Note: The destination data source must contain deployment information for this cluster. Do not use this option for clustering with a different CMS cluster. Refer to the Administrator ‘s Guide for details on CMS clustering workflows.
If you choose Update Data Source Settings a Select Database Driver window will pop up to allow you choose a connection method. Here choose SAP HANA database (ODBC) of cause then click on OK to continue
Select Machine Data Source then select CMS141 created in Step 2 then click on OK to continue. If you have not yet created data source for you HANA database you can now click on New to create new one here.
Enter the user name and password then click on OK to continue
Enter the cluster key then click on OK to continue. CMS tables will be created on HANA database BOE141(ODBC CMS141)
- Copy data from another Data Source
Choose this option if you would like to copy data form a different data source. You may change your data source or keep the one that you are currently using. This option will destroy any information already present in the destination data source. The deployment topology of the destination cluster must match the topology of the source cluster. Refer to the Administrator’s Guide for details on copying CMS data Sources.
Select current using database from list, default is SQL Server or SQL Anywhere. Click on OK to continue.
You might be asked ID and password to log in database
Enter cluster key then click on OK to continue
Next then click on Browse to select target data source. From the list select SAP HANA database then click on OK to continue.
You might be asked ID and password to log in database.
After specifying source and target database then click on OK to start copy CMS tables from source to target. All data in target will be overwritten.
- Recreate the current Data Source
Choose this option if you would like to re-initialize the current data source. This option will destroy any data already present in the data source.
Step 5: Verify CMS changed and configure Audit database
- Select Server Intelligence Agent (SIA) then click on Properties icon on toolbar from Central Configuration Manager (CCM), or you can also right click on SIA then select Properties to verify changed
2. Select Configuration you can find out the CMS now has been changed to CMS141 ( SAP HANA database BOE141). You can also change cluster name and cluster key here. Click on Cancel go back to CCM
Select SIA then click on Start icon ( black triangle) or right click on SIA then select Start to start SIA service again.
After SIA service is running you can login to Central Management Console (CMC), click on Auditing under column Manage
Configure ADS Database settings accordingly, enter SAP HANA ODBC DNS name in field Connection Name, select SAP HANA Database from Type then enter user name and password. Click Save to save configuration.
Warning: You must restart all CMSs before ADS configuration changes will take effect.
Restart server then login CMC again then you will see the Auditing is taking effect
Launch SAP HANA Studio to view CMS and Auditing tables