Search This Blog

Saturday 10 November 2018

OBIEE 12c with MySQL Community Edition Connectivity

This post brings the workaround on How to connect OBIEE 12c Environment with MySQL Server Community Edition.
This is Tested on OBIEE 12.2.1.3.0

Assumption :

OBIEE 12c comes with DataDirect Connections and support only for Enterprise Edition. But our requirement is to connect Community Edition as our DW is developed in there.

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

Import tables in OBIEE Admin Tool

Download latest ODBC Driver and Install, set up DSN,  , Create connection pool, Import metadata, Define business and presentation layers.

Problem:   

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

Solution:

Step1:
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 privileges to do this.  DO NOT DOWNLOAD THE ANSI driver as it does not work.  
We have to download for Redhat 64bit Server:
mysql-connector-odbc-setup-8.0.13-1.el7.x86_64.rpm

Step2:
Update the configuration in ODBC.ini
Go to the following directory and update the ODBC.ini file like below:

ORACLE_BI_HOME/domains/biee/config/fmwconfig/bienv/core

Note: We can use either OBDC Driver (libmyodbc8w) or ANSI Driver(libmyodbc8a).

[MySQLDevTest]
Driver         = /usr/lib64/libmyodbc8a.so
Description  = Connector/ODBC 8.0 ANSI Driver DSN
SERVER       = mysql.dbhostname.com
PORT          = 3306
USER          = username
Password     = password
Database     = dbname
OPTION       = 3




Step3:  
Go to the following directory and update the obis.properties file for LD_LIBRARY_PATH to point OBDC Driver.

ORACLE_BI_HOME/domains/biee/config/fmwconfig/bienv/OBIS


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"

Import Points to Note:
Connection Pool Name, ODBC.ini configuration in OBDC Data Sources name and MYSQL configuration name has to be same.

E.g: MySQLConnTest
Cheers!
Bose

4 comments:

OBIEE 12c to OAS Migration

 Hi All, I have migrated successfully few projects from BI 12c to OAS 6.4. If  anyone wants help then please reach Me! Cheers! Bose

Recent Posts