Connect to Database Data Sources

You can create, edit, and delete database connections and use the connections to create data sets from databases.

Create Database Connections

You can create connections to databases and use the connections to access data.

  1. On the Home page, click Create, then click Connection.
  2. In the Create Connection dialog, click the icon for the connection type that you want to create a connection for (for example Oracle Database).
  3. Enter a unique name for the new connection, and specify the required connection information for the database, such as the hostname, port, credentials, service name and so on.
     

    If you’re creating an SSL connection to an Oracle Database, 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.

  4. (Optional) When you connect to some database types (for example, Oracle Talent Acquisition Cloud), you might have to specify the following authentication options on the Create Connection and Inspect dialogs:
    • Select Always use these credentials, so that the login name and password you provide for the connection are always used and users aren’t prompted to log in.
    • Select Require users to enter their own credentials when you want to prompt users to enter their own user name and password for the data source. Users are required to log in see only the data that they have the permissions, privileges, and role assignments to see.
  5. If you're connecting to an on-premises database, click Use Remote Data Connectivity.
    Check with your administrator that you can access the on-premises database.
  6. Click Save.
    You can now begin creating data sets from the connection.

    You can't use remote connections to save a data set from a Data Flow.

Create Data Sets from Databases

After you create database connections, you can use those connections to create data sets.

You must create the database connection before you can create a data set for it.
  1. On the Home page click Create and click Data Set to open the Create Data Set dialog. In the Create Data Set dialog, select Create Connection and use the Create Connection dialog to create the connection for your data set.
  2. In the Data Set editor, first browse or search for and double-click a schema, and then choose the table that you want to use in the data set. When you double-click to select a table, a list of its columns is displayed.
    You can use breadcrumbs to quickly move back to the table or schema list.
  3. In the column list, browse or search for the columns you want to include in the data set. You can use Shift-click or Ctrl-click to select multiple columns. Click Add Selected to add the columns you selected, or click Add All to include all of the table's columns in the data source.
    Alternatively, you can select the Enter SQL option to view or modify the data source’s SQL statement or to write a SQL statement.
  4. You can also optionally perform the following steps:
    • After you’ve selected columns, you can go to the Step editor at the top of the Data Set editor and click the Filter step to add filters to limit the data in the data set. After you’ve added filters, click Get Preview Data to see how the filters limit the data.

    • Go to the Step editor at the top of the Data Set editor and click the last step in the Step editor to specify a description for the data source.

    • Go to the Step editor at the top of the Data Set editor and click the last step in the Step editor and go to the Refresh field to specify how you want to refresh the data in the data source. Note the following information:

      • Select Live if you want the data source to use data from the database directly rather than copying the data into the cache. Typically because database tables are large, they shouldn’t be copied to Data Visualization's cache.

      • If your table is small, then select Auto and the data is copied into Data Visualization’s cache if possible. If you select Auto, you must refresh the data when it’s stale.

  5. Click Add. The View Data Source page is displayed.
  6. In the View Data Source page you can optionally view the column properties and specify their formatting. The column type determines the available formatting options.

Edit Database Connections

You can edit the database connection details.

  1. In the Data page, click Connections.
  2. Select the connection you want to edit and click Action menu or right-click, then select Inspect.
  3. In the Inspect dialog, edit the connection details.
  4. Click Save.

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.

You must provide a unique Connection Name. If a connection with the same name already exists in your system, an error message is displayed. You can’t see or edit the current password for your connection. If you need to change it, you must create a connection that uses the same password.

Delete Database Connections

You can delete a database connection. 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 data sets, then you must delete the data sets before you can delete the connection.

  1. Go to the Data page and select Connections.
  2. Select the connection that you want to delete and click Actions menu or right-click, then click Delete.
  3. Click Yes.

About Specifying Connections to Databases

Some database types (for example, Oracle Talent Management Cloud) require you to specify additional configuration options.

When you connect to some database types, you might have to specify the following authentication options on the Create Connection and Edit Connection dialogs:

  • Enable Bulk Replication - If you’re loading a data set for a data visualization project, 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

    • Select Always use these credentials, so that the login name and password you provide for the connection are always used and users aren’t prompted to log in.

    • Select Require users to enter their own credentials when you want to prompt users to enter their own user name and password for the data source. Users required to log in see only the data that they have the permissions, privileges, and role assignments to see.