1. Database Administrator's Guide for Oracle Essbase
  2. Work with Load Rules
  3. Define Rules that Query External Sources
  4. Access Data Using ODBC Connectivity
  5. Configure the ODBC Source of Data (Linux)

Configure the ODBC Source of Data (Linux)

To make ODBC sources of data work with Essbase on Linux, you primarily configure odbc.ini in the Essbase server domain.

Prerequisites

Before you begin, make note of the location of these files on the Essbase server domain:

  • For Linux only, odbc.ini - In Essbase 21c, odbc.ini is located in: 

    <Domain Root>/<Domain Name>/config/fmwconfig/essconfig/core

  • ODBC driver libraries - located in

    <Essbase Product Home>/modules/

If you are configuring Teradata as a source, do the following:

  1. Download the Teradata Tools and Utilities client from https://downloads.teradata.com/

  2. Install the Teradata client onto your Essbase server machine.

Configure the ODBC Source of Data

To configure your ODBC source of data, you start by editing odbc.ini (or .odbc.ini, if hidden). This is the ODBC data source configuration information file located on the Essbase server domain.

  1. Open odbc.ini in a text editor.

  2. In the section labeled

    [ODBC Data Sources]

    add/identify your source using the following syntax:

    <DSN name>=<Driver name>

    where

    • DSN name = A name you choose to identify the ODBC connection to the source of data
    • Driver name = The name of the ODBC driver to use for this connection

    For example, to add a DB2 source, add:

    [ODBC Data Sources]
TBC_DB2=Oracle 7.1 DB2 Wire Protocol

    In this release, Oracle Database uses driver version 8.0, and all other RDBMS use driver versions 7.1.

    The following example entries include DB2, SQL Server, MySQL, Oracle Database, and Teradata. For Teradata, see the last two lines (the syntax varies from the other sources).

    [ODBC Data Sources]
TBC_DB2=Oracle 7.1 DB2 Wire Protocol
TBC_SQL=Oracle 7.1 SQL Server Wire Protocol
TBC_MYSQL=Oracle 7.1 MySQL Wire Protocol
TBC_ORA=Oracle 8.0 Oracle Wire Protocol
TBC_ORA_DBAAS=Oracle 8.0 Oracle Wire Protocol
TBC_ORA_ADW=Oracle 8.0 Oracle Wire Protocol
Teradata=tdataodbc_sb64.so
$TELAPI$10.xx.xxx.211=tdataodbc_sb64.so

  3. For each DSN name you added in [ODBC Data Sources] section, add a section for it specifying the driver path (required), description (optional), and host details (required). The following example includes sections for various sources. 

    [TBC_DB2]
#Example for DB2
Driver=/scratch/username/oracle_home/essbase/modules/oracle.essbase.datadirect.odbc/7.1.6/lib/Ardb227.so
Description=Oracle 7.1 DB2 Wire Protocol Driver
Database=TBC
IpAddress=myhost.example.com
TcpPort=50000

[TBC_SQL]
#Example for SQL Server
Driver=/scratch/username/oracle_home/essbase/modules/oracle.essbase.datadirect.odbc/7.1.6/lib/ARsqls27.so
Description=Oracle 7.1 SQL Server Wire Protocol
HostName=myhost.example.com
PortNumber=1433
Database=TBC
EnableQuotedIdentifiers=1

[TBC_MYSQL]
#Example for MySQL
Driver=/scratch/username/oracle_home/essbase/modules/oracle.essbase.datadirect.odbc/7.1.6/lib/ARmysql27.so
Description=Oracle 7.1 MySQL Wire Protocol
Database=tbc
HostName=myhost.example.com
PortNumber=3306

[TBC_ORA]
#Example for standalone Oracle Database
Driver=/scratch/username/oracle_home/essbase/modules/oracle.essbase.datadirect.odbc/8.0.2/lib/ARora28.so
Description=Oracle 8.0 Oracle Wire Protocol
EnableNcharSupport=1
EncryptionLevel=1
DataIntegrityLevel=1
PortNumber=1521
SID=orcl
HostName=myhost.example.com

[TBC_ORA_DBAAS]
#Example for Oracle DBaaS
Driver=/scratch/username/oracle_home/essbase/modules/oracle.essbase.datadirect.odbc/8.0.2/lib/ARora28.so
Description=Oracle 8.0 Oracle Wire Protocol
DataIntegrityLevel=1
EncryptionLevel=1
EncryptionTypes=AES256
DataIntegrityTypes=SHA1
HostName=192.0.2.20
PortNumber=1521
EnableNcharSupport=1
ServiceName=ORCL.esscs.mydb.myhost.example.com

[TBC_ORA_ADW]
#Example for Oracle Database with TLS/SSL security
Driver=/scratch/username/oracle_home/essbase/modules/oracle.essbase.datadirect.odbc/8.0.2/lib/ARora28.so
Description=Oracle 8.0 Oracle Wire Protocol
EnableNcharSupport=1
EncryptionLevel=1
DataIntegrityLevel=1
EncryptionMethod=1
AuthenticationMethod=1
CryptoProtocolVersion=TLSv1.2,TLSv1.1,TLSv1
ValidateServerCertificate=1
TNSNamesFile=/scratch/username/oracle_home/network/admin/tbc_ora_adw/tnsnames.ora
ServerName=TBC_ORA_ADW
Truststore=/scratch/username/oracle_home/network/admin/tbc_ora_adw/cwallet.sso
Keystore=/scratch/username/oracle_home/network/admin/tbc_ora_adw/cwallet.sso

[Teradata]
Driver=/opt/teradata/client/16.20/lib64/tdataodbc_sb64.so
Description=NCR 3600 running Teradata V2R6.2
DBCName=192.xx.xx.110
astUser=
Username=xxxxxx
Password=xxxxxx
Database=TBC
DefaultDatabase=TBC
NoScan=no
MechanismName=TD2

[$TELAPI$192.0.2.110]
Driver=/opt/teradata/client/16.20/lib64/tdataodbc_sb64.so
Description=NCR 3600 running Teradata V2R6.2
DBCName=192.0.2.110
astUser=
Username=xxxxxx
Password=xxxxxx
Database=TBC
DefaultDatabase=TBC
NoScan=no
MechanismName=TD2

    Note:

    The TBC_ORA_ADW example is configured for Autonomous Data Warehouse with TLS/SSL security enabled. For any TLS/SSL configurations (including Autonomous Data Warehouse), you must provide the path to the keystore and wallet as part of the configuration. The keystore and wallet (and TNS names configuration file) are located in <Oracle Home>/network/admin. If you use Autonomous Data Warehouse, you must download the wallet from Autonomous Data Warehouse to <Oracle Home>/network/admin on the Essbase server.

    Note:

    For Teradata, DBCName is not the same as DBName used in connection strings.

    Note:

    For SQL Server, be sure to enable quoted identifiers as shown in the example.

Teradata Only - Set Environment Variables

If you are configuring Teradata as a source, you additionally need to edit the Essbase server configuration file, <Domain Root>/<Domain Name>/config/fmwconfig/essconfig/essbase/essbase.cfg.

  1. Navigate to essbase.cfg and open it in a text editor.

  2. Add LD_LIBRARY_PATH variables to specify the loation of Teradata client libraries:

    env:LD_LIBRARY_PATH /opt/teradata/client/16.20/bin
env:LD_LIBRARY_PATH /opt/teradata/client/16.20/lib64
env:LD_LIBRARY_PATH /opt/teradata/client/16.20/tbuild
env:LD_LIBRARY_PATH /opt/teradata/client/16.20/msg
env:LD_LIBRARY_PATH /opt/teradata/client/16.20/tbuild/tptapi

  3. Add the following line to the end of essbase.cfg, to configure authentication: 

    env:LD_PRELOAD /usr/lib64/libgssapi_krb5.so:/usr/lib64/libkrb5.so:/usr/lib64/libk5crypto.so:/usr/lib64/libkrb5support.so

  4. Save essbase.cfg.

  5. Stop and restart the Essbase server.

    1. Navigate to the domain tools directory, <Domain Root>/<Domain Name>/esstools/bin.

    2. Stop and restart the Essbase services by running ./stop.sh and ./start.sh, respectively.

Teradata Only - Configure odbcinst.ini

If you are configuring Teradata as a source, you additionally need to edit the odbcinst.ini file with information about the ODBC driver for Teradata.

  1. Navigate to <Oracle Home>/essbase/modules/oracle.essbase.datadirect.odbc/8.0.2/ and open odbcinst.ini in a text editor.

  2. Add information about the Teradata ODBC driver:

    Teradata=Installed
[Teradata]
Driver=/opt/teradata/client/16.20/odbc_64/lib/tdataodbc_sb64.so
APILevel=CORE
ConnectFunctions=YYY
DriverODBCVer=3.51
SQLLevel=1

  3. Save odbcinst.ini.