BIPublisher and mySQL

The other day I was asked to look in to how we can use BIPublisher to report against a mySQL database. According to the documentation one can report against basically any database that can be connected to via JDBC. Obviously I am assuming you already have your mySQL database up and running :). But to connect to the database we have to get the JDBC drivers, mySQL Connector/J. The current release is 5.1, so download the zip file and unzip it somewhere on your machine. Next we must register the driver with the application server where the BIPublisher service is running.

  1. Log in to the Enterprise Manager for the Application server. If you are running a locally installed OC4J, the URL is most likely http://localhost:9704/em/
  2. Navigate to the Administration tab of the OC4J instance and find the Shared Libraries link.
  3. Proceed to create a new Shared Library by clicking on the Create button.
  4. Call the new shared library mysql.jdbc and give it version number 5.1.5
    BIP and mysql1 and click on Next
  5. Now add the newly downloaded driver archive to the shared library
    BIP and mysql2
  6. Once the file has been uploaded to the Application Server instance, click on Finish.
    BIP and mysql3
  7. On the Application Server machine, find the file called orion-application.xml for the xmlpserver application (on my machine the file is located in C:\oracle\BI\OracleBI\oc4j_bi\j2ee\home\application-deployments\xmlpserver)
  8. Add the following code to the file: <imported-shared-libraries> <import-shared-library name="mysql.jdbc"/> </imported-shared-libraries> within the <orion-application> tags.
  9. Restart the xmlpserver application.
Now that the JDBC driver has been registered with the Application Server and the BIPublisher application has been modified to include the library in it's application launcher, we can go ahead and create a BIPublisher data source that uses this newly registered driver
  1. Log in to the BIPublisher as a user with Administration privileges.
  2. Add a new JDBC Data Source via the Admin tab (here I have a special created a special bip user in the mySQL database):
    BIP and mysql4
  3. Make sure your connection works by hitting the Test Connection button.
  4. Now you can move on and create your reports, using the newly created Data Source
    BIP and mysql5
And that's that.