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.
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.
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.
10 comments:
Thanks - you're a life saver
Cheers
Tony H
Thanks a lot!
You can find the latest information at:
DataDirect ODBC Driver for SQL Server
DataDirect JDBC Driver for SQL Server
How can we achieve this obiee 12? I have placed till step 2, not the server is crashing. Can you please help me.
Don't know - never tried it on OBI12 . If its crashing there will be a logfile or coredump worth looking at though.
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.
Any idea why on 12c we get
Code: 12010. [nQSError: 12010] Communication error connecting to remote end point: address = bihost; port = 9514. (HY000)
for both MySql and Postgre, while it's OK for Oracle?
In BI 12c, Unicode driver is not connecting. ANSI Driver is connecting with MySQL fine. Try below link:
http://allaboutobiee.blogspot.com/2018/11/mysql-community-edition-connectivity.html
Post a Comment