Data Integration and Management

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.

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.

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.

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

Go to the Language tab and empty the Object Delimiter field.

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.

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.

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)

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.

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.

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.

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.