Create Connections and Datasources
Before you can create connections to external source data from Essbase, you must get the connection details such as host names, user names, passwords, and any other service credentials from your system administrator.
Create a Global Connection
To create a global connection (accessible to multiple applications),
-
Log in to the Essbase web interface as a service administrator.
-
Click Sources.
-
Click Create Connection and select the source you need to connect to. Sources and versions included with Essbase are listed in the Database section of the certification matrix (see the Platform SQL table). If you want to use your own preferred JDBC driver that you will upload, refer for details to Create Connections and Datasources for Generic JDBC Drivers.
-
Complete the connection details and save the connection. Next, create one or more Datasources that use the connection. The input details vary depending on the source type.
Create an Application-level Connection
To create an application-level connection (accessible to only that application),
-
Log in to the Essbase web interface as an application manager, or as a power user with application management permission to the specified application.
-
On the Applications page, click the Actions menu to the right of the application name, and click Inspect.
-
Click the Sources tab.
-
Click Create Connection and select the source you need to connect to. Supported sources and versions included with Essbase are listed in the Database section of the certification matrix (see the Platform SQL table). If you want to use your own preferred JDBC driver that you will upload, refer for details to Create Connections and Datasources for Generic JDBC Drivers.
-
Complete the connection details and save the connection. Next, create one or more Datasources that use the connection. The input details vary depending on the source type.
Source-Specific Workflows
For some source-specific workflows, see the following topics:
Create a Connection and Datasource to Access Oracle Database
Define a connection and Datasource between Essbase and Oracle Database.
If applicable, use one of the following subtopics instead of this one:
Create a Connection and Datasource for Oracle Autonomous Data Warehouse
Define a connection and Datasource between Essbase and Autonomous Data Warehouse.
If you will create a federated partition between Essbase and Autonomous Data Warehouse Serverless, use the following topic instead of this one: Create a Connection for Federated Partitions.
To create a global connection, you need to have the service administrator role. To create an application level connection, you need to have user role, plus application manager permission on the application.
Create a Connection and Datasource to Access Another Cube
Define a connection and Datasource between two Essbase cubes (on different instances).
Create a Connection and Datasource to Access a Data File
Define a connection and Datasource between Essbase and a source data file.
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 bothsqljdbc41.jar
andsqljdbc42.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 thedrivers
directory (not a subfolder). -
Create connections to the JDBC drivers.
-
In the Essbase web interface,
- to create a global connection: on the Sources page, click Connections.
- to create an application-level connection: from the Actions menu to the right of an application name, launch the inspector and click Sources.
-
Click Create Connection and select JDBC.
To find the JDBC driver, Essbase looks in the
drivers
folder. If nojar
files are found, Essbase returns a Class Not Found (or failure to load driver) error when you test the connection. -
In the Create Connection screen,
-
Provide a name for the JDBC connection. For example, OrclJDBC.
-
In the URL field, provide the JDBC connection string. For example,
jdbc:oracle:thin:@myserver:1521:orcl
. 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.
-
For User and Password fields, enter the credentials for a user who is authorized to access the database.
-
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:
-
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 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:@myhost.example.com:1521/orcl.esscs.myhost.example.com
-
The following example uses Service Name with long syntax.
jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(host=myhost.example.com)(protocol=tcp)(port=1521))(CONNECT_DATA=(SERVICE_NAME=orcl.example.com)))
-
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.
-
-
Click Test to validate the connection, and if successful, click Create.
-
Verify that the connection was created successfully and appears in the list of connections.
-
-
Create Datasources over the generic JDBC driver connections.
-
Click Datasources, and click Create Datasource.
-
From the Connection drop-down box, select the name of the connection you just created; for example, SAMPLE.OrclJDBC. Application-level connections are prefixed with the application name, in the format appName.connectionName.
-
Provide a name for the Datasource; for example, OrclJDBC_DS.
-
Optionally enter a description of the Datasource; for example, SAMPLE_BASIC_TABLE on Oracle Database.
-
In the Query field, provide the appropriate SQL query that selects the data you want to make available in this Datasource.
-
Click Next. If the SQL statement was correct to query a table, you should see the queried columns populated.
-
Change any numeric columns to Double, and click Next.
-
Change any additional source-specific parameters, if applicable, and click Next. For information about parameter use, see Implement Parameters for Datasources.
-
Review the preview panel. You should see the results of the query fetching columns of data from the external source.
-
If the preview looks correct, click Create to finish creating the Datasource.
-
More Connection Examples for Generic JDBC Drivers
These examples illustrate using Essbase to connect to non-Oracle JDBC sources of data, using drivers you uploaded to the Essbase Server.
The following examples are for non-Oracle sources. To create an Oracle Database connection using a generic JDBC driver, see Create Connections and Datasources for Generic JDBC Drivers.
JDBC Connection Example for DB2
In the Create Connection screen,
-
Provide a name for the JDBC connection. For example, DB2conn.
-
In the URL field, provide the JDBC connection string. For example,
jdbc:db2://myhostname02.example.com:50000/TBC
. Obtain the JDBC connection string from the JDBC provider. -
For User and Password fields, enter the credentials for a user who is authorized to access the database.
-
In the Driver field, provide the fully qualified class name of the JDBC driver. For example,
com.ibm.db2.jcc.DB2Driver
.
JDBC Connection Example for MySQL
In the Create Connection screen,
-
Provide a name for the JDBC connection. For example, MySQLconn.
-
In the URL field, provide the JDBC connection string. For example,
jdbc:mysql://myhostname03.example.com:3306/tbc
. Obtain the JDBC connection string from the JDBC provider. -
For User and Password fields, enter the credentials for a user who is authorized to access the database.
-
In the Driver field, provide the fully qualified class name of the JDBC driver. For example,
com.mysql.jdbc.Driver
.
JDBC Connection Example for SQL Server
In the Create Connection screen,
-
Provide a name for the JDBC connection. For example, MSSQLConn.
-
In the URL field, provide the JDBC connection string. For example,
jdbc:sqlserver://myhostname04.example.com:1433
. Obtain the JDBC connection string from the JDBC provider. -
For User and Password fields, enter the credentials for a user who is authorized to access the database.
-
In the Driver field, provide the fully qualified class name of the JDBC driver. For example,
com.microsoft.sqlserver.jdbc.SQLServerDriver
.
JDBC Connection Example for Teradata
In the Create Connection screen,
-
Provide a name for the JDBC connection. For example, TeraDconn.
-
In the URL field, provide the JDBC connection string. For example,
jdbc:teradata://myhostname05.example.com/DBS_PORT=1025
. Obtain the JDBC connection string from the JDBC provider. -
For User and Password fields, enter the credentials for a user who is authorized to access the database.
-
In the Driver field, provide the fully qualified class name of the JDBC driver. For example,
com.teradata.jdbc.TeraDriver
.