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.
Published on July 23, 2019
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
Before You Begin
Deploying Remote Data Connector
Loading Java Data Sources
Configuring an RDG Database Connection
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.
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
Mac OS was not tested as it needs to allow ingress from a Windows host.
Administration privileges on a Windows server or client.
Ensure your data source is supported by following the instructions in Supported Data Sources.
Navigate to Developer Client Tool for OAC.
Accept the License Agreement
Click the latest version
Choose a download location if requested
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
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.
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.
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.
If RDC is available an XML file is displayed.
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.
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.
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.
Start the Developer Client Tool Using the Start Menu or via a Command Line
SET OAC_HOME=<Your Installation Directory"
Note: If there is not a Linux RDG to connect to, then there has to be a RDG installation on the same Windows machine as the the OAC Client. It does not need to have a connection to OAC, it just needs to be running.
To find the correct port on Linux, SSH into the RDG host and run the status command:
To find the correct port on Windows, open the ports.properties file in the %localappdata%\Temp\<n>\DataGateway directory.
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.
Right-Click on the Database Connection Name and click Duplicate.
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
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
Based on the note above, if your database type is not Oracle, double-click on it and return the type to its original value.
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.
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