1. Using Oracle Essbase
  2. Work with Sources
  3. Create Connections and Datasources

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),

  1. Log in to the Essbase web interface as a service administrator.

  2. Click Sources.


    Sources icon in Essbase web interface

  3. 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.


    Create Connection button with connection choices: Oracle Database, Essbase, File, Spark, DB2, SQL Server, MySQL, and JDBC

  4. 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),

  1. Log in to the Essbase web interface as an application manager, or as a power user with application management permission to the specified application.

  2. On the Applications page, click the Actions menu to the right of the application name, and click Inspect.

  3. Click the Sources tab.


    Sources tab in the application inspector for application named Sample. The Connections area is showing, with two connections already created: one named OracleDB and one named Essbase2.

  4. 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.

  5. 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:

  1. In Essbase, on the Sources page, click Connections.
    Or, to define the connection and Datasource at application level instead of globally, start on the Applications page instead of the Sources page. From the Actions menu to the right of an application name, launch the inspector and click Sources. The example used in this topic is an application level connection defined on Sample Basic.
  2. Click Create Connection and select Oracle Database.
  3. Enter a connection name, host, port number, user name, and password. When you enter the User name, enter the Oracle Database user name, without the role. Select SID (server ID) or Service, and enter server details.

    Create connection dialog for creating connection to Oracle Database. Name: OracleDB. Host: 198.51.100.165. Port: 1521. User: essbase. Password: (obscured). SID is checked and the value is orcl. Description: Connection to Oracle Database

  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.
    Next, you will create a Datasource for the Oracle Database connection.
  6. Click Datasources, and click Create Datasource.
  7. From the Connection drop-down box, select the name of the connection you just created; for example, SAMPLE.OracleDB. Application-level connections are prefixed with the application name, in the format appName.connectionName.
  8. Provide a name for the Datasource; for example, OracleDB_DS.
  9. Optionally enter a description of the Datasource; for example, SAMPLE_BASIC_TABLE on Oracle Database.
  10. In the Query field, provide the appropriate SQL query that selects the Oracle Database data you want to make available in this Datasource.

    Create Datasource dialog, General step, for creating Datasource to Oracle Database. Connection: SAMPLE.OracleDB, Name: OracleDB_DS, Description: SAMPLE_BASIC_TABLE on Oracle Database, Query: select * from SAMPLE_BASIC_TABLE

  11. Click Next. If the SQL statement was correct to query an Oracle Database area, you should see the queried columns populated.

    Create Datasource dialog, Columns step, for creating Datasource to Oracle Database. Index 1: Name: DIMENSION_PRODUCT, Type: String. Several more columns with other names are listed in order, some of type String, and some of type Double.

  12. Change any numeric columns to Double, and click Next.
  13. Change any additional source-specific parameters, if applicable, and click Next. For information about parameter use, see Implement Parameters for Datasources.
  14. Review the preview panel. You should see the results of the SQL query fetching columns of data from Oracle Database.

    Create Datasource dialog, Preview step, for creating Datasource to Oracle Database. Preview data from Oracle Database is shown, with values under the column names.

  15. If the preview looks correct, click Create to finish creating the Datasource.

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.

  1. In Essbase, on the Sources page, click Connections.
    To define the connection and Datasource at application level, instead of globally, start on the Applications page instead of the Sources page. From the Actions menu to the right of an application name, launch the inspector and click Sources.
  2. Click Create Connection and select Oracle Database.
  3. Select Autonomous using the toggle switch.

    Image of the Create Connection dialog box, showing how to create a connection from Essbase to Oracle Autonomous Data Warehouse

  4. Enter a connection name.
  5. Select a service name.
  6. If needed, drag and drop a wallet file, or click the Wallet File field to upload one.

    If you are using a connection which has already been made available to you (a repository connection), you do not need to upload a wallet, because it should already be in the repository. Select the Repository Database option.


    Repository Database option selected

    If you need to upload a wallet, obtain a wallet file by selecting Download Client Credentials (Wallet) from your Autonomous Data Warehouse Administration page in Oracle Cloud Infrastructure.

  7. Enter your Autonomous Data Warehouse username, password, and optionally, a description.
  8. Click Test to validate the connection, and if successful, click Create.

    If you get connection errors, you may need to expand Advanced Options to adjust the minimum and maximum connection pool sizes.


    Advanced Options with Min Pool Size 5, Max Pool Size 50

    See About Controlling the Pool Size in UCP in Universal Connection Pool Developer's Guide.

  9. Verify that the connection was created successfully and appears in the list of connections.
  10. Next, you will create a Datasource for the Autonomous Data Warehouse connection. Click Datasources, and click Create Datasource.
  11. From the Connection drop-down box, select the name of the connection you just created; for example, EssbaseADW. For application-level Datasources, select the application-level connection name, in the format appName.connectionName.
  12. Provide a name for the Datasource; for example, ADW_DS.
  13. Optionally enter a description of the Datasource; for example, Autonomous Data Warehouse Datasource.
  14. In the Query field, provide the appropriate SQL query that selects the Autonomous Data Warehouse data you want to make available in this Datasource.
  15. Click Next. If the SQL statement was correct to query an Autonomous Data Warehouse area, you should see the queried columns populated.
  16. Change any additional source-specific parameters, if applicable, and click Next.
  17. Review the preview panel. You should see the results of the SQL query fetching columns of data from Autonomous Data Warehouse.
  18. If the preview looks correct, click Create to finish creating the Datasource.

Create a Connection and Datasource to Access Another Cube

Define a connection and Datasource between two Essbase cubes (on different instances).

  1. In Essbase, on the Sources page, click Connections.
    Or, to define the connection and Datasource at application level instead of globally, start on the Applications page instead of the Sources page. From the Actions menu to the right of an application name, launch the inspector and click Sources. The example used in this topic is an application level connection defined on Sample Basic.
  2. Click Create Connection and select Essbase.
  3. Enter a connection name; for example, Essbase2.
  4. Either enter the Host and Port information, or check the box to Use URL. Connection information can be provided by your Service Administrator.

    Create connection dialog for creating a connection to another Essbase instance. Name: Essbase2. Use URL is checked. URL is https://192.0.2.1:443/essbase/agent. Host and Port are blank because Use URL is checked. User: admin. Password (obscured). Description: Connection to Essbase instance 2

    If you are using the URL, use the discovery URL format. A discovery URL is the URL provided by your Service Administrator, with /agent appended to the end. For example: 

    https://192.0.2.1:443/essbase/agent
  5. Click Test to validate the connection, and if successful, click Create.
  6. Verify that the connection was created successfully and appears in the list of connections.
    Next, you will create a Datasource for the Essbase connection.
  7. Click Datasources, and click Create Datasource.
  8. From the Connection drop-down box, select the name of the connection you just created.
  9. Enter a name for the Datasource, and an optional description.
  10. Select the application and database that will be used for this Datasource.
  11. Provide a valid MDX query that selects the cube data you want to make available in this Datasource.

    Create Datasource dialog, General step, for creating Datasource to another Essbase instance. Connection: SAMPLE.Essbase2, Name: Essbase2_DS, Description: Connection to Essbase instance 2, Application: Sample, Database: Basic, MDX Query: Select {Market} on columns, {Product} on rows from Sample.basic

  12. Click Next. If the MDX syntax was correct to query the remote cube, you should see the queried columns populated.
  13. Change any numeric columns to Double, and click Next.

    Create Datasource dialog, Columns step, for creating Datasource to another Essbase instance. Index 1: Name: Product, Type: String. Index 2: Name: Market, Type: String

  14. Change any additional source-specific parameters, if applicable, and click Next.
  15. Review the preview panel. You should see the results of the MDX query fetching columns of data from the other cube.

    Create Datasource dialog, Preview step, for creating Datasource to another Essbase instance. Preview data from Essbase is shown.

  16. If the preview looks correct, click Create to finish creating the Datasource.

Create a Connection and Datasource to Access a Data File

Define a connection and Datasource between Essbase and a source data file.

  1. Upload the source data file to the file catalog on Essbase.
    If you need a sample source data file for this task flow, you can copy and paste UserDetails.csv from the gallery section of the file catalog to your application's file catalog (or reference it without moving it). It represents a data repository of 22 users, with their associated countries, cost centers, currency, managers, company, business units, and offices.
  2. In Essbase, on the Sources page, click Connections.
    Or, to define the connection and Datasource at application level instead of globally, start on the Applications page instead of the Sources page. From the Actions menu to the right of an application name, launch the inspector and click Sources. The example used in this topic is an application level connection defined on Sample Basic.
  3. Click Create Connection and select File.
  4. Enter a name for the connection; for example, UserDetails.
  5. Provide the catalog path to the source data file.
  6. Enter an optional description; for example, CSV file of user details

    Create connection dialog for creating connnection to a file in the Essbase server catalog. Name: UserDetails, Path: /gallery/Technical/Filters/UserDetails.csv, Description: CSV file of user details

  7. Click Test to validate the connection, and if successful, click Create.
  8. Verify that the connection was created successfully and appears in the list of connections.
    Next, you will create a Datasource for the file connection.
  9. Click Datasources, and click Create Datasource.
  10. From the Connection drop-down box, select the name of the connection you just created; for example, UserDetails.
  11. Enter a name for the Datasource, and an optional description.
  12. Essbase detects and enters details about the source data; for example, whether it has a header row, and is comma-delimited. Click Next.

    Create Datasource dialog, General step, for creating Datasource to a file on the Essbase server. Connection: SAMPLE.UserDetails, Name: userDetails_DS, Description: User details repository. Header Row is checked, because Essbase detected that the CSV has a header record. Delimiter is Comma, because Essbase detected that the file is comma delimited

  13. You should see the columns populated from the file source. Change any numeric columns to Double, and click Next.
  14. If the preview looks correct, click Create to finish creating the Datasource.
If you update the source file metadata (for example, to add columns), you must recreate the Datasource.

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,

  1. Connect to the Essbase server machine using SSH.

  2. 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.

  3. 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.

  4. 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).

  5. Create connections to the JDBC drivers.

    1. 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.

    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, OrclJDBC.

      2. 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.

      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.


      Create Connection dialog for JDBC driver connection. Name: OrclJDBC, URL: jdbc:oracle:thin:@myserver:1521:orcl, User: essbase, Password: (obscured), Driver: oracle.jdbc.driver.OracleDriver, Description: My Oracle JDBC 8 connection

      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.

    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.

  6. 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, SAMPLE.OrclJDBC. Application-level connections are prefixed with the application name, in the format appName.connectionName.

    3. Provide a name for the Datasource; for example, OrclJDBC_DS.

    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, you should see the queried columns 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.

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,


Create Connection dialog for JDBC driver connection. Name: DB2conn, URL: jdbc:db2://myhostname02.example.com:50000/TBC, User: myDB2User, Password: (obscured), Driver: com.ibm.db2.jcc.DB2Driver, Description: DB2 connection using JDBC drivers

  1. Provide a name for the JDBC connection. For example, DB2conn.

  2. 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.

  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, com.ibm.db2.jcc.DB2Driver.

JDBC Connection Example for MySQL

In the Create Connection screen,


Create Connection dialog for JDBC driver connection. Name: MySQLconn, URL: jdbc:mysql://myhostname03.example.com:3306/tbc, User: MySQLUsr, Password: (obscured), Driver: com.mysql.jdbc.Driver, Description: MySQL connection using JDBC driver

  1. Provide a name for the JDBC connection. For example, MySQLconn.

  2. 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.

  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, com.mysql.jdbc.Driver.

JDBC Connection Example for SQL Server

In the Create Connection screen,


Create Connection dialog for JDBC driver connection. Name: MSSQLConn, URL: jdbc:sqlserver://myhostname04.example.com:1433, User: MySQLUsr, Password: (obscured), Driver: com.microsoft.sqlserver.jdbc.SQLServerDriver, Description: SQL Server connection using JDBC driver

  1. Provide a name for the JDBC connection. For example, MSSQLConn.

  2. 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.

  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, com.microsoft.sqlserver.jdbc.SQLServerDriver.

JDBC Connection Example for Teradata

In the Create Connection screen,


Create Connection dialog for JDBC driver connection. Name: TeraDconn, URL: jdbc:teradata://myhostname05.example.com/DBS_PORT=1025, User: TeraUsr, Password: (obscured), Driver: com.teradata.jdbc.TeraDriver, Description: Teradata connection using JDBC driver

  1. Provide a name for the JDBC connection. For example, TeraDconn.

  2. 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.

  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, com.teradata.jdbc.TeraDriver.