1. Connecting Oracle Analytics Cloud to Your Data
  2. Connecting Oracle Analytics Cloud to Your Data
  3. Connect to Data for Pixel-Perfect Reports
  4. Set Up a JDBC Connection to a Data Source

Set Up a JDBC Connection to a Data Source

You can set up a JDBC connection to a data source.

  1. From the Administration page, click JDBC Connection.
  2. Click Add Data Source.
  3. Enter a display name for the data source in the Data Source Name field. This name is displayed in the Data Source selection list in the Data Model Editor.
    You can’t create a new Oracle BI EE data source with the same name, nor can you delete the provisioned Oracle BI EE data source.
  4. Select the driver type.
  5. Select Use Data Gateway only if you want to connect to a remote data source.
    Your administrator must enable remote data connectivity and configure Data Gateway on your target on-premises database. If you select Use Data Gateway, the Database Driver Class, Use System User, Pre Process Function, Post Process Function, and Use Proxy Authentication settings aren’t available for selection or update.
  6. You can update the Database Driver Class  field if required.
  7. Enter the database connection string.

    Example connection strings:

    • Oracle database

      To connect to an Oracle database (non-RAC), use the following format for the connection string:

      jdbc:oracle:thin:@[host]:[port]:[sid]

      For example: jdbc:oracle:thin:@myhost.us.example.com:1521:prod

    • Oracle RAC database

      To connect to an Oracle RAC database, use the following format for the connection string:

      jdbc:oracle:thin:@//<host>[:<port>]/<service_name>

      For example: jdbc:oracle:thin:@//myhost.example.com:1521/my_service

    • Microsoft SQL Server

      To connect to a Microsoft SQL Server, use the following format for the connection string:

      jdbc:hyperion:sqlserver://[hostname]:[port];DatabaseName=[Databasename]

      For example: jdbc:hyperion:sqlserver://myhost.us.example.com:7777;DatabaseName=mydatabase

  8. Enter the user name and password required to access the data source.
  9. Optional: Enter a PL/SQL function to execute when a connection is created (Pre Process) or closed (Post Process).
  10. Optional: Specify a client certificate for secured connection.
    The client certificates uploaded in Upload Center are listed for selection.
  11. To enable Proxy Authentication, select Use Proxy Authentication.
  12. Click Test Connection.
  13. Optional: Enable a backup database for this connection:
    1. Select Use Backup Data Source.
    2. Enter the connection string for the backup database.
    3. Enter the user name and password for this database.
    4. Click Test Connection.
  14. Define security for this data source connection. Move the required roles from the Available Roles list to the Allowed Roles list. Only users assigned the roles in the Allowed Roles list can create or view reports from this data source.

    When you set up a JDBC connection to Oracle BI EE data source, make sure you move the BI Consumer role from the Available Roles list to the Allowed Roles list.

    If you defined a backup data source, the security settings are passed to the backup data source.

Create a Secure JDBC Connection to Oracle Autonomous Data Warehouse

You can upload a JDBC client certificate and set up an SSL based JDBC connection to a database on the cloud.

To create a secure JDBC connection to Oracle Autonomous Data Warehouse:
  1. Upload the JDBC client certificate (Oracle wallet file, cwallet.sso) to the server.
    1. From the Administration page, click Upload Center.
    2. Browse and select the Oracle wallet file, cwallet.sso.
    3. Select JDBC Client Certificate from the File Type list.
    4. Click Upload.
  2. From the Administration page, click JDBC Connection.
  3. Click Add Data Source.
  4. Specify the following details for the connection:
    • Data Source Name: DBaaSConnection
    • Driver Type: Oracle 12c
    • Database Driver Class: oracle.jdbc.OracleDriver
  5. Enter the JDBC connection string.

    Use TCPS strings. For example, jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=server_name)(PORT=port))(CONNECT_DATA=(SERVICE_NAME=serviceName)))

    If you are using PAC (Private Access Channel), add (ENABLE=broken) to the DESCRIPTION parameter in the connect string. For example, jdbc:oracle:thin:@(DESCRIPTION=(ENABLE=broken)(ADDRESS=(PROTOCOL=tcps)(HOST=server_name)(PORT=port))(CONNECT_DATA=(SERVICE_NAME=serviceName)))

  6. From the Client Certificate list, select the wallet file, cwallet.sso uploaded earlier.
  7. Click Test Connection.
  8. Click Apply.

Set Up a JDBC Connection to an On-premises Data Source

You can set up a JDBC connection to an on-premises data source by using a data gateway agent.

Ensure that your administrator configures Data Gateway on your target on-premises database and enables data connectivity. See Overview to Connecting to On-premises Data Sources.

  1. Enable Data Gateway in Console:
    1. From the Analytics Cloud Home page, click Console.
    2. Click Remote Data Connectivity.
    3. Enable the Enable Data Gateway option.
    4. Select and enable the data gateway agent you want to use.
  2. From the BI Publisher Administration page, click JDBC Connection.
  3. Click Add Data Source.
  4. Enter a display name for the data source in the Data Source Name field. This name is displayed in the Data Source selection list in the Data Model Editor.
  5. From the Driver Type list, select the driver for the database you want to connect. For example, select Oracle 12c for Oracle Database.
  6. Select Use Data Gateway.

    When you select Use Data Gateway, the following settings aren’t available for selection or update.

    • Database Driver Class (Default: oracle.jdbc.OracleDriver)
    • Use System User
    • Pre Process Function
    • Post Process Function
    • Client Certificate
    • Use Proxy Authentication
  7. Enter the connection string for the database.
  8. Enter the user name and password required to access the data source.
  9. Click Test Connection.
  10. (Optional) Enable a backup database for this connection:
    1. Select Use Backup Data Source.
    2. Enter the connection string for the backup database.
    3. Enter the user name and password for this database.
    4. Click Test Connection.
  11. Define security for this data source connection. Move the required roles from the Available Roles list to the Allowed Roles list. Only users assigned the roles in the Allowed Roles list can create or view reports from this data source.

    If you have defined a backup data source, the security settings are passed to the backup data source.

Set Up Connection to a Snowflake Data Warehouse

Configure connection to a Snowflake data warehouse to use a Snowflake database as a data source.

  1. From the Administration page, click JDBC Connection.
  2. Click Add Data Source.
  3. Enter a display name for the data source in the Data Source Name field. This name is displayed in the Data Source selection list in the Data Model Editor.
  4. Select Snowflake as the driver type.
  5. In the Database Driver Class field, use the default net.snowflake.client.jdbc.SnowflakeDriver.
  6. In the Connection String field, enter the following string:

    jdbc:snowflake://accountName.snowflakecomputing.com;db=database name);warehouse=(warehouse name);schema=(schema name);

    If you want other properties for the connection, add the properties separated by semicolon (; ) as shown in the example.

    Example: jdbc:snowflake://hw11692.us-central1.gcp.snowflakecomputing.com;db=SNOWFLAKE_SAMPLE_DATA;warehouse=COMPUTE_WH;useProxy=true;proxyHost=www-proxy-adcq7-new.us.oracle.com;proxyPort=80

  7. Enter the user name and password required to access the data source.
  8. Optional: Enter a PL/SQL function to execute when a connection is created (Pre Process) or closed (Post Process).
  9. Optional: Specify a client certificate for secured connection.
    The client certificates uploaded in Upload Center are listed for selection.
  10. To enable Proxy Authentication, select Use Proxy Authentication.
  11. Click Test Connection.
  12. Define security for this data source connection. Move the required roles from the Available Roles list to the Allowed Roles list. Only users assigned the roles in the Allowed Roles list can create or view reports from this data source.

Set Up Connection to a Vertica Data Warehouse

Configure connection to a Vertica data warehouse to use a Vertica database as a data source.

  1. From the Administration page, click JDBC Connection.
  2. Click Add Data Source.
  3. Enter a display name for the data source in the Data Source Name field. This name is displayed in the Data Source selection list in the Data Model Editor.
  4. Select Vertica as the driver type.
  5. In the Database Driver Class field, use the default com.vertica.jdbc.Driver.
  6. In the Connection String field, enter the following string:

    jdbc:vertica://[host_name]:[port_number]/[service_name]

  7. Enter the user name and password required to access the data source.
  8. Optional: Enter a PL/SQL function to execute when a connection is created (Pre Process) or closed (Post Process).
  9. Optional: Specify a client certificate for secured connection.
    The client certificates uploaded in Upload Center are listed for selection.
  10. To enable Proxy Authentication, select Use Proxy Authentication.
  11. Click Test Connection.
  12. Define security for this data source connection. Move the required roles from the Available Roles list to the Allowed Roles list. Only users assigned the roles in the Allowed Roles list can create or view reports from this data source.