Configuring Database Connections Using Native ODBC Drivers

Oracle BI EE bundles UNIX ODBC drivers for some data sources, but not all.

For these data sources, including Teradata and Oracle TimesTen In-Memory Database, you must install your own ODBC driver, then update the obis.properties and odbc.ini files to configure the data source.

If you are using Teradata, see Avoiding Spool Space Errors for Queries Against Teradata Data Sources.

See Creating or Changing Connection Pools.

1. Open the obis.properties file, located in:

   BI_DOMAIN/config/fmwconfig/bienv/obis

2. Locate the LD_LIBRARY_PATH variable, use the following information:

   • For Solaris, Linux, and HP-UX, the library path variable is LD_LIBRARY_PATH.

   • For AIX, the library path variable is LIBPATH.

   For example, to set the library path variable for the driver on Linux:

   LD_LIBRARY_PATH=$ORACLE_HOME/opt/teradata/client/14.10/odbc_64/lib,
   $ORACLE_HOME/bi/bifoundation/web/bin,
   $ORACLE_HOME/clients/epm/Essbase/EssbaseRTC/bin,
   $ORACLE_HOME/bi/bifoundation/odbc/lib,
   $ORACLE_INSTANCE,
   $ORACLE_HOME/lib 
   

3. If necessary, update the LD_LIBRARY_PATH variable to include the driver path.

   To update the variable for the driver on Linux, review the following example:

   LD_LIBRARY_PATH=$ORACLE_HOME/opt/teradata/client/14.10/odbc_64/lib,
   $ORACLE_HOME/bi/bifoundation/server/bin,
   $ORACLE_HOME/bi/bifoundation/web/bin,
   $ORACLE_HOME/clients/epm/Essbase/EssbaseRTC/bin,
   $ORACLE_HOME/bi/bifoundation/odbc/lib,
   $ORACLE_INSTANCE,
   $ORACLE_HOME/lib
   

4. In obis.properties, locate the PATH variable and if necessary, include the DataDirect driver path.
5. Save and close the file.
6. Open the odbc.ini file. You can find this file at:

   BI_DOMAIN/config/fmwconfig/bienv/core

7. Create an entry for the database, ensuring that the ODBC connection name is identical to the data source name specified in the connection pool defined in the repository.

   Ensure that you set the Driver parameter to the file name and location of the native ODBC driver for the database, with the library suffix that is appropriate for the operating system, for example, .so for Solaris and AIX, or .sl for HP-UX.

   The following example provides details for a Teradata data source on Linux, with a data source name of Tera_Northwind.

   [Tera_Northwind]
   Driver=/opt/teradata/client/14.10/odbc_64/lib/tdata.so
   Description=NCR 3600 running Teradata V2R6.2
   DBCName=10.345.67.899
   astUser=
   Username=northwind
   Password=northwind
   Database=northwind
   DefaultDatabase=northwind
   NoScan=no
   

   If you have selected the option Require fully qualified table names in the General tab of the Connection Pool dialog for this data source in the Administration Tool, the DefaultDatabase parameter does not require a value.

8. In the odbc.ini file, add an entry to the section ODBC Data Sources with the details appropriate for the data source.

   The following example provides details for a Teradata data source with a data source name of Tera_Northwind.

   Tera_Northwind=tdata.so
   

9. Restart OBIS1.
10. Using the Administration Tool, open the repository and add the new DSN you created as the Connection Pool Data source name for the appropriate physical databases.

Defining Dimension Tables as Not Normalized in Oracle RPAS ODBC Data Sources on AIX UNIX

After configuring the database connection for the Oracle RPAS ODBC data source, use the rdaadmin tool to define dimension tables as not normalized at run time.

1. Locate the rdaadmin client tool in the following location:

   /bin/rdaadmin

2. Run the rdaadmin client tool by typing the following command:

   rdaadmin

3. Enter appropriate text when prompted, as follows:

   • DATABASE: [Oracle_RPAS_database_name]

     The database name must match the name given for the Data Source Name in the previous task (for example, RPAS Sample).

   • ADDRESS: [ip_address]

   • PORT: [port_number]

     An example port number value is 1707.

   • CONNECT_STRING: [NORMALIZE_DIM_TABLES=NO]

     This value treats dimension tables as not normalized at run time.

   • TYPE: []

   • SCHEMA_PATH: []

   • REMARKS: []

4. Declare the RPAS environment variable OPENRDA in the Oracle BI Server session on UNIX.

   For example, declare the variable using the 64 bit rdaadmin client tool as follows:

   OPENRDA_INI=/rpasclient64/config/raix/openrda.ini export OPENRDA_INI