OAC Remote Data Connector with Jetty

For other A-Team articles by Richard, click here

Introduction

The Remote Data Connector (RDC) allows the Oracle Analytics Cloud service to connect to on-premise data sources.

There are now 2 versions.

The initial version required that a web-sever, either weblogic or tomcat, be used on premise for the tool to work.  That version is still available, and for customers already using Weblogic or Tomcat on-premise, that may still be the preferred approach.  Details on configuring that can be found in this article.

For customers who don’t want to license an additional web-server, there is now a version of RDC that is standalone.  The tool is based on a Jetty Servlet Engine (more details here).

It also adds functionality, with new data sources available, and allows Data Visualizer to connect directly to on-premise Essbase, and Relational Database sources, without the need for an RPD.

This article walks through the steps to set up and configure this version of the Remote Data Connector, and to set up connections, both in the RPD, and directly within DV.

Note

The jetty version of RDC is linux only.

The following data sources are available in this first release of the tool:

  • DB2
  • Hive *
  • Impala *
  • My SQL *
  • My SQL – CE*
  • Oracle
  • Spark SQL *
  • SQL Server

Those marked with a * will need an RPD connection.  Those without the * can be used both in the RPD, and also with a direct DV connection.

Please note that Teradata is not supported.  This is due to the distribution of drivers.  The weblogic / tomcat version of RDC will be required for a Teradata target.

This article contains the following sections

Installing the Remote Data Connector

Confirm External Connectivity

Creating Additional RDC Data Sources

Creating a Direct Database Connection from Data Visualizer

Creating a Direct Essbase Connection from Data Visualizer

Configure the RPD for RDC Connections for both Database and Essbase

 

Installing the Remote Data Connector

1. Download the Remote Data Connector from this page on OTN.  Make sure you download the 1.5 version

2. Download the Public Key file from the OAC instance, by going to ‘Service Administration’ and then ‘Manage Connections’

3. Select the ‘Get Public Key’ option from the menu on the far upper right, and save the key.

4. On the linux server within the on-premise network, or DMZ, unzip the files downloaded from OTN, and then run the installer

./bi_rdc_client-VERSION-linux64.bin

5, Click through the welcome screen

6. Set the RDC install location:

7, Select whether to enable RDC to allow direct connections from Data Visualization (DV), without the need for an RPD, and/or to enable an RPD connection.

In this example both have been selected, so both connection methods will be configured.

NOTE – essbase functionality is included in the the jetty version, and is installed by default.  No additional configuration in jetty is required for essbase.  Once installed, see the section below on configuring the essbase connection in OAC.

Select the Database type for the initial DB connection.  Additional connections can be added later.

In this case we will select an Oracle DB source.

8. Enter the details for the connection, which will be database specific.  The ‘JNDI Name’ will be used as part of the RPD connection string, so make sure it is unique.

9. Enter the path to the Public Key file downloaded from the OAC server.

10. Confirm the settings, then hit ‘Install’

11. Once installation is complete, you can have the GUI start the Jetty server for you, otherwise you will need to do this manually.  Hit ‘Finish’ to complete the install.

12. To stop and start jetty from the command line, use the commands found in the path

$RDC_INSTALL_HOME/domain/bin

Note

In that folder there are scripts to stop and start the jetty server, as well as to configure SSL,

Confirm External Connectivity

This article will not cover the configuration of a DMZ, or firewall rules to make sure the jetty server is accessible externally.  Although where possible, it is recommended that the initial connection is set up with the firewall allowing direct HTTP access to the jetty server port.  Once that has been confirmed to work, then firewall rules, and HTTPS certs where necessary, can be introduced.  Doing it this way, confirms that the initial install and configuration is working, and helps narrow down subsequent troubleshooting.

To confirm that the networking has been configured, from OUTSIDE of the on-premise network, open the following URL(s) in a browser, to confirm connectivity.

The URL format will be either

http://externally_available_IP_or_hostname:8080/obiee/javads?status

or for HTTPS connections

https://externally_available_IP_or_hostname:8443/obiee/javads?status

If connectivity is working correctly, a message similar to the one below should be displayed.  If it does not, then confirm installation, and network / firewall rules before continuing.

 

Creating Additional RDC Data Sources

In the event you wish to make additional relational data sources available on the on-premise environment, you can do this by editing the following 2 files:

$RDC_INTALL_HOME/domain/jettibase/webapps/obiee.xml

$RDC_INSTALL_HOME/domain/tmp/obiee/WEB-INF/web.xml

Remember – Essbase functionality is already installed, so additional essbase connections are defined in OAC, not in these files.

For the obiee.xml file,add a section at the bottom, similar to the examples below, adjusting the values as appropriate for the local database connections.  In these examples, the passwords are in clear text, which is not advisable.  See the following section to obfuscate them.

 

DB2 :

<New id=”db2″ class=”org.eclipse.jetty.plus.jndi.Resource”>
<Arg></Arg>
<Arg>DB2</Arg>
<Arg>
<New class=”com.oracle.bi.jdbcx.db2.DB2DataSource”>
<Set name=”ServerName”>xxxhostxxx.us.oracle.com</Set>
<Set name=”PortNumber”>58263</Set>
<Set name=”DatabaseName”>NORTHDB</Set>
<Set name=”User”>db2admin</Set>
<Set name=”Password”>apple</Set>
</New>
</Arg>
</New>

Impala:

<New id=”imp” class=”org.eclipse.jetty.plus.jndi.Resource”>
<Arg></Arg>
<Arg>Impala</Arg>
<Arg>
<New class=”com.oracle.bi.jdbcx.impala.ImpalaDataSource”>
<Set name=”ServerName”>xxxhostxxx.us.oracle.com</Set>
<Set name=”PortNumber”>21050</Set>
<Set name=”DatabaseName”>default</Set>
<Set name=”User”>hive</Set>
<Set name=”Password”>hive</Set>
</New>
</Arg>
</New>

SQLServer:

<New id=”sqlserver” class=”org.eclipse.jetty.plus.jndi.Resource”>
<Arg></Arg>
<Arg>SQL</Arg>
<Arg>
<New class=”com.oracle.bi.jdbcx.sqlserver.SQLServerDataSource”>
<Set name=”ServerName”>xxxhostxxx.us.oracle.com</Set>
<Set name=”PortNumber”>61045</Set>
<Set name=”DatabaseName”>Northwind</Set>
<Set name=”User”>sa</Set>
<Set name=”Password”>admin1-2</Set>
</New>
</Arg>
</New>

Hive:

<New id=”hive” class=”org.eclipse.jetty.plus.jndi.Resource”>
<Arg></Arg>
<Arg>HIVE</Arg>
<Arg>
<New class=”com.oracle.bi.jdbcx.hive.HiveDataSource”>
<Set name=”ServerName”>xxxhostxxx.us.oracle.com</Set>
<Set name=”PortNumber”>10000</Set>
<Set name=”DatabaseName”>default</Set>
<Set name=”User”>hive</Set>
<Set name=”Password”>hive</Set>
</New>
</Arg>
</New>

Spark:<New id=”spark” class=”org.eclipse.jetty.plus.jndi.Resource”>
<Arg></Arg>
<Arg>SPARK</Arg>
<Arg>
<New class=”com.oracle.bi.jdbcx.sparksql.SparkSQLDataSource”>
<Set name=”ServerName”>xxxhostxxx.us.oracle.com</Set>
<Set name=”PortNumber”>10000</Set>
<Set name=”DatabaseName”>default</Set>
<Set name=”User”>hive</Set>
<Set name=”Password”>hive</Set>
</New>
</Arg>
</New>

MySQL:<New id=”mysql” class=”org.eclipse.jetty.plus.jndi.Resource”>
<Arg></Arg>
<Arg>MYSQLCE</Arg>
<Arg>
<New class=”com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource”>
<Set name=”URL”>jdbc:mysql://xxxhostxxx.oracle.com:3306/northwind</Set>
<Set name=”User”>root</Set>
<Set name=”Password”>Welcome1</Set>
</New>
</Arg>
</New>

Oracle:

<New id=”oracle” class=”org.eclipse.jetty.plus.jndi.Resource”>
<Arg></Arg>
<Arg>ORACLE</Arg>
<Arg>
<New class=”com.oracle.bi.jdbcx.oracle.OracleDataSource”>
<Set name=”ServerName”>xxxhostxxx.us.oracle.com</Set>
<Set name=”PortNumber”>1234</Set>
<Set name=”ServiceName”>MATSDB.us.oracle.com</Set>
<Set name=”User”>ORA20104</Set>
<Set name=”Password”>ORA20104</Set>
</New>
</Arg>
</New>

then for the web.xml file, append a line to match the connection you created above, with this format

<resource-ref xmlns=””><res-ref-name>DB2</res-ref-name><res-type>javax.sql.DataSource</res-type><res-auth>Container</res-auth></resource-ref>

<resource-ref xmlns=””><res-ref-name>Impala</res-ref-name><res-type>javax.sql.DataSource</res-type><res-auth>Container</res-auth></resource-ref>

<resource-ref xmlns=””><res-ref-name>SQL</res-ref-name><res-type>javax.sql.DataSource</res-type><res-auth>Container</res-auth></resource-ref>

<resource-ref xmlns=””><res-ref-name>HIVE</res-ref-name><res-type>javax.sql.DataSource</res-type><res-auth>Container</res-auth></resource-ref>

<resource-ref xmlns=””><res-ref-name>SPARK</res-ref-name><res-type>javax.sql.DataSource</res-type><res-auth>Container</res-auth></resource-ref>

<resource-ref xmlns=””><res-ref-name>MYSQLCE</res-ref-name><res-type>javax.sql.DataSource</res-type><res-auth>Container</res-auth></resource-ref>

<resource-ref xmlns=””><res-ref-name>ORACLE</res-ref-name><res-type>javax.sql.DataSource</res-type><res-auth>Container</res-auth></resource-ref>

 

Obfuscate Passwords

The passwords in the obiee.xml file can be obfuscated, to make them harder to decipher.

If you wish to do this to a password, you can use a utility in the following path:

$Oracle_Home/oui/mw/birdc/jlib

This command, would provide the obfuscated version of the password ‘apple’

java -cp jetty-util-9.4.9.v20180320.jar org.eclipse.jetty.util.security.Password “apple”

You can also use the same command, to convert an obfuscated password back into plain text

java -cp jetty-util-9.4.9.v20180320.jar org.eclipse.jetty.util.security.Password “OBF:1saj1wn71xfj1wmz1sar”

And the full example

cd $Oracle_Home/oui/mw/birdc/jlib
[oracle@demo jlib]$ java -cp jetty-util-9.4.9.v20180320.jar org.eclipse.jetty.util.security.Password “apple”
2019-02-27 13:00:43.161:INFO::main: Logging initialized @230ms to org.eclipse.jetty.util.log.StdErrLog
apple
OBF:1saj1wn71xfj1wmz1sar
MD5:1f3870be274f6c49b3e31a0c6728957f
[oracle@demo jlib]$ java -cp jetty-util-9.4.9.v20180320.jar org.eclipse.jetty.util.security.Password “OBF:1saj1wn71xfj1wmz1sar”
2019-02-27 13:01:17.322:INFO::main: Logging initialized @125ms to org.eclipse.jetty.util.log.StdErrLog
apple
OBF:1saj1wn71xfj1wmz1sar
MD5:30b83510e294d96b711261f2ffe50591

 

Stop and start the jetty server with the scripts in this folder.

$RDC_INSTALL_HOME/domain/bin

Creating a Direct Connection from DV

Note

This functionality requires Oracle Analytics Cloud 5.2 or later.

1. From the OAC console, select the hamburger menu item, select ‘Data Set Management

2. Select the ‘Remote Connections‘ menu item, and enter the URL for the on-premise Jetty RDC server.

The URL format will be either

http://externally_available_IP_or_hostname:8080/obiee/javads

or for HTTPS connections

https://externally_available_IP_or_hostname:8443/obiee/javads

In this example

3. ‘Close’ the Data Set Management GUI

4. Create a new Connection

5. The available database options in this first release, are for an Oracle database, SQL Server, or DB2.  In this example we will use Oracle.

6. In the connection, the host, port, user credentials and service name, are all for the on-premise database.  The remote connection configured in the previous step, has already taken care of the connection to the on-premise environment.

Be sure to check the ‘Use Remote Data Connector‘ box, and then hit ‘Save’.

If an error message is displayed, confirm the entries in the connection, and remote data connector, are correct.

7. From DV, create a Data Set as normal, and use the newly created connection to the on-premise database.

 

Create a Direct Essbase Connection in Data Visualizer

 

1. In OAC, create a new connection.

2. Select ‘Oracle Essbase‘ as the connection type.

3. In the DSN, use this URL format:

http://externally_available_IP_or_hostname:8080/essbase/myonpremiseessbase.mycompany.com/1423

Or HTTPS connection:

https://externally_available_IP_or_hostname:8443/essbase/myonpremiseessbase.mycompany.com/1423

 

The username and password is for the Essbase server credentials.

4. If the connection is successful, you will see a list of available cubes.  Select the one you want, and continue creating your data set.

 

 

Configure the RPD for RDC Connections to Databases and Essbase

For RDC to work, the Java Datasources need to be loaded, and the connections pointed to ports that jetty is listening on.  This can be done with the RPD in the cloud, but for this article, we will use a local RPD, and then publish to OAC once completed.

1. Download the latest version of the BI Admin tool from OTN.  Use this link.  This is important, as older versions may not work.

2. Open the Admin Tool, and then under ‘File’ select ‘Load Java Datasources’

3. Point it to the host where the Jetty Server was installed.  This Hostname / IP is not stored, so you can do this on-premise and with an internal IP or host name.

The port for SSL is 8443.  No password is required, but without the user name, the JAVADS will not load properly.

4. Open the RPD, and update the connection strings and properties.

For a Database Connection, update the data source name as follows:

http://externally_available_IP_or_hostname:8080/obiee/javads/datasourcename

Or for HTTPS connections

https://externally_available_IP_or_hostname:8443/obiee/javads/datasourcename

The IP or Hostname must be externally available on the internet.

Be sure to change the Call Interface to be ‘JDBC (JNDI).  No user name or password information is required, as that was defined as part of the RDC database configuration previously.

In our Oracle database example, the string will look like this, with ‘mydatabase’ being the name from step 8 in the RDC install section.

On the ‘Miscellaneous‘ tab, make sure that ‘Use SQL Over HTTP‘ is set to true

For an Essbase connection in the RPD, the format for an HTTP connection will be:

http://externally_available_IP_or_hostname:8080/essbase/myonpremiseessbase.mycompany.com/1423

Or HTTPS connection:

https://externally_available_IP_or_hostname:8443/essbase/myonpremiseessbase.mycompany.com/1423

The first hostname field must be externally available on the internet.

The second hostname in the URL is for the essbase server, and can be an internal hostname or IP.  This only needs to be able to resolve from the host where the jetty server is running.

The username and password is for an Essbase login on that Essbase host.

The call interface can be left as an Essbase connection.

Upload the new RPD to the OAC instance, by selecting ‘Service Administration’, and then ‘Manage Snapshots’

Then Replace the Data Model.

 

Summary

This article walked through the steps to set up and configure a the Jetty Version of the Remote Data Connector, and to set up connections, both directly, and via the RPD

 

For other A-Team articles by Richard, click here

 

Comments

  1. Hi Richard,

    While trying to install the RDC on a linux VM we got this error after adding in the database details. Database used is 12c.

    Launcher log file is /tmp/OraInstall2019-04-10_06-54-46PM/launcher2019-04-10_06-54-46PM.log.
    Checking if CPU speed is above 300 MHz. Actual 1995.309 MHz Passed
    Checking swap space: must be greater than 512 MB. Actual 8191 MB Passed
    Preparing to launch the Oracle Universal Installer from /tmp/OraInstall2019-04-10_06-54-46PM
    ^[[C^[[D^[[D^[[D^[[D^[[DLog: /tmp/OraInstall2019-04-10_06-54-46PM/install2019-04-10_06-54-46PM.log
    Calling invokeStandaloneValidations
    Calling invokeStandaloneValidations::isEverythingInstalled::false
    /tmp
    java.sql.SQLException: [Oracle DataDirect][Oracle JDBC Driver]ORA-12660: Encryption or crypto-checksumming parameters incompatible
    at com.oracle.bi.jdbc.oraclebase.ddcr.b(Unknown Source)
    at com.oracle.bi.jdbc.oraclebase.ddcr.a(Unknown Source)
    at com.oracle.bi.jdbc.oraclebase.ddcq.b(Unknown Source)
    at com.oracle.bi.jdbc.oraclebase.ddcq.a(Unknown Source)
    at com.oracle.bi.jdbc.oracle.OracleImplConnection.a(Unknown Source)
    at com.oracle.bi.jdbc.oracle.OracleImplConnection.b(Unknown Source)
    at com.oracle.bi.jdbc.oracle.OracleImplConnection.aa(Unknown Source)
    at com.oracle.bi.jdbc.oraclebase.BaseConnection.b(Unknown Source)
    at com.oracle.bi.jdbc.oraclebase.BaseConnection.k(Unknown Source)
    at com.oracle.bi.jdbc.oraclebase.BaseConnection.b(Unknown Source)
    at com.oracle.bi.jdbc.oraclebase.BaseConnection.a(Unknown Source)
    at com.oracle.bi.jdbc.oraclebase.BaseDriver.connect(Unknown Source)
    at java.sql.DriverManager.getConnection(DriverManager.java:664)
    at java.sql.DriverManager.getConnection(DriverManager.java:208)
    at oracle.as.install.birdc.ui.screens.BIRDCConnectionDetailsPage.invokeStandaloneValidations(BIRDCConnectionDetailsPage.java:650)
    at oracle.as.install.engine.modules.presentation.ui.common.wizard.ModifiedDWizard.doNext(ModifiedDWizard.java:1200)
    at oracle.bali.ewt.wizard.BaseWizard$Action$1.run(BaseWizard.java:4072)
    at java.awt.event.InvocationEvent.dispatch(InvocationEvent.java:311)
    at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:756)
    at java.awt.EventQueue.access$500(EventQueue.java:97)
    at java.awt.EventQueue$3.run(EventQueue.java:709)
    at java.awt.EventQueue$3.run(EventQueue.java:703)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:80)
    at java.awt.EventQueue.dispatchEvent(EventQueue.java:726)
    at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:201)
    at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:116)
    at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:105)
    at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:101)
    at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:93)
    at java.awt.EventDispatchThread.run(EventDispatchThread.java:82)
    java.sql.SQLException: [Oracle DataDirect][Oracle JDBC Driver]ORA-12660: Encryption or crypto-checksumming parameters incompatible
    at com.oracle.bi.jdbc.oraclebase.ddcr.b(Unknown Source)
    at com.oracle.bi.jdbc.oraclebase.ddcr.a(Unknown Source)
    at com.oracle.bi.jdbc.oraclebase.ddcq.b(Unknown Source)
    at com.oracle.bi.jdbc.oraclebase.ddcq.a(Unknown Source)
    at com.oracle.bi.jdbc.oracle.OracleImplConnection.a(Unknown Source)

    Any idea why?

Add Your Comment