Irfan's World

February 17, 2013

JDBC connectivity in SAP BI 4.0

Filed under: SAP BI 4.0 Administration — Tags: , , — Irfan @ 11:32 pm

If you want to connect IDT(Information Design Tool)  to reporting data sources using JDBC you need a .jar file for that relational database.  Here in this example, I am going to show you how to connect to ORACLE database 11g and SQL Server 2008 databases.  First make sure you have the middle ware /client software installed on the machine where you are using IDT. Also every database vendor will provide their own JDBC connectivity driver software to connect to their databases or you can write your own class definition and use it.

The most common errors that you will see are the following ones when you try to connect to a database using JDBC drivers using IDT:

 Java Class not found in classpath and No suitable driver found (Generic)
 java.lang.ClassNotFoundException: oracle.jdbc.driver.OracleDriver (for Oracle only)
oracle.JDBC.OracleDriver (for Oracle only)
com.microsoft.sqlserver.jdbc.SQLServerDriver (for SQLServer only)

Oracle database

1) Lets create a new relational connection in IDT to connect to Oracle using JDBC drivers. If you don’t find ojdbc14.jar(required for oracle 11g) then you can download directly from www.oracle.com for the version you need.
image
2) Give it a name and click Next, Select the database middleware driver, Oracle 11.
image
3) In the above screen select JDBC Drivers and click Next to go to next screen
image
4) In the above screen specify the user name, password, oracle server host name:port # and oracle service.  Click Test Connection, it may display the error discussed above.  To resolve this  error copy the ojdbc14.jar to the following
directory : <install folder>\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer\jdbc\drivers\oracle\ojdbc14.jar on the BI Server or a client machine.   If you don’t see oracle directory under drivers directory
in the said path then create it and save the .jar file under it.
5) Restart IDT and try creating the JDBC connection again, It should be successful and hence we resolved the error.

image



SQL Server 2008

Similarly trying to connect to SQL Server 2008  using IDT throws an error shown below.

image

To resolve the error, download the latest jdbc driver for SQL Server 2008 from Microsoft website (http://www.microsoft.com/en-us/download/details.aspx?id=11774) , As of this writing the file available from microsoft is “sqljdbc_4.0.2206.100_enu.exe”, extract this file to a temp folder on your machine and copy sqljdbc4.jar to the following folder: <install folder>\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer\jdbc\drivers\sqlsrv2008.

After saving the sqljdbc4.jar in the right folder, try to create a new JDBC connection to connect to SQL Server 2008 using IDT the connection was successful.

image

One last thing I would like to bring is that most of jdbc connectivity errors will be resolved if the classes or jar files are found in the path environment variable.  That is all for today.

Advertisements

8 Comments »

  1. Thanks Irfan, thats a time saving post !!

    Comment by Zamir — February 27, 2013 @ 5:44 am

  2. Many Thanks Irfan!!!! in fact i have the similar issue for postgresql database. your solution is the only one solution which solved my issue
    Thank you very much…………………

    Comment by Lakshigan- — October 8, 2013 @ 11:55 pm

  3. Thanks Irfan, this helped me out solve the connection issues to Oracle which I was attempting using ODBC drivers without much luck..
    BTW, any views on how to build a combined datafoundation on Oracle (JDBC connection) & SAP BW databases, both being relational connections?

    Comment by SM — November 27, 2013 @ 3:48 pm

  4. Thankyou Irfan… It is very helpful 🙂

    Comment by Jyoti — December 12, 2013 @ 2:01 am

  5. Thank you Irfan… its really helpful.. Could you please help me out, why I am not able to see the database middleware driver selection when I tried to create the new relational connection in IDT. Its showing empty.

    Comment by devi — October 15, 2014 @ 6:19 am

  6. Thank you very much for you valuable inputs. It really save my time.

    Comment by Babu — December 11, 2014 @ 7:06 am

  7. Hello Irfaan, we are currently using SAP BO 4.0 SP12 with Oracle 10g…there is a plan to upgrade DB to Oracle 11g. What are the steps we should be taking care pre & post upgrade.

    Comment by Fayaz — February 4, 2016 @ 9:53 am

    • Faiyaz,
      Let them upgrade to Oracle 11g first. Take a backup of BI Repository on Oracle 10g and move the BI repository to 11g. Stop the BI Server (CMS Service), Go to CCM and change the repository connection to point to 11g host and DBName.
      Lastly change the connections for .unv\.unx and wherever applicable.

      Good Luck,
      Irfan

      Comment by Irfan — February 5, 2016 @ 12:52 am


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: