Create Connections and Datasources for Generic JDBC Drivers

Use this workflow to enable Essbase to connect to any JDBC source of data, using drivers you upload to the Essbase Server.

If you are the Essbase deployment administrator, you can configure Essbase to use your preferred drivers you upload to the Essbase server machine. Oracle has tested Essbase JDBC connectivity using Oracle drivers. To use JDBC drivers from other vendors, check the driver documentation for requirements on specifying the URL and credentials of your JDBC data source. For any performance-related steps, refer to the vendor JDBC documentation.

Note:

Ensure that the JDBC driver you use with Essbase honors the setFetchSize method for controlling memory used while processing the result set. For optimal performance of data load and dimension build processes, Essbase fetches 1000 records per network call.

To configure Essbase to use generic JDBC drivers,

Connect to the Essbase server machine using SSH.
Manually create a drivers directory in <Essbase Product Home> on the server instance.

Ensure drivers is all lower case, as the path is case sensitive.
From your vendor site, download the JDBC driver JARs you want to use.

The Oracle Database JDBC driver supported by Essbase is ojdbc8.jar.

If you use Autonomous Data Warehouse, you need to download the full archive (ojdbc8-full.tar.gz) containing the Oracle JDBC Thin driver and companion JARs.
Upload the JDBC driver jars to the drivers directory on the Essbase instance.

Upload only one version of each database driver to the drivers directory. For example, do not upload both sqljdbc41.jar and sqljdbc42.jar, or else Essbase will use the older one (as it appears first in CLASSPATH).

If you use Autonomous Data Warehouse, extract the archive (ojdbc8-full.tar.gz) and move all of the contents directly into the drivers directory (not a subfolder).
Create connections to the JDBC drivers.
1. In the Essbase web interface, click Sources, then Connections.
  
  Or, to define the connection and Datasource at application level instead of globally, start on the Applications page instead of the Sources page, click an application name, and then click Sources.
2. Click Create Connection and select JDBC.
  
  To find the JDBC driver, Essbase looks in the drivers folder. If no jar files are found, Essbase returns a Class Not Found (or failure to load driver) error when you test the connection.
3. In the Create Connection screen,
  1. Provide a name for the JDBC connection. For example, Oracle JDBC.
  2. In the URL field, provide the JDBC connection string. For example, jdbc:oracle:thin:@myserver.example.com:1521/orclpdb.example.com. Obtain the JDBC connection string from the JDBC provider.
    
    The syntax format above applies only for Oracle Database. See More Connection Examples for Generic JDBC Drivers if you are working with other providers.
  3. For User and Password fields, enter the credentials for a user who is authorized to access the database.
  4. In the Driver field, provide the fully qualified class name of the JDBC driver. For example, oracle.jdbc.driver.OracleDriver.
  For Oracle drivers, specify the URL using the following syntax guidelines:
  - If Oracle Database is registered with a listener, you can use Service Name in the URL instead of the SID, using short syntax jdbc:oracle:thin:@<host>:<port>/<servicename>. Example:
```
jdbc:oracle:thin:@myserver.example.com:1521/orclpdb.example.com
```
  - The following example uses Service Name with long syntax.
```
jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(host=myserver.example.com)(protocol=tcp)(port=1521))(CONNECT_DATA=(SERVICE_NAME=orclpdb.example.com)))
```
  - To use the Oracle System ID (SID) that uniquely identifies the database, use the syntax jdbc:oracle:thin:@<host>:<port>:<SID>. For example,
```
jdbc:oracle:thin:@myhost:1521:orcl
```
  - If you are using Autonomous Data Warehouse, in the URL syntax, you must include the TNS_ADMIN environment variable specifying the path to the wallet. The wallet can be anywhere on the Essbase server machine, but you must provide the full path, using syntax like jdbc:oracle:thin:@database_service_name?TNS_ADMIN=walletpath.
    
    Linux Example
```
jdbc:oracle:thin:@adwsql_low?TNS_ADMIN=/scratch/oracle_home/dist/essbase/drivers/adwConn
```
    Windows Example
```
jdbc:oracle:thin:@adwsql_low?TNS_ADMIN="C:\\Oracle123\\Middleware\\Oracle_Home\\essbase\\drivers\\adwConn"
```
    Example on an OCI Deployment
```
jdbc:oracle:thin:@adwsql_low?TNS_ADMIN=/u01/data/essbase/catalog/users/firstname.lastname@example.com/adwconn
```
  The examples above work only for Oracle Database. See More Connection Examples for Generic JDBC Drivers if you are working with other providers.
4. Click Test to validate the connection, and if successful, click Create.
5. Verify that the connection was created successfully and appears in the list of connections.
Create Datasources over the generic JDBC driver connections.
1. Click Datasources, and click Create Datasource.
2. From the Connection drop-down box, select the name of the connection you just created; for example, Oracle JDBC. Application-level connections are prefixed with the application name, in the format appName.connectionName.
3. Provide a name for the Datasource; for example, OracleDB_Datasource.
4. Optionally enter a description of the Datasource; for example, SAMPLE_BASIC_TABLE on Oracle Database.
5. In the Query field, provide the appropriate SQL query that selects the data you want to make available in this Datasource.
6. Click Next. If the SQL statement was correct to query a table, the queried columns are populated.
7. Change any numeric columns to Double, and click Next.
8. Change any additional source-specific parameters, if applicable, and click Next. For information about parameter use, see Implement Parameters for Datasources.
9. Review the preview panel. You should see the results of the query fetching columns of data from the external source.
10. If the preview looks correct, click Create to finish creating the Datasource.