Skip to Content

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

To report this post you need to login first.

37 Comments

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

  1. James O'Donnell

    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

    (0) 
    1. Jose Ramos 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.

      (0) 
    1. Jose Ramos 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.

      (0) 
        1. Jose Ramos 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
          (0) 
            1. Jose Ramos 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!

              (0) 
                1. Jose Ramos 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!

                  (0) 
  2. Jaymin Patel

    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?

    (0) 
    1. Jose Ramos 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.

      (0) 
  3. 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

    (0) 
    1. Jose Ramos 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.

      (0) 
        1. Jose Ramos 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.

          (0) 
          1. Raj R

            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

            (0) 
            1. Jose Ramos Post author

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

              José

              (0) 
  4. Robinson Quiroz

    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

    (0) 
    1. Jose Ramos 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.

      (0) 
  5. Jerry Liu

    😕 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

    (0) 
  6. 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

    (0) 
    1. Jose Ramos 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.

      (0) 
      1. 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

        (0) 
        1. Jose Ramos 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.

          (0) 
  7. Sophie Xu

    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

    (0) 
    1. Jose Ramos 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.

      (0) 
  8. Bipllaw Bhattacharya

     

    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

    (0) 
    1. Jose Ramos 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é

      (0) 
      1. Bipllaw Bhattacharya

        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

        (0) 

Leave a Reply