JNDI Data Source for Oracle BI Server

In the previous couple of posts you saw how to create the JNDI data source for a database data source and use it from BI Publisher. But what if you want to connect to Oracle BI Server as the data source? You can create the JDPBC connection pool for the BI Server and register it as a JNDI entry, then use it as JNDI data source in BI Publisher just as same as you do with the database.

Today, I’ll talk about how to create the JNDI data source for Oracle BI Server by using OC4J as J2EE Server as an example.

Oracle BI JDBC Driver Registration

In order to create a JNDI connection for Oracle BI Server you need to register a BI-JDBC library to an OC4J server first. You can do this by manually updating one of the OC4J configuration file called, application.xml. Once you register the additional library file then the rest of the steps are the same as the one for the database.

Register BI-JDBC Library to OC4J Server

1. Identify a location where bijdbc.jar is located under OC4J instance. The jar file comes with the BI Publisher Enterprise Server installation and can be found at, %OC4J_HOME%/j2ee/home/applications/xmlpserver/xmlpserver/WEB-INF/lib/bijdbc14.jar
   (where OC4J_HOME is a home folder (or directory) for the OC4J. e.g. C:\OracleBI\oc4j_bi)
2. Go to %OC4J_HOME%/j2ee/home/config folder (or directory)
3. Open the application.xml file
4. Add the following line under this entry “<web-module id="defaultWebApp" path="../../home/default-web-app" /> “
   
   <library path="C:\BIP_Home\oc4j_bi\j2ee\home\applications\xmlpserver\xmlpserver\WEB-INF\lib\bijdbc14.jar"/>

Sample:

<orion-application xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation=http://xmlns.oracle.com/oracleas/schema/orion-application-10_0.xsd autocreate-tables="true" default-data-source="jdbc/OracleDS" schema-major-version="10" schema-minor-version="0"> <web-module id="defaultWebApp" path="../../home/default-web-app" /> <library path="C:\BIP_Home\oc4j_bi\j2ee\home\applications\xmlpserver\xmlpserver\WEB-INF\lib\bijdbc14.jar"/> <web-module id="jmsrouter_web" path="../../home/applications/jmsrouter.war" /> <ejb-module id="jmsrouter_ejb" path="../../home/applications/jmsrouter-ejb.jar" /> <connectors path="./oc4j-connectors.xml"/>

Once you updated the file then save it and restart the OC4J Server to take the change in effect.

This is the only extra step to create the JNDI connection for Oracle BI Server. I’ll go through the steps to create the JDBC connection pool and JNDI for the BI Server, but it’s going to be pretty much the same as the one you have probably already seen in the previous post…

Create Connection Pool

1. Login to Oracle Enterprise Manager (OC4J Admin Console)

2. Click ‘Administration’ tab

3. Click on the ‘Go To Task’ link of ‘JDBC Resources’

4. Click on the ‘Create’ button under Connection Pool

5. Click ‘Continue’ button (Leave all the fields as default)

6. Enter the following information

Name	BIServerDS
Connection Factory Class	oracle.bi.jdbc.AnaJdbcDriver
JDBC URL	jdbc:oraclebi://localhost:9703/
Username	Administrator
Use Cleartext Password	Check
Password	<Password>

7. Click on the 'Test Connection' button, which is located under URL section

8. Type the sample query and Click ‘Test’ button (OBI doesn’t support ‘dual’ table concept so you need to use an actual query here. You can get the query from Answers request under its ‘Advance’ tab.)

9. Click on the ‘Test’ button.

If you get an error message make sure all the values entered at the above steps are correct. If you get a success message then click ‘Finish’ button to save.

Create Data Source

1. Click on the ‘Create’ button under Data Sources

2. Click on the ‘Continue’ button (Leave all the fields as default)

3. Enter the following information

Name	BIServer
Connection Factory Class	jdbc/APEXDS
Transaction Level	Leave as default
Connection Pool	BIServerDS

4. Click on the ‘Finish’ button

Now you have created a JDBC connection pool for the BI Server and register it with JNDI. That means, you can use that as the JNDI data source in BI Publisher Enterprise Server. As you might have remember, in this way you can take advantage of the connection pool. Especially when you have many queries against BI Server for one single report, for example you have many LOVs that goes against BI Server, this option will make a significant impact.