1. User’s Guide for Oracle Analytics Desktop
  2. Connect to Data Sources
  3. Manage Connections to Data Sources

Manage Connections to Data Sources

You can create, update, remove, and share connections to data sources.

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

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.

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.