Connecting MySQL to OBIEE

Tested on OBIEE 11.1.1.6BP6

Similar problem to connecting SQL Server to OBIEE

Assumption : MySQL Community Edition ( i.e. FREE edition )
Assumption : Server OS is Redhat 5 64-bit

How do we connect our client ?

Straightforward. Download latest ODBC driver, set up DSN,  ( remember to give it a meaningful name ),  create connection pool, import metadata, define business and presentation layers.


How do we connect our server ?

1. Problem set :   Data Direct Drivers are shipped to connect OBIEE to MySQL.  Unfortunately these do not work as you get a message "[nQSError: 16001] ODBC error state: S1000 code: 0 message: [DataDirect][ODBC MySQL Wire Protocol driver]Connections to MySQL Community Servers are not supported.  Please contact MySQL to obtain a MySQL Enterprise or Commercial version.."

Which I think is appalling but there you are.

How to solve this ?

Use MySQL native ODBC instead of DataDirect ODBC.

You can get these from

http://dev.mysql.com/downloads/connector/odbc/#downloads

This is an rpm download and install, you need root to do this so go get the sysadmin to do all that no reboot and no outage needed.  DO NOT DOWNLOAD THE ANSI driver as it does not work.  You want the one called mysql-connector-odbc-5.2.3-1.rhel5.x86_64.rpm.  I

If itt says ANSI it's wrong and you'll get lots of UNICODE errros. 

2. Problem Set 2.   What entries in ODBC.ini ?

AND where, you may ask is odbc.ini location set ( hint - look at opmn.xml )

change host,DSN, uid,pwd,dbname as needed below.


[LIVE_MYSQL]
Driver       = /usr/lib64/libmyodbc5w.so
Description  = Connector/ODBC 3.51 Driver DSN
SERVER       = svr.myhost.com
PORT         = 3306
USER         = username
Password     = password
Database     = dbname
OPTION       = 3

This doesn't work -  causes nqserver to crash repeatedly. 

3.  Solution :  Add /usr/lib64 to opmn.xml 

For every mention of LIBPATH  and PATH, add /usr/lib64 to the beginning.
e.g. 

"LD_LIBRARY_PATH" value="/usr/lib64:$ORACLE_HOME/common/ODBC/Merant/6.0SP1/lib$:$ORACLE_HOME/bifoundation/server/bin$:$ORACLE_HOME/bifoundation/web/bin$:$ORACLE_HOME/clients/epm/Essbase/EssbaseRTC/bin$:$ORACLE_HOME/bifoundation/odbc/lib$:$ORACLE_INSTANCE$:$ORACLE_HOME/lib"

Remember there are 8 mentions 4 LIBPATHS and 4 paths. 

Restart the whole stack and then voila it all works, mysql data sources appear in Analytics 

Happy days. 

Regards

Chris.