Connecting Oracle Business Intelligence OBIEE to ODBC SQL Server Data Source

Hello again.   One thing I wanted to blog about was to how to get OBIEE to talk to SQL Server.

Version of  OBIEE :  11.1.1.6 ,  OS Linux x86-64

Version of SQL Server :  2008.

There are two things to do, one easy and one a little complicated.

The easy one to do is to connect rhe Admin PC ; this is as simple as creating a SQL Server ODBC DSN in ODBC Administrator.  You can then reference this ODBC DSN  in the Repository Builder.

The only real thing to be noted here is the consistent naming convention that must be adopted; you MUST give the DSN a valid name such as LIVE_DWH , TEST_DWH or some such.  The reason for this is you will be using it on the server side, as the name will be encoded into the RPD and so it will look for it on our ODBC config on the server side.


So we assume when starting here you are configuring the server side only  to talk to the ODBC data source.

I presume a slightly different approach is needed for a Windows server OS but it may not be, I haven't tried.

Items to be obtained before we continue.

1.  IP Address or hostname of the database server.
2. Port Number for the SQL Server Database This is usually 1433

Before you proceed ensure you can reach the database server, e.g. via telnet 1433.  If the screen clears or responds then the commlink is usually OK, if unreachable or timeout then you need to fix before progressing ( usually a firewall issue ). If telnet is not present ask for it to be installed or su - yourself and yum -y install telnet 

So we know we can reach the server.  We now need to know

3.  Userid and password to connect to the database.
4.  Name of the database inside SQL Server.

Merant Drivers. 
Oracle supply the Merant drivers to enable OBIEE to talk to an ODBC data source; specifically what you are doing is enabling nqserver to do so.

Unfortunately to confuse matters they also supply  2 different driver versions ( 5.3 and 6.0 ) and the default 5.3  one doesn't work ( well it doesn't for me )  to talk to SQL Server 2008. However the 6.0 has no issues.

So our story is
1:  Reconfigure OBIEE to use 6.0 drivers
2:  Configure the drivers to point at our DSN
3:  Check functionality.

Reconfiguring OBIEE

OBIEE Siebel processes ( nqserver, sawserver etc ) are executables that are controlled in V11 by the opmn framework.

Environment settings for these processes are contained in opmn.xml


OPMN to use the 6.0 Drivers 

Therefore in order to make OBIEE use the 6.0 drivers instead of the 5.3 ones we must edit opmn,xml.

Our first task is to find the right opmn.xml file

If you've installed OBIEE in the best practice setup you will have two, one for the FMW components and one for the WT components

The one you want is the FMW one.

This is usuallly found in

$OH//mw/instances/instance1/config/OPMN/opmn/opmn.xml

OK.  So take a backup of the file ( if you don't, and it breaks, it is one of the easiest means of making the OBIEE completely unusable as it is a key configuration file. ).

In this file you will see several entries for LD_LIBPATH and PATH.

These paths contain references to the Merant 5.3 drivers -e.g.


   

We need to point to all the 6.0 drivers instead.  Check the driver are installed 

ls $ORACLE_HOME/common/ODBC/Merant

]$ ls -lrt /mw/Oracle_BI1/common/ODBC/Merant/
total 16
 4096 Jun 14  2011 5.3
 4096 Jun 14  2011 5.2
 4096 Jun 14  2011 6.0SP1
 4096 Jun 14  2011 6.0

OK so they are there.
Now we need to replace the paths to 6.0 instead

Do this manually in opmn.xml above

or you can use ( no charge ! )



 sed 's/Merant\/5\.3/Merant\/6\.0/' < ./mw/instances/instance1/config/OPMN/opmn/opmn.xml > opmn.xml.new , and replace the files as appropriate

if you can't be bothered with all that typing 

OBIEE ODBC to point at Sql Server 2008 

 With the above fixes we now have good drivers to use, the next thing to is to configure the drivers to point at our needed DSN.  This means editing ODBC.ini

From the Windows Admin tool you have already added an ODBC DSN . We will call this FOODSN. 

Since the RPD wants this we also create it in ODBC.INI

Where edits are done below to protect the client settings I have **  them. 

[FOODSN]

Driver= ** PATH TO ** /Oracle_BI1/common/ODBC/Merant/6.0/lib/ARmsss24.so

Description=DataDirect 6.0 SQL Server Native Wire Protocol

AlternateServers=

AlwaysReportTriggerResults=0

AnsiNPW=1

ApplicationName=

ApplicationUsingThreads=1

AuthenticationMethod=1

BulkBinaryThreshold=32

BulkCharacterThreshold=-1

BulkLoadBatchSize=1024

BulkLoadOptions=2

ConnectionReset=0

ConnectionRetryCount=0

ConnectionRetryDelay=3

Database=**  NAME OF SQL SERVER DATABASE 

EnableBulkLoad=0

EnableQuotedIdentifiers=0

EncryptionMethod=0

FailoverGranularity=0

FailoverMode=0

FailoverPreconnect=0

FetchTSWTZasTimestamp=0

FetchTWFSasTime=1

GSSClient=native

Address=** IP ADDRESS  OF THE DATABASE, PORT OF THE SQL CONNECTION;

HostNameInCertificate=

InitializationString=

Language=

LoadBalanceTimeout=0

LoadBalancing=0

LoginTimeout=15

LogonID=** USERID TO CONNECT TO SQL SERVER 

MaxPoolSize=100

MinPoolSize=0

PacketSize=-1

Password=** PASSWORD TO CONNECT TO SQL SERVER 

Pooling=0

PortNumber=** WHATEVER THE PORT IS 

QueryTimeout=0

ReportCodePageConversionErrors=0

SnapshotSerializable=0

TrustStore=

TrustStorePassword=

ValidateServerCertificate=1

WorkStationID=

XML Describe Type=-10

Trace = 1

TraceFile = ** PATH TO SOME TRACE FILE IF PROBLEMS OCCUR 

TraceDll =  ** PATH TO ** /Oracle_BI1/common/ODBC/Merant/6.0/lib/odbctrac.so

OK.  To make all this work you must restart OPMN 

opmnctl stopall

opmnctl startall 

For debugging purposes, if errors occur they will be seen when you try and access a table in FOODSN.  

The best source of debugging errors is 

1. nqserver.log

2.  IF you are very lucky the ODBC trace routines will work.  This is very tedious to get working.