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
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.
