1. Connecting Oracle Analytics Cloud to Your Data
  2. Connecting Oracle Analytics Cloud to Your Data
  3. Connect to Data
  4. Manage Connections to Data Sources

Manage Connections to Data Sources

You can create, update, remove, and share connections to data sources. As an Oracle Analytics user with DV Content Author access, you can perform these actions.

Topics:

Create a Connection to a Data Source

You can create a connection to enable you to analyze data in that data source.

  1. On the Home page, click Create, and then click Connection.
  2. In the Select Connection Type dialog box, click the icon for the connection type that you want. For example, Oracle Database.
  3. Enter the required connection information such as host, port, username, password, and service name.
  4. Optional: Select an Authentication option for your connection..
    • Always use these credentials - Oracle Analytics always uses the login name and password you provide for the connection. Users aren’t prompted to log in.
    • Require users to enter their own credentials - Oracle Analytics prompts users to enter their own user name and password for the data source. Users can only access the data for which they have the permissions, privileges, and role assignments.
    • (Displayed if Oracle Analytics supports impersonation for this database type) Use the active user’s credentials - Oracle Analytics doesn't prompt users to sign in to access the data. The same credentials they used to sign in to Oracle Analytics are also used to access this data source.
  5. If you're connecting to a remote database, click Use Remote Data Connectivity.
    Check with your administrator that you can access the remote database.
  6. If you want to use these connection details in Semantic Modeler or Model Administration Tool, click System connection. See Database Connection Options.
  7. Click Save.
    You can now begin creating workbooks or datasets using this connection. For example, from the Home page, click Create, then click Dataset, then select the connection that you just created.

Edit a Data Source Connection

You can update a data source's connection details.

If you’re editing an SSL connection to an Oracle Database and you need to use a new cwallet.sso file, in the Client Wallet field, click Select to browse for the cwallet.sso file. Ask your administrator for the location of the cwallet.sso file.

  1. On the Home page click the Navigator, select Data, and then click Connections.
  2. Hover over the connection that you want to edit. To the right of the highlighted connection, click Actions, then select Inspect.
  3. In the Inspect dialog, edit the connection details.
    You can’t see the current password or Logical SQL for connections. If you need to change these, create a new connection.
  4. Click Save.

Delete a Data Source Connection

You can remove a data source connection from Oracle Analytics Cloud. For example, you must delete a database connection and create a new connection when the database's password has changed.

If the connection contains any datasets, then you must delete the datasets before you can delete the connection.

  1. Go to the Data page and select Connections.
  2. Hover over the connection that you want to delete. To the right of the highlighted connection, click Actions, then select Delete.
  3. Click Yes.

Share a Data Source Connection

You can assign access permissions to the data source connections that you create or administer.

  1. On the Home page click the Navigator. Click Data, and then click Connections.
  2. Hover over the connection that you want to share, click Actions, then select Inspect.
  3. Click Access, and use the tabs to grant access:

    • All - Share the connection with individual users or roles.

    • Users - Share the connection with individual users.

    • Roles - Share the connection with application roles (for example, BI Consumer), so that all users with those roles can use the connection.

  4. Use the Add box to search for and select a user or role.
    The user or role is displayed in the list below with the default privileges Read-Only.
  5. To change the default privileges, select one of the following:
    • Full Control - The user or role can use the connection to create datasets, and modify, rename, or delete the connection. They can also change the privileges for the connection.
    • Read-Write - The user or role can use the connection to create datasets, and modify or rename the connection (but not delete it).
    • Read-Only - The user or role can use the connection to create datasets, but not change the connection details.
  6. Click Save.
When users next log in, they can use connections that you've shared to visualize data from this database.

Database Connection Options

When you specify connection details using the Create Connection dialog or Inspect dialog, some database types have extra configuration options.

General Options

  • When you create connections to Oracle Databases, you can connect in two ways using the Connection Type option:
    • Basic - Specify the Host, Port, and Service Name of the database.
    • Advanced - In the Connection String field specify the Single Client Access Name (SCAN) ID of databases running in a RAC cluster. For example:

      sales.example.com =(DESCRIPTION= (ADDRESS_LIST= (LOAD_BALANCE=on)(FAILOVER=ON) (ADDRESS=(PROTOCOL=tcp)(HOST=123.45.67.111)(PORT=1521)) (ADDRESS=(PROTOCOL=tcp)(HOST=123.45.67.222)(PORT=1521)) (ADDRESS=(PROTOCOL=tcp)(HOST=123.45.67.333)(PORT=1521))) (CONNECT_DATA=(SERVICE_NAME= salesservice.example.com)))

  • Enable Bulk Replication - If you’re loading a dataset for a workbook, then this option should be turned off and you can ignore it. This option is reserved for data analysts and advanced users for replicating data from one database to another database.

Authentication Options

  • Always use these credentials - Oracle Analytics always uses the login name and password you provide for the connection. Users aren’t prompted to log in.
  • Require users to enter their own credentials - Oracle Analytics prompts users to enter their own user name and password for the data source. Users can only access the data for which they have the permissions, privileges, and role assignments.
  • (Displayed if Oracle Analytics supports impersonation for this database type) Use the active user’s credentials - Oracle Analytics doesn't prompt users to sign in to access the data. The same credentials they used to sign in to Oracle Analytics are also used to access this data source.

System connection

Select System connection if you want to use these connection details in Semantic Modeler or Model Administration Tool.

  • If you're modeling data using Semantic Modeler, choosing this option enables Semantic Modeler to connect to the data source. To find out which databases Semantic Modeler supports, look for a Yes in the 'Use in Semantic Modeler' column in the supported data sources list. See Supported Data Sources.
  • If you're modeling data using Model Administration Tool, you can connect to some data sources by copying connection details from Oracle Analytics Cloud into Model Administration Tool. To find out which data sources support this, look for System Connection in the Connectivity Options in the Use in Model Administration Tool in Supported Data Sources. Click Copy, to copy the connection's Object ID. Business modelers can paste in the Object ID to identify and use a data connection in the Model Administration Tool (Connection Pool dialog).

Click Copy, to copy the connection's Object ID. Business modelers can paste in the Object ID to identify and use a data connection in the Model Administration Tool (Connection Pool dialog).

Note: If you don't click System connection when you initially create the connection, if you later want data modelers to use the connection details, then you have to create a new connection and select System connection. In other words, you can't edit the connection later and select this option.

Database Connection Limits

Understand your database connection requirements before your create the database connection.

The maximum number of database tables displayed in Oracle Analytics is 10,000. If you need additional tables, Oracle recommends that you ask your database administrator to create a database user with access to the specific objects you want to analyze and specify that user's credentials when you create the database connection.

Connect to Data With Upper, Lower, or Mixed-case Characters

If you're connecting to an Oracle database, Oracle Autonomous Data Warehouse, Oracle Transaction Processing, Snowflake, SQL Server, or My SQL, you can change the default quoting identifier so that you can read data with upper, lower, or mixed-case characters in table or column names.

For example, you might choose double quotes as the quoting identifier. Oracle Analytics then adds double quotes to the underlying SQL statement select "EfG_Field" from "AbCd"; instead of issuing select EfG_Field from AbCd;, (which would fail).
  1. On the Home page, click Create and then click Connection.
  2. Click one of the database types that support advanced properties.
    Supported databases are Oracle, Oracle Autonomous Data Warehouse, Snowflake, and My SQL.
  3. Specify the connection details then save the connection.
  4. On the home page, click the Navigator, then click Data, and then Connections.
  5. Hover over the connection that you saved in Step 2 and click Actions then click Inspect.
  6. Click Advanced, then use the Quoting Identifiers option to select the quoting identifiers used in the database.
    Description of inspect-connection-advanced-tab.png follows
    Description of the illustration inspect-connection-advanced-tab.png

    For example, you might select Double Quotes (" "). Oracle Analytics adds double quotes to the underlying SQL statement select "EfG_Field" from "AbCd"; instead of issuing select EfG_Field from AbCd;.

    Note:

    You won't see the Advanced option if the connection was created with the System connection option selected. Advanced options aren't supported for system connections.
  7. Click Save.

Manage Connections Using REST APIs

You can use Oracle Analytics Cloud REST APIs to programmatically manage connections to a range of data sources. For example, you might want to create a script that creates (or modifies) the same set of connections in both your test and production Oracle Analytics Cloud environments.

About Connection REST APIs

You can create, update, and delete connections for a range of data sources using REST APIs. This topic lists the types of data source connections you can manage with REST APIs.

Note:

REST API for Oracle Analytics Cloud provides detailed information about each REST API. See Connection REST Endpoints.

Supported Data Sources

  • Oracle Database
  • Oracle Autonomous Data Warehouse
  • Oracle Essbase
  • MySQL
  • PostgreSQL
  • Snowflake
  • SQL Server
  • Vertica

Connection Parameters

The connection parameters required for each data source are different. If you want to use the REST API to create or update a connection, you must know the JSON payload format required for your data source. See Sample JSON Payloads for Data Sources.

Typical Workflow for Managing Connections Using REST APIs

Here are the common tasks to start using Oracle Analytics Cloud REST APIs to programmatically manage connections. If you’re using the REST APIs for the first time, follow these tasks as a guide.

Task Description REST API Documentation
Understand prerequisites

Understand and complete several prerequisite tasks.

You must have permissions to create workbooks and connect to data in Oracle Analytics Cloud to manage data connections using REST APIs (DV Content Author).

Prerequisites
Understand OAuth 2.0 token authentication Authentication and authorization in Oracle Analytics Cloud is managed by Oracle Identity Cloud Service. To access the Oracle Analytics Cloud REST APIs, you need an OAuth 2.0 access token to use for authorization. OAuth 2.0 Token Authentication
Understand supported data sources Read the topic that describes the types of data connections you can manage with REST APIs. Supported Data Sources
Determine JSON payload formats Read the topic that describes the JSON payload format for your data source and obtain the required connection parameters. Sample JSON Payloads for Data Sources
Create a connection Create a data connection for use in workbooks, reports, and dashboards. Create a connection (Preview)
Update a connection Update one or more properties of an existing data connection. Update a connection (Preview)
Delete a connection Delete a data connection. Delete a connection (Preview)

How to Use REST APIs to Manage Data Source Connections

Use these samples and examples to help you manage data source connections with REST API requests using cURL.

Sample cURL Format

Use the following cURL command format to create or update data source connections using REST APIs:
  • Simple JSON
    curl 
--header "Authorization: Bearer <token>" 
--header "Content-Type: application/json" 
--request POST|PUT https://<hostname>/api/20210901/catalog/connections
--data "<data source connection payload>"
  • Multi-part/Form data
    curl 
--header "Authorization: Bearer <token>" 
--request POST|PUT https://<hostname>/api/20210901/catalog/connections
--form "cert=<security wallet file>"
--form "connectionParams=<data source connection payload>"
Where:

  • <token> - OAuth 2.0 bearer token required to authenticate calls to Oracle Analytics Cloud REST APIs. See OAuth 2.0 Token Authentication.

  • <hostname> - Host where Oracle Analytics Cloud is running.

  • <data source connection payload> - Data source-specific connection information. See Sample JSON Payloads for Data Sources.

  • <security wallet file> - Stores SSL-related information such as authentication and signing credentials, private keys, certificates, and trusted certificate. Required for some connection types, such as Oracle Database with SSL and Oracle Autonomous Data Warehouse (Mutual TLS).

Examples

The following examples show you how to create a connection to Oracle Autonomous Data Warehouse (ADW).

  • Example 1 - Create a wallet-less (TLS) connection to Oracle ADW
  • Example 2 - Create a connection to Oracle ADW that uses a credentials wallet file cwallet.sso (Mutual TLS)

Additional examples are available in REST API for Oracle Analytics Cloud. See Create a connection (Preview), Update a connection (Preview) and Delete a connection (Preview).

Note:

The JSON payloads in these examples are specific to Oracle ADW. The JSON payload format is different for other data sources. See Sample JSON Payloads for Supported Data Sources.

Example 1 Create a wallet-less (TLS) connection to Oracle ADW

In this example, you create a connection named oracle_adw_walletless. The request body includes simple JSON application/json. 
curl 
  --header "Authorization: Bearer <token>"
  --header "Content-Type: application/json" 
  --request POST https://example.com/api/20210901/catalog/connections 
  --data "{
     "version": "2.0.0",
     "type": "connection",
     "name": "oracle_adw_walletless",
     "description": "Sample Oracle ADW connection without a wallet created using Connections API",
     "content": {    
       "connectionParams": {
           "connectionType": "oracle-autonomous-data-warehouse",
           "connectionString": "(description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1521)(host=adb.us-ashburn-1.oraclecloud.com))(connect_data=(service_name=abcdefg1hijkl2m_adwwalletless_high.adb.oraclecloud.com))(security=(ssl_server_dn_match=yes)))",
           "username": "ADMIN",
           "password": "<<password>>",
           "systemConnection": false,        
           "remoteData": false,
           "sslType": "ServerSideSSL"
       }
     }
   }"

Response Body

{"connectionID":"J0FkbWluJy4nb3JhY2xlX2Fkd193YWxsZXRsZXNzJw=="}

Make a note of the Base64 encoded connectionId in the response body. Later on, you can use this value to update or delete the connection.

Example 2 Create a connection to Oracle ADW that uses a wallet file (Mutual TLS)

In this example, you create a connection named oracle_adw_with_wallet. The request body includes multipart/form-data, that is, requires both a wallet file from Oracle ADW (cwallet.sso) and Oracle ADW connection parameters.
curl
 --header "Authorization: Bearer <token>"
 -–request POST https://<hostname>/api/20210901/catalog/connections 
 --form "cert=@"/Users/scott/Downloads/Wallet_adw/cwallet.sso"" 
 --form "connectionParams= "{
    "version": "2.0.0",
    "type": "connection",
    "name": "oracle_adw_with_wallet",
    "description": "Sample Oracle ADW connection with a wallet created using Connections API",   
    "content": {
      "connectionParams": {
        "connectionType": "oracle-autonomous-data-warehouse",
        "connectionString": "(description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps) (port=1522)(host=adb.us-ashburn-1.oraclecloud.com))(connect_data=(service_name=abcdefg1hijkl2m_walletadw_high.adwc.oraclecloud.com/))(security=(ssl_server_dn_match=yes)))",
        "username": "ADMIN",
        "password": "<<password>>",
        "remoteData": "false",   
        "systemConnection": false,
        "sslType": "ClientSideSSL"    
       }
      }
    }"

Response Body

{"connectionID":"J2FkbWluJy4nb3JhY2xlX2Fkd193aXRoX3dhbGxldCc="}

Make a note of the Base64 encoded connectionId in the response body. Later on, you can use this value to update or delete the connection.

Sample JSON Payloads for Data Sources

To connect to data source you provide connection parameters. When you use the Connections REST API, you specify connection parameters in a JSON payload format. Use this table to determine the JSON payload for the data source you want to connect to.

Data Source Request Type Input Payload

Oracle Database

(Non-SSL)

 application/json

Basic Connection Type

{ 
 "version": "2.0.0", 
 "type": "connection",  
 "name": "oracle_db_non_ssl_basic",
 "description": "Sample non-SSL Oracle Database connection created using Connections API",
 "content": {
   "connectionParams": {
     "connectionType": "oracle-database",
     "host": "example.com",
     "port": "1521",
     "serviceName": "orcl",
     "username": "admin",
     "password": "<password>",
     "remoteData": false,
     "systemConnection": false
   }
  }
 }

Advanced Connection Type

{
  "version": "2.0.0",
  "type": "connection",
  "name": "oracle_db_non_ssl_advanced ",
  "description": " Sample non-SSL Oracle Database connection created with the advanced connection string format using Connections API",
  "content": {
    "connectionParams": {
     "connectionString": "(DESCRIPTION= (ADDRESS_LIST= (LOAD_BALANCE=on)(FAILOVER=ON) (ADDRESS=(PROTOCOL=tcp)(HOST=example.com)(PORT=1521))) (CONNECT_DATA=(SERVICE_NAME=ORCLPDB1)))",
     "username": "admin",
     "password": "<password>",
     "connectionType": "oracle-database",
     "remoteData": false,
     "systemConnection": false
    }
  }
}

Oracle Database with SSL

 multi-part/form-data 
cwallet.sso (client credentials file)

Basic Connection Type

cert: <cwallet.sso file location>
connectionParams: {
 "version": "2.0.0",
 "type": "connection",
 "name": "oracle_db_ssl",
 "description": "Sample Oracle Database connection with SSL created using Connections API",
 "content": {
   "connectionParams": {
     "connectionType": "oracle-database",
     "host": "example.com",
     "port": "2484",
     "serviceName": "ORCLPDB1",
     "username": "admin",
     "password": "<password>",
     "systemConnection": false,
     "remoteData": false
   }
  }
}

Oracle Autonomous Data Warehouse - Wallet-less (TLS)

 application/json

Basic Connection Type

{
 "version": "2.0.0",
 "type": "connection",
 "name": "oracle_adw_walletless_basic",
 "description": "Sample Oracle ADW connection without a wallet created using Connections API",
 "content": {
   "connectionParams": { "connectionType": "oracle-autonomous-data-warehouse",
     "connectionString": "(description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1521) (host=example.com)) (connect_data=(service_name=example.com)) (security=(ssl_server_dn_match=yes)))",
     "username": "admin",
     "password": "<password>",
     "systemConnection": false,
     "remoteData": false,
     "sslType": "ServerSideSSL"
     }
   }
 }

Oracle Autonomous Data Warehouse - Wallet (Mutual TLS)

 multipart/form-data 
cwallet.sso (client credentials file)

Basic Connection Type

cert: <cwallet.sso file location>

connectionParams: {
 "version": "2.0.0",
 "type": "connection",
 "name": "oracle_adw_with_wallet",
 "description": "Sample Oracle ADW connection with wallet created using Connections API",
 "content": {
   "connectionParams": {
     "connectionType": "oracle-autonomous-data-warehouse",
     "connectionString": "(description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)
                        (port=1522)(host=example.com))
                        (connect_data=(service_name=example.com))
                        (security=(ssl_server_dn_match=yes)))",
     "username": "admin",
     "password": "<password>",
     "remoteData": "false",
     "systemConnection": false,
     "sslType": "ClientSideSSL"
    }
  }
 }

PostgreSQL

(Non-SSL)

 application/json

Basic Connection Type

{
 "version":"2.0.0",
 "type": "connection",
 "name": "postgres_non_SSL",
 "description": "Sample PostgreSQL connection created using Connections API",
 "content": {
   "connectionParams": {
     "connectionType": "postgresql-database",
     "host": "example.com",
     "port": "5432",
     "serviceName": "postgres",
     "username": "admin",
     "password": "<password>",
     "remoteData": false,
     "systemConnection": false 
    }
  }
}

PostgreSQL with SSL

 application/json

Basic Connection Type

{
 "version":"2.0.0",
 "type": "connection",
 "name": "postgres_SSL_Conn",
 "description": "Sample PostgreSQL connection with SSL created using Connections API",
 "content": {
   "connectionParams": {
     "connectionType": "postgresql-database",
     "host": "example.com",
     "port": "5432",
     "serviceName": "postgres",
     "username": "admin",
     "password": "<password>",
     "sslType":"ServerSideSSL",
     "remoteData": false,
     "systemConnection": false
    }
  }
}

SQL Server

(Non-SSL)

 application/json

Basic Connection Type

{
 "version":"2.0.0",
 "type": "connection",
 "name": "SqlServer_non_ssl",
 "description": "Sample non-SSL SQL Server connection created using Connections API",
 "content": {
   "connectionParams": {
     "connectionType": "sqlserver-database",
     "host": "example.com",
     "port": "1400",
     "serviceName": "sqlserver1",
     "username": "admin",
     "password": "<password>",
     "remoteData": false,
     "systemConnection": false
    }
  }
}
SQL Server with SSL application/json

Basic Connection Type

{
 "version":"2.0.0",
 "type": "connection",
 "name": "SqlServer_ssl",
 "description": "Sample SQL Server connection with SSL created using the Connections API",
 "content": {
   "connectionParams": {
     "connectionType": "sqlserver-database",
     "host": "example.com",
     "port": "60190",
     "serviceName": "sqlserver1",
     "username": "admin",
     "password": "<password>",
     "sslType":"ServerSideSSL",
     "remoteData": false,
     "systemConnection": false
    }
  }
}

MySQL

(Non-SSL)

 application/json

Basic Connection Type

{
 "version":"2.0.0",
 "type": "connection",
 "name": "MySql_no_SSL",
 "description": "Sample MySQL connection created using the Connections API",
 "content": {
   "connectionParams": {
     "connectionType": "mysql-database",
     "host": "example.com",
     "port": "3307",
     "serviceName": "mysql1",
     "username": "admin",
     "password": "<password>",
     "remoteData": false,
     "systemConnection": false
    }
 }
}
MySQL with SSL application/json

Basic Connection Type

{
 "version":"2.0.0",
 "type": "connection",
 "name": "MySql_ssl",
 "description": "Sample MySQL connection with SSL created using Connections API",
 "content": {
   "connectionParams": {
     "connectionType": "mysql-database",
     "host": "example.com",
     "port": "3306",
     "serviceName": "mysql1",
     "username": "admin",
     "password": "<password>",
     "sslType":"ServerSideSSL",
     "remoteData": false,
     "systemConnection": false
    }
  }
}

Oracle Essbase

 application/json

Basic Connection Type

{
 "version":"2.0.0",
 "type": "connection",
 "name": "Oracle_Essbase",
 "description": "Sample Oracle Essbase connection created using Connections API",
 "content": {
   "connectionParams": {
     "connectionType": "oracle-essbase",
     "dsn":"example.com",
     "username":"admin",
     "password":"<password>",
     "remoteData": false,
     "systemConnection": false,
     "authentication":"current"/"private"/"sso"
    }
  }
}