Connecting to Oracle Business Intelligence EE using Oracle Data Integrator


Looking for Data Integration at OpenWorld 2009? Click here!

The posts in this series assume that you have some level of familiarity with ODI. The concepts of Topology, Data Server, Physical and Logical Architecture are used here assuming that you understand them in the context of ODI. If you need more details on these elements, please refer to the ODI Tutorial for a quick introduction, or to the complete ODI documentation for more details.

In this post I will describe how to create a connection to an Oracle Business Intelligence (OBI) Server.

Adding the Oracle BI Server JDBC driver to ODI

Before we can connect to Oracle BI Server we need to add its JDBC driver to the ODI_HOME\oracledi\drivers directory.
You will find the Oracle BI JDBC driver (bijdbc.jar) in your OBI EE installation directory -> OBI_HOME\jdbc. You can simply copy it and then paste it into the ODI drivers directory.
Note: Make sure you use the JDBC driver coming with OBI EE 10.1.3.4.1, I've had issues with previous releases of the driver. If you are getting an error like 'No Suitable Driver' you are not using the right driver.

Creating the Oracle BI Server Technology in Topology Manager

The Oracle BI Server technology does not exist in ODI 10g, while we could use the Oracle technology it is cleaner to create a new technology.
Instead of creating it from scratch I recommend to duplicate the Oracle technology.

Open Topology Manager and locate the Oracle technology in the Physical Architecture tree.
Right-click on Oracle and select Duplicate.

Duplicate_Technology.png

A new technology called Copy of Oracle should now appear in the list.
Double-click on it to edit it.
Change its name to Oracle BI Server and its code to ORACLE_BI_SERVER.
BI_Server_Techno_1.png

Under Naming Rules check the Using "Catalog"' checkbox and enter Catalog in the Used Term field.
Remove Schema in the Used Term field and uncheck the Using "Schema" checkbox.
BI_Server_Techno_2.png

In the Local Object Mask and Remote Object Mask fields enter "%CATALOG"."%OBJECT".

BI_Server_Techno_3.png

Go to the Language tab, empty the Object Delimiter field and check the Object case-sensitive checkbox.

BI_Server_Techno_4.png

Creating a Data Server for the Oracle BI Server Technology

Now that we have created the OBI Server technology we can create a data server to connect to an OBI Server instance.
Right-click on the Oracle BI Server technology and select Insert Data Server.

BIServer_Insert_DataServer.png

In the Data Server window, enter any name in the Name field.
Enter your OBI Server username and password in the User and Password fields.

BI_Server_DataServer.png

Click on the JDBC tab.

Enter the JDBC Driver Name and JDBC URL in their respective fields:
- JDBC Driver: oracle.bi.jdbc.AnaJdbcDriver
- JDBC URL: jdbc:oraclebi://localhost:9703/ (modify it according to your environment settings)

BI_Server_DataServer_2.png

Note:
The OBI Server JDBC Driver is not in the Driver list, you need to type the Driver name in the JDBC Driver field.
Make sure that there are no spaces at the end or at the beginning of each string.
The JDBC URL needs to end with the '/' character otherwise you'll receive an error while trying to connect.

When you are done click on the Properties tab. We need to add 2 properties.

Click on the Insert button to add a Key.
Enter Catalog in the Key field and the name of the OBI Catalog you would like to access in the Value field.
In this example I used a catalog called Paint.

Click on the Insert button to add a second Key.
Enter NQ_SESSION.SELECTPHYSICAL in the Key field and yes in the Value field.

BI_Server_DataServer_3.png

Click on Test to validate the connection information.
Click OK to close the Successful Connection window.

Click OK to save and close the Data Server window.

A Physical Schema window should pop up.
In the Catalog (Catalog) list select the Catalog you would like to access.
In the Catalog (Work Catalog) list select the same Catalog.

BI_Server_PSchema_1.png

Verify that the Local Object Mask and Remote Object Mask fields are set to "%CATALOG"."%OBJECT".
Click on the Context tab then click on the Insert button to add a row.

Select a context in the Context column and select a Logical Schema or type a new Logical Schema name in the Logical Schema column. I used BIEE_LSCHEMA in this example.

BI_Server_PSchema_2.png

Click OK to save and close the Physical Schema window.

We now have successfully created the infrastructure we need to use OBI Server in our ODI processes.

In another post I will describe how to use OBI EE as a source in an ODI interface:

Comments:

This is a great post. Looking forward for your next post on OBIEE. --Sam

Posted by Sam on November 02, 2009 at 07:03 AM PST #

In another post I will describe how to use OBI EE as a source in an ODI interface. where is your post about this content?

Posted by allen on November 24, 2009 at 03:09 PM PST #

This guide is great1 "In another post I will describe how to use OBI EE as a source in an ODI interface." But, where is your post about above content?

Posted by allen on November 24, 2009 at 03:10 PM PST #

Hi Sam, Allen, Thanks for your comments, you can find the new post here: http://blogs.oracle.com/dataintegration/2009/12/using_oracle_business_intellig.html Thanks, Julien

Posted by Julien Testut on December 09, 2009 at 05:18 AM PST #

Hi, I followed all steps and I am able to test connection succesfully (Test button on data server). But I get following error when I try to insert physical schema: "java.lang.UnsupportedClassVersionError: oracle/bi/jdbc/AnaJdbcDriver (Unsupported major.minor version 49.0)". I am using JRE 1.4.2, which is shipped with ODI. The test connection fail when I try to switch to JRE 1.5.0 or higher with error: "java.sql.SQLException: No suitable driver". Any hints, please? Thanks, Dusan

Posted by Dusan on May 14, 2010 at 02:40 AM PDT #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Learn the latest trends, use cases, product updates, and customer success examples for Oracle's data integration products-- including Oracle Data Integrator, Oracle GoldenGate and Oracle Enterprise Data Quality

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
2
3
5
6
7
8
9
10
12
13
14
17
18
19
20
21
23
24
25
26
27
28
29
30
   
       
Today