One of the main features of Oracle Business Intelligence Publisher (BIP) is its ability to connect to pretty much every major RDBMS on the market. By default, the configuration for connecting to the Oracle RDBMS is present out of the box. Being a Java-based application at heart, the connection setup to Oracle uses JDBC. Let’s venture out a bit and establish a connection to SQL Server.
Overall, what we’re trying to do is create another data source. The types of data sources appear under the Admin tab.
Clicking the JDBC Connection link and then the Add Data Source link brings up the interface below.
As far as the default Driver Type is concerned with respect to Oracle, been there done that. Expand the drop down list to see what the other currently support database sources are.
Select the Microsoft SQL Server 2005 option and note the similarity as far as the JDBC connection string is concerned.
NOTE: In some references at My Oracle Support and OTN, you may see the driver class written as com.microsoft.jdbc.sqlserver.SQLServerDriver. Accept the default string BIP provides, which uses com.microsoft.sqlserver.jdbc.SQLServerDriver.
So far, this looks to be trivial. I’ve installed SQL Server 2005 on the same PC hosting the BIP installation, and have gone a bit further by installing some sample databases. The database of interest here will be AdventureWorks, which you can obtain from MSDN. I’ve also created the highly original username/password combination of scott/tiger as a Login. Scott has also been added to the Users folder under the Security folder for the database, and has the appropriate roles to see tables in the database (select db_owner if you don’t know what else to pick).
At this point, the completed fields look as shown below, and we’re ready to click Test Connection.
NOTE: To avoid wasting hours looking up error messages related to output such as…
com.microsoft.sqlserver.jdbc.SQLServerException: The port number [1433] is not valid.
…or
com.microsoft.sqlserver.jdbc.SQLServerException: Cannot open database "[AdventureWorks]"
…be sure to NOT use the square brackets in the boilerplate text provided by BIP (in the Connection String field).
A good, final example is shown below.
I named the data source as MSSQL5 to help identify the source in an expedient manner. The MSSQL part should be obvious, and the 5 relates to 2005 so as to separate it from the 2000, 2005 and 2008 named versions of SQL Server. The port number can be found by examining the output from netstat, logs within MSSQL, and also knowing that 1433 is the standard port number similar to 1521 or 1526 in Oracle. You can also go into the configuration manager and examine the TCP information.
Upon clicking Test Connection, we get the highly informative message shown below.
What went wrong with the connection setup? Actually, nothing went wrong; it’s just that we haven’t done everything necessary yet. Going back to the Add Data Source picture, note the tip at the top of the frame: “Please make sure to install the required JDBC driver classes.” Such a simple tip, and as you may already be guessing, it implies a good bit of work or research to get those classes, and the path (the work needing to be done) is not entirely clear either.
The short and sweet of this is that three jar files (msbase, mssqlserver, and msutil) need to be acquired and placed into the ORACLE_HOME (for BIP) and under the path below:
<start>/oc4j_bi/j2ee/home/applib
Once these files are in place (where to get them is coming up) and the OC4J instance is started, enter the connection information as shown, modified for your particulars, and test the connection. This is a go/no-go situation; you either get the error message just shown, or a success message.
Don’t forget to assign a role (BI_USER will likely be the only role available if you haven’t created non-default roles yet) and then click Apply at the top right of the page to finalize the setup. If all goes well, you’ll see the new data source in the available list.
Where to get the MSSQL jar files
A quick and easy way to get the requisite files is to download them from MSDN. The SQL Server 2000 JDBC files work; you don’t need the single 2005 version named sqljdbc.jar (at least as far as BIP is concerned, and even though we did the setup using a 2005 database). Navigate to this URL…
…and download the tar or exe file as appropriate for your platform. Using Windows, run the setup and the JDBC installation for 2000 will be located at:
C:\Program Files\Microsoft SQL Server 2000 Driver for JDBC\lib
Within this folder, you will find the three jar files. Copy them to the applib folder as mentioned earlier. A note on My Oracle Support (Doc ID: 445157.1, How to Install JDBC Connection for MS SQL Server in BI Publisher Enterprise) makes reference to a how-to note at OTN (SQL Server Walkthrough). Within this walkthrough, there are instructions on how to configure OC4J to work with foreign datasources.
This section discusses editing the application.xml file in the <OracleHome>/j2ee/home/config directory. You simply add in three library path tags with the path and file name of each jar file. To be a bit more precise, the example shown at OTN (shown below) has had the closing tags corrected to use “/>” instead of just “>” (just like strict HTML with self-closing tags).
<library path="C:\Program files\Microsoft SQL Server 2000 Driver for JDBC\lib\msbase.jar" /> <library path="C:\Program files\Microsoft SQL Server 2000 Driver for JDBC\lib\msutil.jar" /> <library path="C:\Program files\Microsoft SQL Server 2000 Driver for JDBC\lib\mssqlserver.jar" />
You can go this route if you want (it works just as well, and if you test this, be sure to move or rename the files in the applib folder so you know the jar files are only being referenced via the application.xml file). The edited file also has each tag on one line; the line returns above are for formatting in this article.
NOTE: The link to get the JDBC files in the walkthrough document at OTN is broken or outdated, so use one shown earlier, or do a search at MSDN.
From this point forward, you should be able to create folders and reports in a normal fashion.
In Closing
Once some of the gotcha’s were taken care of in setting up a data source, getting BI Publisher to connect to SQL Server was fairly straightforward, in fact, it was no more difficult than creating a new data source within Oracle. Once the jar files were placed and connection details were identified for the MSSQL database, the setup was trivial.
Can you find this information in the installation guide or release notes? Unfortunately, no, and even the notes on My Oracle Support are a bit lacking. The product as a whole is improving by leaps and bounds, but some time needs to be spent on administration and documentation. Oracle recently released a patch (8284524) for version 10.1.3.4. The patch apply or upgrade process will be the focus of a future article. You may find it worthwhile to apply the patch forthwith as it has two major enhancements (numbers to words, and better support for Single Sign-On) and lots of bug fixes.
