OBIEE and Vertica

[kaja78]

UPDATE:

By default Oracle BI on Linux/HP-UX uses Data Direct ODBC driver manager. The above configuration works, but it serializes database requests. This means if you have multiple Oracle BI users working in parallel they are blocking each other. We were unable to find out how to prevent this behaviour. The Vertica ODBC driver should work with data direct...

But we found out this workaround: Use unixODBC for comunication between Oracle BI server and external ODBC datasource (Vertica).

For OBI 10g on HP-UX we used the following process (for 11g you will have to play with opmn.xml ...):

1) Install unixODBC
2) Edit /usr/local/odbc/config/odbcinst.ini

Code: Select all

[VerticaDriver]
Description=Vertica driver
Driver=/opt/vertica/client-7.0.1/lib64/libverticaodbc.so
[ODBC]
Threading=1
#Trace=yes
#TraceFile=/tmp/unixODBC.out

3) Create separate /oracle/product/OracleBI/setup/unixODBC.vertica.ini

Code: Select all

[Driver]
DriverManagerEncoding=UTF-16
ErrorMessagesPath=/opt/vertica/client-7.0.1/lib64
#LogLevel=4
#LogPath=/tmp/unixODBC.vertica.out
ODBCInstLib=/usr/local/odbc/unixODBC-2.3.2/odbcinst/.libs/libodbcinst.so

4) Create separate /oracle/product/OracleBI/setup/unixODBC.odbc.ini

Code: Select all

[ODBC]
#Trace=yes
#TraceFile=/tmp/unixodbctrace.out
InstallDir=/usr/local/odbc/unixODBC-2.3.2/lib

[ODBC Data Sources]
Vertica=Vertica Analytics Platform

[Vertica]
Description=VERTDB Database
Driver=/opt/vertica/client-7.0.1/lib64/libverticaodbc.so
Database=VERTDB
Servername=XXX
UserName=
DriverStringConversions=NONE
Label=OracleBI

5) Just before you start the BI server using run-sa.sh start64 change your environment to use unixODBC (do not start presentation services from the same session. Presentation services should still use datadirect to communicate with Oracle BI server):

Code: Select all

export LD_LIBRARY_PATH=/usr/local/odbc/unixODBC-2.3.2/DriverManager/.libs:/usr/local/odbc/unixODBC-2.3.2/odbcinst/.libs:/usr/local/odbc/unixODBC-2.3.2/cur/.libs
export ODBCINI=/oracle/product/OracleBI/setup/unixODBC.odbc.ini
export VERTICAINI=/oracle/product/OracleBI/setup/unixODBC.vertica.ini

* We have left the Vertica DSN also in odbc.ini. It seems that BI server still reads this file ignoring ODBCINI env variable...
6) Now launch the BI server.
7) Define the Vertica datasource in Repository Administrator. Use ODBC 2.0