1. REST API for Oracle Essbase
  2. Tasks
  3. Application Connections

Create Application Connection

post

/essbase/rest/v1/applications/{applicationName}/connections

Creates an application-level connection based on specified inputs. name and type are required inputs for all types of connections. Other required inputs differ based on the type of the connection. You must be an application manager, or a power user with application management permission to the specified application.

A connection stores information about an external server and the login credentials that are required to access it. By defining one connection that can be used by multiple processes and artifacts, you can simplify many aspects of your analytics.

If you are creating a connection to Autonomous Data Warehouse, see also Upload Application Connection Wallet File.

Request

Supported Media Types
Path Parameters
Body ()

Connection details.

Root Schema : connection
Type: object
Show Source

  • Optional. If type is DB and you are configuring Essbase to use a generic JDBC driver, provide the fully qualified class name of the JDBC driver. For example, oracle.jdbc.driver.OracleDriver.

  • URL to an external RDBMS database, or, discovery URL to an Essbase instance. For connections to other Essbase instances, this parameter is an alternative to providing the host and port. Example of Essbase discovery URL https://192.0.2.1:443/essbase/agent. For examples of other uses, see documentation for global Get Connection endpoint.

  • A descriptive string for this connection.

  • Host server name or IP. Required for Oracle Database connections. Required for Essbase connections, unless dbURL is used instead.

  • links

  • Maximum connection pool size. Default is 50. If you get connection errors you may need to adjust minimum and maximum connection pool sizes. See About Controlling the Pool Size in UCP in Universal Connection Pool Developer's Guide.

  • Minimum connection pool size. Default is 5. If you get connection errors you may need to adjust minimum and maximum connection pool sizes. See About Controlling the Pool Size in UCP in Universal Connection Pool Developer's Guide.

  • A name for this connection.

  • Required. Password of the user ID with authorization to access the remote source for this connection.

  • Required for file type connections. Catalog path to the file source of data. Example: /gallery/Technical/Drill Through/DrillthroughDS.csv

  • Port number on the remote host. Required for connections when host is given.

  • Set to true if you are using an Autonomous Data Warehouse connection which is already available (a repository connection). In this case, you do not need to upload a wallet.

  • Service name, if you are defining an Oracle Database connection.

  • The Oracle System ID (SID) that uniquely identifies an Oracle Database. Required for Oracle Database connections unless service is used instead.

  • Allowed Values: [ "TEMPLATE", "EXCELFILE", "DB", "DELIMITEDFILE", "FIXEDWIDTHFILE", "ESSBASE", "JDBC", "SPARK", "MS_SQL", "MYSQL", "DB2", "ORACLE", "FILE" ]

    The type of external source. Supported sources and versions are listed in the Database section of the certification matrix (Platform SQL table).

  • Allowed Values: [ "FILE", "DB", "ESSBASE" ]

    Required. Type of connection. FILE to connect to a file on the server, DB to connect to an external source system, or ESSBASE to connect to another cube.

  • Required. User ID with authorization to access the remote source for this connection.

  • Path to a wallet file, if required for your connection to Autonomous Data Warehouse (if repoWallet = false). Example: /system/wallets/EssbaseADWS. Obtain a wallet file by selecting Download Client Credentials (Wallet) from your Autonomous Data Warehouse Administration page in Oracle Cloud Infrastructure. If you are using a connection which is already available (a repository connection), you do not need to upload a wallet.

Back to Top

Response

Supported Media Types

200 Response

OK

Connection created successfully.

400 Response

Bad Request

Failed to create connection.

Back to Top

Examples

The following example shows how to create an application-level connection.

This example uses cURL to access the REST API from a Windows shell script. The calling user's ID and password are variables whose values are set in properties.bat.

Script with cURL Command

call properties.bat
curl -X POST "https://myserver.example.com:9001/essbase/rest/v1/applications/Sample/connections" -H Accept:application/json -H Content-Type:application/json --data "@./app_conn_details.json" -u %User%:%Password%

The cURL example above delivers a JSON payload in app_conn_details.json. The details you include in the payload determine what kind of connection is created. See below for examples to create connections to different sources supported by Essbase.

Sample JSON Payload - Oracle Database

The following sample JSON payload, passed to REST API in app_conn_details.json, is an example for creating a connection to Oracle Database. The required parameters are name, type, subtype,host, port, user, password, and either sid or service. 

{
  "name" : "OracleDB",
  "type" : "DB",
  "subtype" : "ORACLE",
  "host" : "dbhostname.example.com",
  "port" : 1521,
  "user" : "essbase",
  "password" : "cGE1NXdvcmQx",
  "sid" : "orcl"
}

Sample JSON Payload - Oracle Database Autonomous Data Warehouse

The following sample JSON payload, passed to REST API in app_conn_details.json, is an example for creating a connection to Oracle Autonomous Data Warehouse. The required parameters are name, type, subtype, user, password, and service. 

{
  "name" : "OracleADW",
  "type" : "DB",
  "subtype" : "ORACLE",
  "walletPath" : "/system/wallets/Sample",
  "repoWallet" : "false",
  "user" : "essbase",
  "password" : "cGE1NXdvcmQx",
  "service" : "adwsql_low"
}

Sample JSON Payload - File

The following sample JSON payload, passed to REST API in app_conn_details.json, is an example for creating a connection to a file in the catalog. The required parameters are name, type, and path. 

{
  "name" : "fileConn",
  "type" : "FILE",
  "path" : "/shared/Dim_Year.csv"
}

Sample JSON Payload - Essbase

The following sample JSON payload, passed to REST API in app_conn_details.json, is an example for creating a connection to another Essbase cube. The required parameters are name, type, host + port (OR dbURL), user, and password. 

{
  "name" : "essConn",
  "type" : "ESSBASE",
  "dbURL" : "https://esscube.example.com:9001/essbase/agent",
  "user" : "admin",
  "password" : "cGE1NXdvcmQx"
}

Sample JSON Payload - IBM DB2

The following sample JSON payload, passed to REST API in app_conn_details.json, is an example for creating a connection to DB2. The required parameters are name, type, subtype, dbDriver, host + port+ service (OR dbURL), user, and password. 

{
  "name" : "DB2Conn",
  "type" : "DB",
  "subtype" : "DB2",
  "dbDriver" : "com.oracle.bi.jdbc.db2.DB2Driver",
  "host" : db2host.example.com",
  "port" : 50000,
  "user" : "user1",
  "password" : "cGE1NXdvcmQx",
  "service" : "dbname"
}

Alternate Payload for DB2

The following sample JSON payload for creating a connection to DB2 does not require host, port, and service, because you provide that information in dbURL. 

{
  "name" : "DB2Conn",
  "type" : "DB",
  "subtype" : "DB2",
  "dbURL" : "jdbc:oracle:db2://db2host.example.com:50000;DatabaseName=dbname",
  "dbDriver" : "com.oracle.bi.jdbc.db2.DB2Driver",
  "user" : "user1",
  "password" : "cGE1NXdvcmQx"
}

Sample JSON Payload - Microsoft SQL Server

The following sample JSON payload, passed to REST API in app_conn_details.json, is an example for creating a connection to Microsoft SQL Server. The required parameters are name, type, subtype, dbDriver, host + port (OR dbURL), user, and password. 

{
  "name" : "MSSQLConn",
  "type" : "DB",
  "subtype" : "MS_SQL",
  "dbDriver" : "com.oracle.bi.jdbc.sqlserver.SQLServerDriver",
  "host" : "mssqlhost.example.com",
  "port" : 1433,
  "user" : "user1",
  "password" : "cGE1NXdvcmQx"
}

Alternate Payload for Microsoft SQL Server

The following sample JSON payload for creating a connection to Microsoft SQL Server does not require host and port, because you provide that information in dbURL. 

{
  "name" : "MSSQLConn"
  "type" : "DB",
  "subtype" : "MS_SQL",
  "dbURL" : "jdbc:oracle:sqlserver://mssqlhost.example.com:1433",
  "dbDriver" : "com.oracle.bi.jdbc.sqlserver.SQLServerDriver",
  "user" : "user1",
  "password" : "cGE1NXdvcmQx"
}

Sample JSON Payload - MySQL

The following sample JSON payload, passed to REST API in app_conn_details.json, is an example for creating a connection to MySQL. The required parameters are name, type, subtype, dbDriver, host + port (OR dbURL), user, and password. 

{
  "name" : "MySQLConn",
  "type" : "DB",
  "subtype" : "MYSQL",
  "dbDriver" : "com.oracle.bi.jdbc.mysql.MySQLDriver",
  "host" : "mysqlhost.example.com",
  "port" : 3306,
  "user" : "user1",
  "password" : "cGE1NXdvcmQx"
}

Alternate Payload for MySQL

The following sample JSON payload for creating a connection to MySQL does not require host and port, because you provide that information in dbURL. 

{
  "name" : "MySQLConn"
  "type" : "DB",
  "subtype" : "MYSQL",
  "dbURL" : "jdbc:oracle:sqlserver://mysqlhost.example.com:3306",
  "dbDriver" : "com.oracle.bi.jdbc.mysql.MySQLDriver",
  "user" : "user1",
  "password" : "cGE1NXdvcmQx"
}

Sample JSON Payload - Spark

The following sample JSON payload, passed to REST API in app_conn_details.json, is an example for creating a connection to Spark. The required parameters are name, type, subtype, dbDriver, host + port (OR dbURL), user, and password. 

{
  "name" : "SparkConn",
  "type" : "DB",
  "subtype" : "SPARK",
  "dbDriver" : "com.oracle.bi.jdbc.sparksql.SparkSQLDriver",
  "host" : "sparkhost.example.com",
  "port" : 10000,
  "user" : "user1",
  "password" : "cGE1NXdvcmQx"
}

Alternate Payload for Spark

The following sample JSON payload for creating a connection to Spark does not require host and port, because you provide that information in dbURL. 

{
  "name" : "SparkConn"
  "type" : "DB",
  "subtype" : "SPARK",
  "dbURL" : "jdbc:oracle:sparksql://sparkhost.example.com:10000",
  "dbDriver" : "com.oracle.bi.jdbc.sparksql.SparkSQLDriver",
  "user" : "user1",
  "password" : "cGE1NXdvcmQx"
}

Sample JSON Payload - Oracle Database (JDBC)

The following sample JSON payload, passed to REST API in app_conn_details.json, is an example for creating a connection to Oracle Database using JDBC. To use this type of connection, you must first configure Essbase to use generic JDBC drivers, as described in Create Connections and Datasources for Generic JDBC Drivers. The required parameters are name, type, subtype, dbURL, dbDriver, user, and password. 

{
  "name" : "oracle_jdbc",
  "type" : "DB",
  "subtype" : "JDBC",
  "dbURL" : "jdbc:oracle:thin:@dbhost.example.com:1521/orclpdb.example.com",
  "dbDriver" : "oracle.jdbc.driver.OracleDriver",
  "user" : "user1",
  "password" : "cGE1NXdvcmQx"
}

Sample JSON Payload - Teradata (JDBC)

The following sample JSON payload, passed to REST API in app_conn_details.json, is an example for creating a connection to Teradata using JDBC. To use this type of connection, you must first configure Essbase to use generic JDBC drivers, as described in Create Connections and Datasources for Generic JDBC Drivers. The required parameters are name, type, subtype, dbURL, dbDriver, user, and password. 

{
  "name" : "tera_jdbc_conn",
  "type" : "DB",
  "subtype" : "JDBC",
  "dbURL" : "jdbc:teradata://10.x.x.x/TBC",
  "dbDriver" : "com.teradata.jdbc.TeraDriver",
  "user" : "TBC",
  "password" : "cGE1NXdvcmQx"
}
Back to Top