Upgrade to Essbase 21.8 Requires Manual ODBC Configuration

Essbase 21.8 deployment includes updated DataDirect ODBC drivers (version 8.0.2). After you apply Essbase 21.8 OPatch to an independent Essbase deployment, you need to manually update your ODBC configuration (applies on Linux and Windows).

This issue affects you if you are upgrading to Essbase 21.8 from a previous version, and you need to load external source data / dimensions to Essbase using ODBC drivers (as described in Access Data Using ODBC Connectivity).

Note:

When you apply Essbase 21.8 OPatch, older ODBC driver versions (for example, 7.1.6 or 5.3), are removed. They are unsupported for Essbase 21.8, and will not work.

Essbase 21.8 is bundled only with these DataDirect ODBC driver versions, and only these are supported: 

[ODBC Drivers]
Oracle 8.1 MongoDB
Oracle 8.0 Amazon Redshift Wire Protocol
Oracle 8.0 Apache Hive Wire Protocol
Oracle 8.0 Apache Spark SQL
Oracle 8.0 DB2 Wire Protocol
Oracle 8.0 Greenplum Wire Protocol
Oracle 8.0 Informix Wire Protocol
Oracle 8.0 MySQL Wire Protocol
Oracle 8.0 Oracle Wire Protocol
Oracle 8.0 PostgreSQL Wire Protocol
Oracle 8.0 SQL Server Wire Protocol
Oracle 8.0 Sybase IQ Wire Protocol

After you apply the Essbase 21.8 OPatch,

  • You must manually update your ODBC configuration (applies on both Linux and Windows).
  • Oracle recommends manually backing up your ODBC configurations for the older 7.1.6 drivers, in case you need to roll back to a previous Essbase version that uses those drivers.

Linux Instructions

Before you begin, make note of the location of the older 7.1.6 driver configurations on the Essbase server domain:

  • odbc.ini, located in 

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

  • odbcinst.ini, located in 

    <Oracle_HOME>/essbase/modules/oracle.essbase.datadirect.odbc/7.1.6/odbcinst.ini

  • ODBC driver libraries, located in

    <Essbase Product Home>/modules/

  1. If Essbase is running, stop the servers, as described in Start, Stop, and Check Servers.

  2. Make a backup copy of your older 7.1.6 driver configurations: odbc.ini (required), and odbcinst.ini (required if you're using third party drivers such as Teradata).

    Note:

    To avoid accidental overwrite, ensure your 7.1.6 driver configuration backups are saved somewhere outside of 
    <Essbase Product Home>/modules/

    Examples:

    cp <Domain_Home>/config/fmwconfig/essconfig/core/odbc.ini <Oracle_HOME>/odbc.ini_orig
    cp <Oracle_HOME>/essbase/modules/oracle.essbase.datadirect.odbc/7.1.6/odbcinst.ini <Oracle_HOME>/odbcinst.ini_orig

  3. Apply Essbase 21.8 OPatch (as described in Patch and Roll Back).

  4. Open odbc.ini for editing.

  5. Replace the old driver names with new ones, providing the absolute library path in the Driver name entry. For example, (replace Oracle_HOME with your system's Oracle_HOME path):

    • Change

      Driver=<Oracle_HOME>/essbase/modules/oracle.essbase.datadirect.odbc/7.1.6/lib/ARsqls27.so

      to

      Driver=<Oracle_HOME>/essbase/modules/oracle.essbase.datadirect.odbc/8.0.2/lib/ARsqls28.so

    • Change

      Driver=<Oracle_HOME>/essbase/modules/oracle.essbase.datadirect.odbc/7.1.6/lib/ARdb227.so

      to

      Driver=<Oracle_HOME>/essbase/modules/oracle.essbase.datadirect.odbc/8.0.2/lib/ARdb228.so

    • Change

      Driver=<Oracle_HOME>/essbase/modules/oracle.essbase.datadirect.odbc/7.1.6/lib/ARmysql27.so

      to

      Driver=<Oracle_HOME>/essbase/modules/oracle.essbase.datadirect.odbc/8.0.2/lib/ARmysql28.so

  6. (Optional) replace the old driver descriptions with new ones.

    • Change Oracle 7.1 SQL Server Wire Protocol to Oracle 8.0 SQL Server Wire Protocol
    • Change Oracle 7.1 DB2 Wire Protocol Driver to Oracle 8.0 DB2 Wire Protocol Driver
    • Change Oracle 7.1 MySQL Wire Protocol to Oracle 8.0 MySQL Wire Protocol

  7. Save odbc.ini.

  8. Edit the odbcinst.ini file, <Oracle_HOME>/essbase/modules/oracle.essbase.datadirect.odbc/8.0.2/odbcinst.ini.

    Example for DB2

    (replace Oracle_HOME with your system's Oracle_HOME path): 

    Driver=<Oracle_HOME>/essbase/modules/oracle.essbase.datadirect.odbc/8.0.2/Ardb228.so

    Example for Teradata

    Copying and pasting your Teradata-related entries from the backup file, <Oracle_HOME>/odbcinst.ini_orig, add information about the Teradata ODBC driver to odbcinst.ini: 

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

  9. Save odbcinst.ini.

  10. Start Essbase, as described in Start, Stop, and Check Servers.

Note:

If you ever need to roll back the Essbase 21.8 OPatch, you will also need to restore to your backup odbc.ini file (and odbcinst.ini, if using third party drivers such as Teradata). Example: 

cd <DOMAIN_HOME>/config/fmwconfig/essconfig/core
cp odbc.ini.orig odbc.ini

Windows Instructions

On Windows, previously configured system DSNs – as described in Configure the ODBC Source of Data (Windows) – will not work after applying the Essbase 21.8 OPatch. You must reconfigure System DSN to use the 8.0.2 drivers.

  1. If Essbase is running, stop the servers, as described in Start, Stop, and Check Servers.

  2. Apply Essbase 21.8 OPatch (as described in Patch and Roll Back).

  3. On the Essbase Windows server, open the Windows Registry Editor (from Start menu, run regedit).

  4. Navigate to Computer\HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI.

  5. Right click ODBC.INI and select Export. Save it as a backup, in case you need to roll back the OPatch. For example, save the configuration as odbc.orig.reg.

  6. Navigate to Computer\HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI.

  7. Right click ODBCINST.INI and select Export. Save it as a backup, in case you need to roll back the OPatch. For example, save the configuration as odbcini.orig.reg.

  8. Navigate into ODBC.INI and select essbase_datasource

  9. For the entries named Driver and Description, right click and select Modify to update the information for all data sources.

    Change

    • Oracle 7.1 SQL Server Wire Protocol

      to

      Oracle 8.0 SQL Server Wire Protocol

    • Change

      Oracle 7.1 DB2 Wire Protocol Driver

      to

      Oracle 8.0 DB2 Wire Protocol Driver

    • Change

      Oracle 7.1 MySQL Wire Protocol

      to

      Oracle 8.0 MySQL Wire Protocol

  10. On the Essbase Windows Server, navigate to <ESSBASE_PRODUCT_HOME>\modules\oracle.essbase.datadirect.odbc\8.0.2.

  11. Make a backup copy of ODBC802.reg (for example, ODBC802_orig.reg).

  12. Edit ODBC802.reg, replacing all occurrences of $ODBC_PATH with the absolute path of the ODBC drivers – for example, C:\\Oracle\\Middleware\\Oracle_Home\\essbase\\modules\\oracle.essbase.datadirect.odbc\\.

  13. Double click the edited ODBC802.reg file. This will edit the Windows registry to have entries for the DataDirect 8.0.2 drivers bundled with Essbase 21.8.

  14. Start Essbase, as described in Start, Stop, and Check Servers.

Note:

If you ever need to roll back the Essbase 21.8 OPatch, you will also need to restore to your backup ODBC.INI and ODBCINST.INI Windows registry entries. For example, delete the ODBC.INI entry from regedit, then double click odbc.orig.reg to restore the previous entry. Alternatively, you can import/merge the registry entries.