Skip to Content
Author's profile photo Jose Ramos

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

  1. Open the ODBC Administrator.  It is located in Control Panel | Administrative Tools.

/wp-content/uploads/2013/05/odbcad32_223980.png

  1. Add a new System DSN using the HDBODBC driver.  Call this DSN “Business Objects HANA CMS” and enter the appropriate server location to connect.

HDBODBC.pngSystemDSN.png

Step 3: Switch the CMS Database

  1. Launch the Central Configuration Manager (CCM) using the Start menu.

LaunchCCM.png

  1. Right-click on Server Intelligence Agent (SIA) and select Stop from the popup menu to stop that service.

StopSIA.png

  1. Once the service is stopped, right-click again on the SIA and select Properties from the popup menu.

PropsSIA.png

  1. The SIA properties dialog appears. Select the Configuration tab and click the Specify button.

SIA-Config.png

  1. Choose to Copy data from another Data Source and click OK.

CopyFromOtherDSN.png

  1. You are asked to specify the source and destination data sources. Click Specify.

SpecifyDSN.png

  1. Choose SQL Server (ODBC) as the connection method and click OK.

MSSQLConnMethod.png

  1. Select the SQL Server data source name (BusinesObjects CMS 140) and click OK.

MSSQL-DSN.png

  1. Enter the credentials to login to the SQL Server CMS database and click OK.

MSSQL-Login.png

  1. Enter the cluster key and click OK.

ClusterKey.png

  1. You are now back at the dialog to specify the source and destination data sources. Click the Browse button.

DestinationDSN.png

  1. Choose SAP HANA database (ODBC) as the connection method and click OK.

HANAConnMethod.png

  1. Select the HANA data source name created earlier (BusinesObjects HANA CMS) and click OK.

HANA-DSN.png

  1. Enter the credentials to login to the HANA database. Make sure to specify the user BOEUSER you created in Step 1. Click OK.

ConnectToHANA.png

  1. Enter the cluster key and click OK.

ClusterKey.png

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”.

  1. Click OK to begin the process. You will be warned that all BOBJ tables will be deleted from the database. Click Yes.

SpecifyDSN2.pngWarning.png

BusinessObjects will now create and populate the CMS tables in the HANA database.

MigratingDBs.png

  1. Click OK when the procedure completes.

MigrationComplete.png

  1. You can now start the SIA service. Right-click the SIA and select Start from the popup menu.

StartSIA.png

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:

HANAStudio.png

Assigned Tags

      38 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      perfiect

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Jose Ramos
      Jose Ramos
      Blog Post Author

      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.

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Jose Ramos
      Jose Ramos
      Blog Post Author

      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.

      Author's profile photo Former Member
      Former Member

      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?

      Author's profile photo Jose Ramos
      Jose Ramos
      Blog Post Author

      That's actually correct, the installer does not show HANA for the CMS, but you can still use it:

      1. Install BOBJ 4.1 using the default SQL Anywhere database for the CMS
      2. After installation, switch the CMS from SQL Anywhere to HANA
      Author's profile photo Former Member
      Former Member

      Just switch? Are you shore?

      On your screen 5 choose "Update Data Source Settings"  and that's all?

      Author's profile photo Jose Ramos
      Jose Ramos
      Blog Post Author

      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!

      Author's profile photo Former Member
      Former Member

      Hi Jose.

      We add Second CMS, not first.

      Author's profile photo Jose Ramos
      Jose Ramos
      Blog Post Author

      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!

      Author's profile photo Former Member
      Former Member

      Problem solved.

      The way how to add second node in HANA found.

      Thank you.

      Author's profile photo Former Member
      Former Member

      Hi Mikhail,

      Can you please share me the process how you add second node in HANA...?

      Thanks,

      Raju

      Author's profile photo Former Member
      Former Member

      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?

      Author's profile photo Jose Ramos
      Jose Ramos
      Blog Post Author

      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.

      Author's profile photo Rahul Verma
      Rahul Verma

      Thanks for Sharing.

      Author's profile photo Skugan Venkatesan
      Skugan Venkatesan

      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 HANASybase SQL Anywhere.jpgHANA Tables.jpg

      Author's profile photo Jose Ramos
      Jose Ramos
      Blog Post Author

      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.

      Author's profile photo Skugan Venkatesan
      Skugan Venkatesan

      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 aDSN.jpgdvise Error.jpg

      Author's profile photo Jose Ramos
      Jose Ramos
      Blog Post Author

      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.

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Jose Ramos
      Jose Ramos
      Blog Post Author

      Hi Raju - yes it's possible to use another database (but why would you want to 😉 ).  Simply use the same procedure.

      José

      Author's profile photo Former Member
      Former Member

      Hi Skugan,

      you have to login CMC then chose Auditing to setup HANA AUDIT database

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Jose Ramos
      Jose Ramos
      Blog Post Author

      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.

      Author's profile photo Former Member
      Former Member

      😕 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.

      CMC_Auditing.jpg

      Jerry

      Author's profile photo POD AAES
      POD AAES

      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

      Author's profile photo Jose Ramos
      Jose Ramos
      Blog Post Author

      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.

      Author's profile photo POD AAES
      POD AAES

      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

      Author's profile photo Jose Ramos
      Jose Ramos
      Blog Post Author

      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.

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Jose Ramos
      Jose Ramos
      Blog Post Author

      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.

      Author's profile photo Former Member
      Former Member

      Thanks a lot.

      We will monitor it during migration.

      Author's profile photo Deepak Mohanty
      Deepak Mohanty

      Nice One 🙂 Thanks

      Author's profile photo Former Member
      Former Member

       

      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

      Author's profile photo Jose Ramos
      Jose Ramos
      Blog Post Author

      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é

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo David Hays
      David Hays

      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

      • David Hays