Monday, February 4, 2013

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. 


9 comments:

Anonymous said...

thanks for share..

Dinesh dev said...
This comment has been removed by a blog administrator.
Anonymous said...
This comment has been removed by a blog administrator.
Anonymous said...

Thanks - you're a life saver

Cheers
Tony H

Andrew Fomin said...

Thanks a lot!

Anonymous said...

You can find the latest information at:

DataDirect ODBC Driver for SQL Server

DataDirect JDBC Driver for SQL Server

Anonymous said...

How can we achieve this obiee 12? I have placed till step 2, not the server is crashing. Can you please help me.

Anonymous said...

Don't know - never tried it on OBI12 . If its crashing there will be a logfile or coredump worth looking at though.

Anonymous said...

For OBIEE 12c, you need to perform the step 3 as well. But the file is obis.properties located at $ORACLE_HOME/bi/modules/oracle.bi.cam.obis/env.

Update it for PATH, LD_LIBRARY_PATH, LIBPATH

Then MySQL connection will be success.