X

Best Practices from Oracle Development's A‑Team

Deploying Remote Data Gateway in Oracle Analytics Cloud for Metadata Repositories

Last validated October 22, 2019 with OAC 5.4 and RDG 5.4

Introduction

The latest releases of Oracle Analytics Cloud (OAC) now include the new Remote Data Gateway (RDG) for accessing databases that are not otherwise accessible by OAC.

This post is a supplement to the post Deploying Remote Data Gateway in Oracle Analytics Cloud for Data Visualization.

It is a step-by-step guide to creating RDG connections using the Developer Client Tool for OAC.

The official documentation is  Connect to On-premises Data Sources and Known Issues.

Updates

Published on July 23, 2019

Versions

  • Oracle Analytics Cloud versions 105.3.x, 104.X

  • RDG versions 105.3.x, 104.X

  • Developer Client Tool for OAC 105.3.x, 104.x

  • Windows Server R12 Compute Instance

Topics

  • Before You Begin

  • Deploying Remote Data Connector

  • Loading Java Data Sources

  • Configuring an RDG Database Connection

 Before You Begin

Prerequisites

For this post, creating RDG connections using the Developer Client Tool for OAC requires.

  • An OAC Repository Data Model (RPD). This can either be accessed In the Cloud or Offline.

  • The latest Developer Client Tool for OAC downloaded and installed on a Windows server or client.

  • For 105.3.x, a server with a public IP address that is accessible using HTTP or HTTPs running the Oracle Remote Data Connector (RDC) or is available to install RDC. This has been resolved in 105.4

  • For 105.3.x, an OAC Public Key downloaded from an OAC instance. If the Linux server does not have a browser, download it to your client and then upload it to the Linux server. An OAC public key is required for a successful RDC installation. This has been resolved in 105.4

  • A supported RDG data source for data models. The connections details for a JDBC thin driver for that source.

  • For 105.4+ use in loading Java Data Sources, on the machine where you've installed a Data Gateway agent, obtain the machine name, IP address and port number.

    • For Linux:
      Execute the command <DataGatewayInstallFolder>/domain/bin/status.sh 
      In the command output, note the machine name, IP address and HTTP Port values.

    • For All Windows:
      Find the file named ports.properties for the installation you are using: It may be under %localappdata%\Temp\
      Note the port number. e.g. 51685

    • For a Remote Windows Server:
      Find the IP address. For a public IP,  open a browser on that server and type What is my IP address. For a private IP address, open a CMD session and type IPCONFIG

    • For MAC:
      Mac OS was not tested as it needs to allow ingress from a Windows host. 

  • Administration privileges on a Windows server or client.

Reviewing Supported Data Sources

Ensure your data source is supported by following the instructions in Supported Data Sources.

Downloading the Developer Client Tool

Navigate to Developer Client Tool for OAC.

  • Accept the License Agreement

  • Click the latest version

  • Choose a download location if requested

Installing the Developer Client Tool

Go to the folder where the tool was downloaded.

  • Unzip the downloaded file

  • Click on the installer file e.g. setup_bi_client-5.x-win64.exe

  • Choose an installation folder

Deploying the Remote Data Gateway Agent(s)

Deploy and configure one or more RDG agents following the steps in the post Deploying Remote Data Gateway in Oracle Analytics Cloud for Data Visualization. Ensure the agent(s) can reach the remote database. Note: This post assumes a Linux agent is deployed. The installer is reused below for 103.x.

 Deploying the Remote Data Connector

Skip this section if using 105.4x or later.

For 103.x, an RDG agent installation cannot be used to load java data sources. The workaround is to use an existing Remote Data Connector (RDC) installation or to install a new RDC. This post uses both an existing Tomcat RDC installation and a new RDC installation using the Data Gateway installer for Linux.

Using an Existing RDC

Note the host name/IP and the HTTP(s) port it is using. Ensure it is accessible and running by issuing the RDC status command in a browser. An HTTP example using default port 80 is below.

http://129.nnn.nn.nnn:80/obiee/javads?status

If RDC is available an XML file is displayed.

Installing a New RDC

The same Linux server and RDG installer is used to additionally install a new RDC. As noted above this post assumes a Linux RDG agent is installed. Use the RDG blog section Installing RDG on Linux to start the installer.

Accept the defaults for the Installation Inventory Setup and click OK.

If a Welcome dialog is displayed, click Next.

At the Installation Location dialog, choose a different location than what is used for RDG and click Next.

At the Select Remote Data Version dialog, select Remote Data Connector and click Next.

At the RDC Configuration dialog, check the Enable DV Connection box and click Next.

At the OAC Public Key dialog browse or enter the path to the downloaded OAC public key mentioned in the prerequisitesClick Next.

At the Installation Summary dialog, click Install.

After the Installation Progress screen completes, click Next.

At the Installation Complete dialog, click Start Jetty and then click Finish.

Validating the Linux Installation and Obtaining the Port and the URL

Return to the SSH session and type the status command. Make a note of the HTTP Port for use in loading the Java Data Sources.

/home/opc/Oracle/Middleware/RDC/domain/bin/status.sh

Ensuring Access to RDC

The Windows client hosting the Developer Client Tool requires the ability to access the RDC HTTP port. If the client is in the same network as the RDC server this should not be an issue.

To test if access is available, use a browser on the Windows client and the following URL:

http://<Your RDC host name/IP>:<Your RDC HTTP port>/obiee/javads?status

If successful, an XML response is returned.

If you need to open the HTTP port, refer to http://www.ateam-oracle.com/opening-ports-in-linux-7-firewalls-for-oracle-analytics-cloud-access-to-databases-and-remote-data-connector for guidance.

 Loading the Java Data Sources

Start the Developer Client Tool Using the Start Menu or via a Command Line

SET OAC_HOME=<Your Installation Directory"

CD %OAC_HOME%\BI\BITOOLS\BIN
ADMINTOOL.CMD​

  • ​Click Load Java Data Sources from the File drop-down

  • Complete the details.

If the full host name is not resolvable, use the IP address. The password is not required. The user is not validated so it can be admin. Do not check the SSL box.

Note: If the OAC Client and the RDG agent are on the same machine use localhost for the host name.

  • Click OK on the confirmation screen.

 Configuring the RDG Database Connection

In this section an existing database connection is duplicated to preserve the original settings. It is then configured for RDG.

Open the repository either In the Cloud or Offline mode.

Duplicating the Database Connection

Right-Click on the Database Connection Name and click Duplicate.

Configuring the Database Connection for RDG

Note: In the link to Known Issues is the following:

  • Based on the above, if your database type is not Oracle, double-click on it and change the type to Oracle.

  • Double-Click on the Connection Pool to open it.

  • On the General tab

  • Change the Call Interface to JDBC (Direct Driver)

  • Change the Connection String to use the JDBC format for the database type used and click OK.

The format for Oracle databases is:

jdbc:oracle:thin:@//host IP or name:port/service name

-or-

jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=<hostname or IP address>)(PORT=<port>))(CONNECT_DATA=(<service-name>)))

  • ​​​Change to the Miscellaneous tab.

  • For Required Cartridge Version enter 12.1

  • Ensure Javads Server URL is Blank

  • Enter the Driver Class for the database type you use. For Oracle it is oracle.jdbc.driver.OracleDriver

  • For Use SQL over HTTP use true

  • For RDC Version use 2

  • Click OK

Based on the note above, if your database type is not Oracle, double-click on it and return the type to its original value.

Saving the Repository

From the File menu click Save.

If the repository is opened in the cloud, then Publish it from the File>Cloud menu.

If opened offline, then replace it in OAC using the steps in using the steps in Upload Data Models.

You can now create Analyses in OAC using the remote data source.

 Summary

This post described creating RDG connections to remote data sources using the Developer Client Tool for OAC.

For other posts relating to analytics and data integration visit http://www.ateam-oracle.com/dayne-carley

 

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha