1. Administering Oracle Analytics Publisher in Oracle Analytics Server
  2. Set Up Data Sources
  3. 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.

Make sure all prerequisites have been met before setting up a JDBC connection to a data source:

  • The JDBC driver for the selected database must be available to Publisher. If you're using an Oracle database or one of the DataDirect drivers provided by WebLogic Server, then the drivers must be installed in the correct location and there is no further setup required.

  • If you plan to use a different version of any of the drivers installed with WebLogic Server, then you can replace the driver file in WL_HOME\server\lib with an updated version of the file or add the new file to the front of your CLASSPATH.

    If you plan to use a third-party JDBC driver that's not installed with WebLogic Server, then you must update the WebLogic Server classpath to include the location of the JDBC driver classes.

    When the JDBC connection is defined, the administrator defines the user that Publisher uses to connect to the database. It is the responsibility of the administrator to establish security on the database to allow or disallow actions this user can take on the database schema.

    For report consumer access to data that's returned in a report, the administrator and data model developer can establish security, if needed, that can limit the data viewed by a particular Publisher user. One method for securing data returned is to use pre-process and post-process function calls to pass the xdo_username.

  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. Select Use System User. This is reserved for connections to the Oracle BI Server.
  9. Enter the user name and password required to access the data source.
  10. Optional: Enter a PL/SQL function to execute when a connection is created (Pre Process) or closed (Post Process).
  11. Optional: Specify a client certificate for secured connection.
  12. To enable Proxy Authentication, select Use Proxy Authentication.
  13. Click Test Connection.
  14. 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.
  15. 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.
  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)))

    After you enable internal SSL, suffix SSL=true to the JDBC connection string of the Oracle BI EE connection. For example, if the Oracle BI EE connection string is jdbc:oraclebi://biplatform:9514/, then enter jdbc:oraclebi://biplatform:9514/SSL=true.

  6. In the Client Certificate field, enter the absolute directory path to the wallet file, cwallet.sso uploaded earlier.
  7. Click Test Connection.
  8. Click Apply.

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