One of the principle highlights of Oracle Business Intelligence Publisher (OBIP) is its capacity to associate with basically every major RDBMS available. As a matter of course, the arrangement for associating with the Oracle RDBMS is available out of the container. Being a Java-based application on a fundamental level, the association arrangement to Oracle utilizes JDBC. We should wander out a piece and set up an association with SQL Server.
Overall, what we’re trying to do is create another data source. The types of data sources appear under the Admin tab. Tapping the JDBC connection and afterward the Add Data Source interface raises the interface underneath
All things considered concerning Oracle, no need to relive that. Grow the drop down rundown to perceive what the other as of now uphold information base sources are.
Select the Microsoft SQL Server 2005 choice and note the likeness all things considered.
NOTE: In certain references at My Oracle Support and OTN, you may see the driver class composed as com.microsoft.jdbc.sqlserver.SQLServerDriver. Acknowledge the default string BIP gives, which utilizes com.microsoft.sqlserver.jdbc.SQLServerDriver.
Up until now, this seems to be trifling. I’ve introduced SQL Server 2005 on a similar PC facilitating the BIP establishment, and have gone somewhat further by introducing some example information bases. The information base of interest here will be AdventureWorks, which you can get from MSDN. I’ve additionally made the profoundly unique username/secret word blend of scott/tiger as a Login. Scott has additionally been added to the Users envelope under the Security organizer for the information base, and has the suitable parts to see tables in the data set (select db_owner on the off chance that you don’t have a clue what else to pick).
Now, the finished fields look as demonstrated as follows, and we’re prepared to click Test Connection.
NOTE: To abstain from squandering hours looking into blunder messages identified with yield, for example,
com.microsoft.sqlserver.jdbc.SQLServerException: The port number  isn’t legitimate.
…or on the other hand
com.microsoft.sqlserver.jdbc.SQLServerException: Cannot open information base “[AdventureWorks]”
…make certain to NOT utilize the square sections in the standard content gave by BIP (in the Connection String field).
A Decent, last model is demonstrated as follows.
I named the information source as MSSQL5 to help distinguish the source in a catalyst way. The MSSQL part ought to be self-evident, and the 5 identifies with 2005 in order to isolate it from the 2000, 2005 and 2008 named forms of SQL Server. The port number can be found by analyzing the yield from netstat, logs inside MSSQL, and furthermore realizing that 1433 is the standard port number like 1521 or 1526 in Oracle. You can likewise go into the design administrator and inspect the TCP data.
What turned out badly with the association arrangement? In reality, nothing turned out badly; it’s simply that we haven’t done all things required at this point. Returning to the Add Data Source picture, note the tip at the highest point of the casing: “It would be ideal if you try to introduce the necessary JDBC driver classes.” Such a straightforward tip, and as you may as of now be speculating, it suggests a decent bit of work or exploration to get those classes, and the way (the work waiting be done) isn’t completely clear by the same token.
The quick and painless of this is that three container records (msbase, mssqlserver, and msutil) should be gained and set into the ORACLE_HOME (for BIP) and under the way underneath:
When these records are set up (where to get them is coming up) and the OC4J occurrence is begun, enter the association data as appeared, adjusted for your specifics, and test the association. This is a go/off limits circumstance; you either get the blunder message just appeared, or a triumph message.
Remember to allocate a job (BI_USER will probably be the main job accessible in the event that you haven’t made non-default jobs yet) and afterward click Apply at the upper right of the page to conclude the arrangement. In the event that all works out in a good way, you’ll see the new information source in the accessible rundown.
Where to get the MSSQL container documents
A fast and simple approach to get the imperative documents is to download them from MSDN. The SQL Server 2000 JDBC documents work; you needn’t bother with the single 2005 rendition named sqljdbc.jar (at any rate taking everything into account, and despite the fact that we did the arrangement utilizing a 2005 information base). Explore to this URL…
…furthermore, download the tar or exe record as proper for your foundation. Utilizing Windows, run the arrangement and the JDBC establishment for 2000 will be situated at:
C:\Program Files\Microsoft SQL Server 2000 Driver for JDBC\lib
Inside this organizer, you will locate the three container documents. Duplicate them to the applib envelope as referenced before. 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). Inside this walkthrough, there are directions on the best way to design OC4J to work with unfamiliar datasources.
This part talks about altering the application.xml record in the <OracleHome>/j2ee/home/config catalog. You essentially include three library way labels with the way and document name of each container record. To be somewhat more exact, the model appeared at OTN (demonstrated as follows) has had the end labels adjusted to utilize “/>” rather than just “>” (simply like severe HTML with self-shutting labels).
<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 course in the event that you need (it works similarly too, and on the off chance that you test this, make certain to move or rename the records in the applib organizer so you realize the container documents are just being referred to through the application.xml document). The altered record additionally has each tag on one line; the line returns above are for arranging in this article.
NOTE: The connection to get the JDBC records in the walkthrough archive at OTN is broken or obsolete, so utilize one demonstrated before, or do a hunt at MSDN.
Starting now and into the foreseeable future, you ought to have the option to make envelopes and reports in a typical manner.
When a portion of the gotcha’s were dealt with in setting up an information source, getting BI Publisher to interface with SQL Server was genuinely clear, truth be told, it was not any more troublesome than making another information source inside Oracle. When the container records were set and association subtleties were recognized for the MSSQL information base, the arrangement was paltry.
Would you be able to discover this data in the establishment guide or delivery notes? Lamentably, no, and even the notes on My Oracle Support are somewhat inadequate. The item all in all is improving significantly, however some time should be spent on organization and documentation. Prophet as of late delivered a fix (8284524) for rendition 10.1.3.4. The fix apply or update cycle will be the focal point of a future article. You may think that its advantageous to apply the fix forthwith as it has two significant upgrades (numbers to words, and better help for Single Sign-On) and loads of bug fixes.
About Me – Harish an graduate and been into a technical content contribution for Mindmajix for the past one year. He is passionate about learning new things about the technological advancements that help for a better living. His articles focus more on the latest trends happening around the world.