Changing Your CMS Database to SAP HANA
I was working on an interesting case where a customer was looking to switch the default CMS database (SQL Server for Windows) to an SAP database. The customer was using SAP BusinessObjects BI Platform 4.0 SP4, which is the version that supports SAP HANA for the CMS. The Administrator Guide has a section that describes what you need to do to select HANA for the CMS database. Although the procedure is documented, I couldn’t find a step-by-step guide, so I figured I’d write about it.
In addition to BOBJ 4.0, you also need a HANA database and the HANA client. If you want to see the database objects, you also need HANA Studio. You can download client and Studio from the HANA Developer Center.
Note that these steps are only for Windows. The procedure applies to BusinessObjects BI Platforms 4.0 SP4. Newer versions will likely allow you to use HANA as the default CMS database.
Step 1: Create a HANA User for BOBJ
Assuming that you have administrative rights on your HANA database, create a new user called “BOEUSER“. This user will own the CMS tables.
Step 2: Create a System ODBC Data Source Name for HANA
- Open the ODBC Administrator. It is located in Control Panel | Administrative Tools.
- Add a new System DSN using the HDBODBC driver. Call this DSN “Business Objects HANA CMS” and enter the appropriate server location to connect.
Step 3: Switch the CMS Database
- Launch the Central Configuration Manager (CCM) using the Start menu.
- Right-click on Server Intelligence Agent (SIA) and select Stop from the popup menu to stop that service.
- Once the service is stopped, right-click again on the SIA and select Properties from the popup menu.
- The SIA properties dialog appears. Select the Configuration tab and click the Specify button.
- Choose to Copy data from another Data Source and click OK.
- You are asked to specify the source and destination data sources. Click Specify.
- Choose SQL Server (ODBC) as the connection method and click OK.
- Select the SQL Server data source name (BusinesObjects CMS 140) and click OK.
- Enter the credentials to login to the SQL Server CMS database and click OK.
- Enter the cluster key and click OK.
- You are now back at the dialog to specify the source and destination data sources. Click the Browse button.
- Choose SAP HANA database (ODBC) as the connection method and click OK.
- Select the HANA data source name created earlier (BusinesObjects HANA CMS) and click OK.
- Enter the credentials to login to the HANA database. Make sure to specify the user BOEUSER you created in Step 1. Click OK.
- Enter the cluster key and click OK.
Again, you are back at the dialog to specify the source and destination data sources. The field “Get data from” should read “BusinessObjects CMS 140” and the field “Copy to the following data source” should read “BusinessObjects HANA CMS”.
- Click OK to begin the process. You will be warned that all BOBJ tables will be deleted from the database. Click Yes.
BusinessObjects will now create and populate the CMS tables in the HANA database.
- Click OK when the procedure completes.
- You can now start the SIA service. Right-click the SIA and select Start from the popup menu.
You’re done! You can now close the CCM.
Viewing the HANA CMS Tables
This step is optional, but if you want to see the tables that were just created in your HANA database, you can query them using HANA Studio:
perfiect
Jose,
Thanks for the timely, useful and well constructed post.
During your project and subsequently, did you happen to take any observations about the performance impact of running the CMS (and alternatively the Audit DB) in HANA? I do a lot of implementations, often with attending automated stress testing and I presume the CMS process and DB get fairly busy when 1200 users attack the cluster at the same time. How has this impacted performance of HANA as a data store?
Jim
Hello Jim,
I don't have any stats about performance of a HANA CMS. I suggest you ask this question in the forum and someone from the BOBJ team can point you in the right direction.
And if i have a one CMS on HANA and want to add another one CMS on HANA to build cluster. What i need to do in this case?
BI 4.1 SP1
Hello Mikhail,
I believe you can use HANA in a cluster environment, just like any other database. You'll need the appropriate clustering software and hardware and then it's just a matter of making sure traffic is routed to the appropriate node. Try asking this question in the HANA community because it's more related to the database than BOBJ.
Hi Jose,
I can't add second CMS because in BI 4.1 SP1 installer do not have option with HANA DB. Maybe you know?
That's actually correct, the installer does not show HANA for the CMS, but you can still use it:
Just switch? Are you shore?
On your screen 5 choose "Update Data Source Settings" and that's all?
By switching I just meant changing the CMS to HANA exactly as described in this document. On screen 5, use "Copy data from another Data Source" so that you take the data from SQL Anywhere and put it into HANA. Don't forget to create the System ODBC DSN for your HANA database!
Hi Jose.
We add Second CMS, not first.
Mikhail - I think it would be best to ask this question in the BI forum. My guess is that you still need to install the second CMS using the default database and then re-create it with HANA, similar to the procedure above. Best to ask the BI folks via the forum to be sure. Thanks!
Problem solved.
The way how to add second node in HANA found.
Thank you.
Hi Mikhail,
Can you please share me the process how you add second node in HANA...?
Thanks,
Raju
Hello Jose Ramos,
Thanks for sharing the information. I have a questions. We have BW on HANA currently being deployed. I wanted to know if I could create instance and use HANA for CMS database. Could you please help me with this question? Would there be a licensing issue as we are running BW on HANA?
Hi Jaymin - I recommend you ask this question to your account exec. As you noted, there may be an issue with using HANA outside of BW, and that should be spelled out in your licensing terms.
Thanks for Sharing.
Hello Jose,
I was planning to do a CMS Migration from the default Sybase SQL Anywhere database to SAP HANA. I would say I was half successful. Because, by default, while specifying the destination from which the data has to be copied, the DSN lists 2 databases created for the SQL ANYWHERE. one for Audit and one for CMS. I'm able to select only one from which the data has to be transferred. When the migration is completed, I checked in SAP HANA, only the CMS tables were copied and not the auditing tables. Any advise on how to migrate the auditing tables as well to SAP HANA
Hi Skugan - Now that you have the CMS database migrated to SAP HANA, try doing the procedure again, but this time choose the SQL Anywhere Audit data source. You'll need to create an ODBC DSN for the SAP HANA Audit database, just like you did for the SAP HANA CMS database.
Hi Jose,
As per your instruction I created a new ODBC DSN for the SAP HANA Audit database, but it is throwing the following error. Please a
dvise 
Hi Skugan - are you migrating the existing SQLA Audit schema into a new HANA Audit schema? The error above looks like you're trying to migrate to an existing HANA schema (the CMS one). Try creating a new schema just for the Audit database and point to it in your ODBC DSN for HANA. If that doesn't work, try asking that question in the BI forum. I don't know whether you can use the same schema for both CMS and Audit dbs. AFAIK, you need different schemas for each one.
Hi Jose,
Nice post and thanks for the detailed information. Can you also tell me if there is any possibility of switching BO CMS from HANA to SQL Server or any other Database..? If Yes, is the procedure is same ?
Thanks in Advance...
Raju
Hi Raju - yes it's possible to use another database (but why would you want to 😉 ). Simply use the same procedure.
José
Hi Skugan,
you have to login CMC then chose Auditing to setup HANA AUDIT database
Hi Jose, I have BO on DB2 (default) on Linux, and I want to change Database to Oracle 11g, so how can i perform it?, Itś needed to install another Business Object on Oracle in the same server connect both database and run the CMS migration tool?, or itś needed only install Oracle and change in some configuration option-place the database?
Iappreciate your help.
Thanks Robinson
Hi Robinson - To switch from DB2 to Oracle, just follow the same steps above, but choose the Oracle native driver. You'll need to create the Oracle databases for the CMS and Audit stores, as well as the ODBC data source names.
😕 don't know why SAP did not just add option HANA DB on installation process, even BOE4.1 SP01 which i am using.
to setup Audit Database need to login CMC then chose Auditing.
Jerry
Hi Jose,
I am Installing SAP BO on Linux with a Default DB. Is it possible to migrate the DB to HANA by creating system DSN in SUSE Linux in the same way? What would be the procedure.
Thanks
Hello there - yes you can create the DSN in Linux. I believe you need to edit the .odbc.ini file, similar to what's stated in this post: http://scn.sap.com/thread/3204662. You have to ensure the HANA Client is present in the Linux machine. I think this is the case when you install BO, but better to double-check.
Hi Jose,
Thanks for your reply
I am installing BO on SUSE Linux and later running cms db setup to migrate cms db to HANA. when the system asks me to enter ODBC DSN for SAP HANA DB I give SAPHANACMS based on the odbc.ini file I edited in <install dir>/sap_bobj/enterprise_x140
[saphanacms]
Driver = /usr/sap/hdbclient/libodbchdb.so
Host = hostname
Port = 3xx15
I get the below error after entering the HDB user ID and Password
err: Error: Unable to check Database
err: Error Description: Internal Error: An Unknown exception has occurred. FWB 00090
Need your help
Thanks
Internal errors usually mean there's something wrong with the actual software. I suggest you contact BOBJ support to find out why this is happening.
Dear Jose,
Wonderful document!
May i know whether there is any specific privilege need to be grant on HANA side.
I found most of the documents only mentioned steps of migrating the other DB to HANA db. However, for user which is created in HANA, do we have to give any privilege?
Much appreciate!
Sophie
I believe the only privilege you need is the ability to create a schema and tables. That's what the setup program does - it adds the BOBJ tables in its own schema.
Thanks a lot.
We will monitor it during migration.
Nice One 🙂 Thanks
Hi ,
I am doing migration of Source BOBJ ( Oracle ) with 1CI and 2 APP servers to BOBJ on HANA similar setup 1CI and 2 APPP . Let me know do I need to perform 3 individual migration processes individually on each servers ?
Regards
Bipllaw
Hi Bipllaw - you definitely need to migrate the BOBJ database from Oracle to HANA by following the steps in this article. For the other 2 sources, 1CI and APP, it seems to me they are not part of the BOBJ database (are they?) so in that case you'll need to manually do the migration.
BR,
José
Hi Jose,
Thanks for your reply. I have completed the CI BOBJ from ORACLE to HANA .
Let me know the process of setting up the two APP servers as well. Do I need to do another migration for them.
Is there any document available on how to set additional application servers in BOBJ.
Regards
Bipllaw
Hi, Jose -
I realize this note is pretty old, - we are currently running BOE 4.3 SP1 on Windows. We're considering migration of our CMS/Audit Database schemas from Oracle to HANA.
It appears that the process for doing so which you outline here is largely the same. Are you familiar with any changes since this post was originally written?
-dave