Connect Oracle Analytics Cloud to Your On-Premises Database

For Oracle Analytics Cloud to access your on-premises database, you must set up a connection between them using Remote Data Connector.

About Setting Up On-Premises Database Connections

If you want to connect Oracle Analytics Cloud to your on-premises database, you must deploy Remote Data Connector on Oracle WebLogic Server in your on-premises environment and configure connection details.

This diagram illustrates Remote Data Connector deployment. The set up process includes steps to configure your HTTP server and load balancer. If you’re not familiar with this type of configuration, ask your networking team to help you.

You need to complete several steps to set up the connection.

  1. Download Remote Data Connector from Oracle Technology Network and deploy the Remote Data Connector WAR file in Oracle WebLogic Server in your on-premises environment.

  2. In Oracle WebLogic Server, set up a JDBC data source for your on-premises database.

  3. Download the public key required to access Oracle Analytics Cloud and deploy the public key in your on-premises Oracle WebLogic Server.

  4. With the help of a network administrator, configure your HTTP server and load balancer to give Oracle Analytics Cloud access to Remote Data Connector, and verify that Remote Data Connector can connect to Oracle Analytics Cloud through the on-premises firewall.

  5. Download Oracle Analytics Cloud Developer Client Tool from Oracle Technology Network to your local environment.

  6. Use Oracle Analytics Cloud Developer Client Tool to update connection pool settings in the data model you migrated to Oracle Analytics Cloud with information about Remote Data Connector.

  7. Test that the Remote Data Connector URL you enter in the connection pool is accessible through the internet.

After including Remote Data Connector information in the data model (the RPD file) that you migrated to Oracle Analytics Cloud, the analyses and dashboards you migrated from Oracle BI EE 11g display data from your on-premises database.

Before You Begin

Before you begin setting up the on-premises database connection:

  • Discuss your requirement to connect your on-premises environment to Oracle Analytics Cloud with the networking team in your organization.

  • Download Oracle Analytics Cloud Remote Data Connector 1.3.

  • Download Oracle Analytics Cloud Developer Client Tool.

    Download the version that matches your Oracle Analytics Cloud service. For example, if your service is patched to 18.3.3, download Oracle Analytics Cloud Developer Client Tool 18.3.3.

  • Ensure you have the correct permissions:

    • Oracle WebLogic Server where Remote Data Connector is deployed: Admin role

    • Oracle Analytics Cloud: BI Service Administrator application role

Download and Install Remote Data Connector

You download Remote Data Connector from Oracle Technology Network (OTN).

  1. Navigate to the OTN download page for Oracle Analytics Cloud.
    http://www-content.oracle.com/technetwork/middleware/oac/downloads/index.html
  2. Click Accept License Agreement.
  3. Click OAC Remote Data Connector <version>.
    The obi-remotedataconnector-<version>.zip file is downloaded to your system.
  4. Extract the WAR file for your web server type to your web server machine.
    • For Oracle Weblogic Server, extract the obi-remotedataconnector-weblogic-<version>.war file.
    • For Apache Tomcat, extract the obi-remotedataconnector-tomcat-<version>.war file.

Deploy the Remote Data Connector WAR File

You deploy the Remote Data Connector WAR file (obi-remotedataconnector-weblogic-<version>.war) in its own domain in Oracle WebLogic Server in your on-premises environment.

  1. In Oracle WebLogic Server, create a new domain for Remote Data Connector.
    Oracle recommends that you create a new domain for Remote Data Connector to ensure that you don’t have other applications deployed with Remote Data Connector.

    Note:

    Only use an existing domain if no other applications are deployed on the domain. Deploying other applications on the same domain as Remote Data Connector creates a security risk in the other applications deployed on that domain.
  2. In Oracle WebLogic Server, log into the domain on which you’re deploying Remote Data Connector, and in the Change Center pane click Lock & Edit.
  3. In the Domain Structure, select Deployments, and display the Control pane.
  4. In the Deployments list, click Install.
  5. In the Install Application Assistant, click the upload your file(s) link, click the Choose File button for the Deployment Archive, and select the obi-remotedataconnector-weblogic-v1-2.war file you downloaded.
  6. Click Next.
  7. Confirm that the Install this deployment as an application option is selected and click Next.
  8. On the Select deployment targets page, under Clusters, select the target server, for example, AdminServer.
  9. Click Next.
  10. (Optional) On the Settings page, specify a name for the deployment, for example, obi-remotedataconnector, then click Next
  11. Verify the deployment summary.
  12. Click Finish.
    You see a message that indicates that the deployment has been successfully installed.
  13. On the left-hand side Change Center pane, click Activate Changes.
  14. On the right-hand content pane, select the option next to the EAR just deployed.
  15. Click Start, and select Servicing all requests from the drop-down list.
  16. In the content pane of the new page, click Yes.
    The deployment status changes from Prepared to Active.
  17. Temporarily disable Remote Data Connector's Metadata security:

    If Node Manager is installed, navigate to Environment, and click Servers. Select the server on which you deployed Remote Data Connector, click Server Start, and in the Arguments edit box, enter:

    -Doracle.bics.rdc.disable_metadata_security=1

    Note: If this variable is not set to 1, the status URL (provided in the next step) is blocked. After setting this property, restart Oracle WebLogic Server using Node Manager.

    If Node Manager is not installed, then before starting Oracle WebLogic Server (in the same command prompt or script that starts Oracle WebLogic Server), execute one of the following commands to set this environment variable:

    On Linux:

    export DISABLE_RDC_METADATA_SECURITY=1

    On Windows:

    set DISABLE_RDC_METADATA_SECURITY=1
  18. Restart Oracle WebLogic Server.
  19. Test the deployment by navigating to http(s)://<weblogic-server>:<weblogic-port>/obiee/javads?status.
    If the connection is valid, the browser displays XML code for the JavaDSServer.

Configure Your On-Premises Database as a JDBC Data Source

Configure a JDBC data source for your on-premises database.

  1. Sign in to Oracle WebLogic Server where you deployed Remote Data Connector, navigate to Domain Structure, and select Services.
  2. In the Summary of Services list, click Data Sources, and display the Configuration tab.
  3. In the Change Center pane click Lock & Edit.
  4. Under Data Sources, click New, and select Generic Data Source.
  5. Specify values for the Database Type, Name, and JNDI Name fields, then click Next.

    For example, you might use mysalesdatasource as a name for your sales database. To avoid confusion, use the same name for Name and JNDI Name. The JNDI Name forms a component of the URL used to access this data source after the setup is complete.

  6. In the next screen, for an Oracle database, select Oracle's Driver (Thin) for Service connections: Versions:Any in the Database Driver drop-down list and click Next.
  7. Accept defaults in the next screen and click Next.
  8. Enter connection details for your on-premises database, and click Next.
  9. In the next screen, click Test Configuration to test your database connection.
  10. At the Connection test succeeded message, click Next.
  11. In the Targets tab under the settings, select the server where you deployed Remote Data Connector.

    Note:

    You must disable metadata security on the server where you deployed Remote Data Connector. To navigate to the server, click Environment, and then Servers. Click the name of the server, click Server Start, and in the Arguments field, enter the value:

    —Doracle.bics.rdc.disable_metadata_security=1

  12. Verify that you can see the newly created JDBC data source in the list of data sources.

Download and Deploy the Public Key for Your Cloud Service

You download your public key from Oracle Analytics Cloud and deploy the public key in the on-premises Oracle WebLogic Server where you deployed Remote Data Connector.

  1. Sign-in to Oracle Analytics Cloud.
  2. Click Navigator This screenshot shows the Navigator icon used to display the navigator bar in Oracle Analytics Cloud. to display the navigator bar, and then click Console.
  3. Click Service Administration, and then click Manage Connections.
  4. From the menu, click Get Public Key.
    This downloads the key file oracle_bics_rdc.pem to your local system.
  5. In the Oracle WebLogic Server environment where you deployed Remote Data Connector, create the folder where you will copy the key file at this location:
    DOMAIN_HOME/rdc_keys/deployment_name
    For example, create the folder: DOMAIN_HOME/rdc_keys/obi-remotedataconnector.
    Where:
    • DOMAIN_HOME path is the directory in which the Oracle WebLogic Server domain is installed.

    • The default deployment_name is obi-remotedataconnector

  6. Copy the key file you downloaded earlier to the folder you created in the Oracle WebLogic Server environment. For example, copy oracle_bics_rdc.pem to the folder: DOMAIN_HOME/rdc_keys/obi-remotedataconnector.

Make Remote Data Connector Available to Your Service

With the help of a network administrator, give your cloud service access to Remote Data Connector and verify that Remote Data Connector can connect through the on-premises firewall.

Before you start, work with your network administrator to:
  • Configure the load balancer or reverse proxy for SSL communication and to route requests to the HTTP server.

  • Configure the HTTP server to direct requests to Oracle WebLogic Server.

  1. Using a browser, enter the public IP address or domain URL for WebLogic Server in the browser’s address bar using the following URL format:
    https://<Public IP or Domain Name>:<port>/obiee/javads?status
    For example https://10.200.5.1:7007/obiee/javads?status
    If the connection is valid, the browser displays XML code for the JavaDSServer.

Download and Install Oracle Analytics Cloud Developer Client Tool

Download the Oracle Analytics Cloud Developer Client Tool if you want to edit data models in the cloud or configure connections to on-premises databases for Remote Data Connector. Oracle Analytics Cloud Developer Client Tool runs on a Windows machine.

  1. Download the latest Oracle Analytics Cloud Developer Client Tool.

    1. Navigate to:

      http://www.oracle.com/technetwork/middleware/oac/downloads/oac-tools-4392272.html

    2. Click Accept license agreement.

    3. To start the download, click the latest Oracle Analytics Developer Client Tool link.

      If you’re not sure which version to download, ask your Oracle Analytics Cloud administrator.

  2. Install Oracle Analytics Cloud Developer Client Tool.

    1. From the downloaded ZIP file, extract the setup_bi_client-5.0.0.0.0-win64.exe file.
    2. In the local download area, double-click the setup_bi_client-5.0.0.0.0-win64.exe file to start the installer.

    3. Follow the on-screen instructions.

To start Oracle Analytics Cloud Developer Client Tool, from the Windows Start menu, click Oracle Business Intelligence Client, click <BI Client Home Name> , and then click Administration.

Configure Oracle Analytics Cloud Developer Client Tool with Proxy Server Details

If you are using a version of Oracle Analytics Cloud Developer Client Tool earlier than 18.3.3, you need to configure Oracle Analytics Cloud Developer Client Tool if you connect to Oracle Analytics Cloud through a proxy server.

Note:

Skip this step if you're using Oracle Analytics Cloud 18.3.3 or later.

To specify your proxy details when you connect to the Oracle Analytics Cloud:

  1. Start Oracle Analytics Cloud Developer Client Tool.

    1. Click the Windows Start menu.

    2. Click Oracle Business Intelligence Client, click <BI Client Home Name> , and then click Administration.

    3. After confirming you have access, exit the tool.

  2. Configure Oracle Analytics Cloud Developer Client Tool to use your proxy host and port.

    1. Use a text editor to edit the datamodel.cmd file located in the <installation root>\<Oracle Home>\bi\modules\oracle.bi.commandline.tools\scripts folder.

      Don’t edit any other copy of the datamodel.cmd file in other folders in the installation.

    2. At the end of the file, immediately before the oracle.bi.utils.BIExec %* string, add the proxy host and port number that your deployment uses.

      For example, add the line:

      -Dhttp.proxyHost=www.myproxyhost.company.com -Dhttp.proxyPort=80

      This example shows the proxy host and port number specified in the datamodel.cmd file:

      !JAVA_HOME!\bin\java -classpath !CP! -Djava.util.logging.config.file=!BI_PRODUCT_HOME!\modules\oracle.bi.commandline.tools\scripts\logging.properties -Djavax.net.ssl.trustStore=!TRUST_STORE! -Djavax.net.ssl.trustStorePassword=!TRUST_STORE_PW! -Ddomain.home=!DOMAIN_HOME! -Doracle.bi.home.dir=!BI_PRODUCT_HOME! -Doracle.bitools.bin=!CLIENT_BITOOLS_BIN_DIR! -Dhttp.proxyHost=www.myproxyhost.company.com -Dhttp.proxyPort=80 oracle.bi.utils.BIExec %*

    3. Save the changes.

Update the Connection Pool for the Data Model

You use Oracle Analytics Cloud Developer Client Tool to connect to the on-premises database for your data model through Remote Data Connector.

  1. Open Oracle Analytics Cloud Developer Client Tool.
  2. In the File menu, select Load Java Datasources.
  3. In the Connect to Java Datasource Server dialog, specify the Hostname and the Port where Remote Data Connector is running.
  4. For Username and Password, enter the user name and password for the WebLogic Server administrator where Remote Data Connector is running.
  5. Click OK.

    A success message is displayed: Successfully loaded javads metadata from https://Public IP or Domain Name:Port.

    If you haven't disabled metadata security for Remote Data Connector, then this URL fails with the message: 401-Unauthorized.

  6. Open the data model file you migrated to Oracle Analytics Cloud.
    1. Click File, click Open, and then click In the Cloud.
    2. Enter connection information for Oracle Analytics Cloud.
      Option Description
      User Enter the user name of an Oracle Analytics Cloud administrator (a user with the BI Service Administrator application role).
      Password Enter the password for the specified user.
      Cloud Enter bootstrap.
      Host name Enter the public host name or IP address of Oracle Analytics Cloud without the string /va at the end of the URL. For example, myoac.oraclecloud.com.

      Because you subscribe to Oracle Analytics Cloud though Universal Credits, the host name includes the Load Balancer as a Service (LBaaS) host name.

      Port number

      Specify 80 for non–SSL setup or 443 for SSL setup.

      Set up SSL with your own certificate. Use a truststore specific to your SSL configuration.

      Proxy If you’re deploying Oracle Analytics Developer Client Tool on a machine in a proxy-enabled network, select this option. Ask your network administrator for the proxy server URL and Port number.
    3. (Optional) To connect to your data model securely:
      1. Make sure that the SSL option is selected.

      2. Click Browse and select the trust store that you’re using to secure the server.

        You can use the default JDK SSL trust store named cacerts, (with the default password) located in:

        oracle_home\oracle_common\jdk\jre\security

      3. In the Password field, specify the password for the trust store.

    Expect to wait between 3 and 15 minutes for the connection to be established, depending on the size of the repository.
  7. In the Physical layer, double-click the data source, then display the Connection Pools tab.
  8. Double-click the connection pool to display the Connection Pool dialog.
  9. Change the Call interface to JDBC (JNDI).
  10. In the Data source name field, change the value of the Remote Data Connector URL.

    The Remote Data Connector URL is the endpoint URL in the format:

    https://Public IP or Domain Name:port/obiee/javads/JNDI connectionname.

    For the JNDI connectionname value, use the JNDI name that you specified when you created a new data source in WebLogic Server (in the task ‘Configure Your On-Premises Database as a JDBC Data Source’Configure Your On-Premises Database as a JDBC Data Source).

  11. Click the Miscellaneous tab.
    This step is mandatory because the data model (RPD) isn’t updated unless the Use SQL over HTTP variable is set to true. This is only saved on switching to this tab.
  12. Click OK.
  13. To save the changes locally, click File, and then click Save.
  14. To upload your changes to Oracle Analytics Cloud, click File, click Cloud, and then click Publish.

Verify that Your Data Connection is Secure

Verify that your connection is secure by testing that it's accessible only within your network.

Test Remote Data Connector using the URL that you specified in the Data source name field on the Connection Pool dialog.

Note:

An easy way to test this is to use a smart phone with its mobile data turned on and its WiFi turned off. From the smart phone, in a browser enter the exact URL that you specified as the Data source name value in the previous setup task (Update the Connection Pool for the Data Model). If your connection is configured securely, the message "401--Unauthorized" is displayed. For other errors, double check the protocol (HTTP/HTTPS), URL, and port.