OBIEE 11g Configuration for MS SQL Database

Step 1: Apply the updated/recent ODBC driver.

Considered , OBIEE version: 11.1.1.7.1 and Linux 64 bit server. 

Need to download the latest Data driver 7.1.5 from https://support.oracle.com

Patch #: 21440314

Apply the patches as per the Oracle instructions. Post to that we need to manually copy and unzip the driver to below location.

/u01/BIUAT/OBIEE/Oracle_BI1/common/ODBC/Merant

Step 2: System DSN Setup.

Create a System DSN to connect to MS SQL database

Step 3: OPMN.XML file Configuration.

Navigate to below location to do the change for opmn.xml file.

/u01/BIUAT/OBIEE/instances/instance1/config/OPMN/opmn

Backup copies of opmn.xml. Make edits to opmn.xml.

Search for BI components in opmn.xml. These are XML elements, <ias-component>, with the id "coreapplication_obi<...>".

In each of these BI component elements, modify and make sure that these environment variables point to recent ODBC driver.    

 $LD_LIBRARY_PATH

 $PATH

Step 4: ODBC.INI file Configuration.

Navigate to below location to do the change for odbc.ini file.

/u01/BIUAT/OBIEE/instances/instance1/bifoundation/OracleBIApplication/coreapplication/setup

Backup of odbc.ini file.

Need to do below changes for [ODBC] tag. TraceDll and InstallDir need to be pointed to new data driver.

Need to modify the below mentioned line for MS SQL Data driver section.

·         Tag name should be same as System DSN name which we have created.

·         Driver path should point to 7.1.5 .

·         Database

·         EnableQuotedIdentifiers should set to 1.

·         HostName. ( Along with Instance if any)

·         PortNumber

Once this changes are done , we need to copy the file to new Data driver folder as well.

/u01/BIUAT/OBIEE/Oracle_BI1/common/ODBC/Merant/7.1.5

Step 5: USER.SH file Configuration.

Navigate to below location to do the change for user.sh file.

/u01/BIUAT/OBIEE/instances/instance1/bifoundation/OracleBIApplication/coreapplication/setup

Backup the user.sh file.

In the section for your operating system, include the appropriate library path environment variable for the DataDirect Connect libraries. Ensure that you point to the appropriate library, depending on whether you are using a 32-bit or 64-bit database. Note the following:

·         For Solaris and Linux, the library path variable is LD_LIBRARY_PATH.

·         For HP-UX, the library path variable is SHLIB_PATH.

·         For AIX, the library path variable is LIBPATH.

Step 6: BI-INIT.SH file Configuration.

bi-init.sh file used to source env , should point to the DataDirect 7.1.5 driver

Back up bi-init.sh, usually located under

/u01/BIUAT/OBIEE/instances/instance1/bifoundation/OracleBIApplication/coreapplication/setup

Make edit to bi-init.sh.

Modify the EPM_ODBC_DRIVER_DIR parameter to point it the location of the DD 7.1.5 ODBC driver library directory.

   For example: EPM_ODBC_DRIVER_DIR=$ORACLE_HOME/common/ODBC/Merant/7.1.5/lib

   Note that bi-init.sh is only needed only for running OBIEE applications directly from a shell command line.

Step 7: RPD Changes.

Create a new connection pool to connect to MS SQL database.

Disable function "NESTED_ORDERBY_SUPPORTED" in the rpd .

Step 8: Restart BI Services.

Restart all servers (AdminServer and all Managed server(s))

Enjoy and Stay Well !! :)