Deprecation of Remote Data Connector (RDC) Private Access Channel is now available in Oracle Analytics and is recommended by Oracle for new connections to private data sources. For connections where Private Access Channels is not yet supported, Data Gateway should be used instead of RDC. For more information on Private Access Channel and Supported Data Sources: Connect to Private Data Sources Through a Private Access Channel |
* This blog was written for Remote Data Connector (RDC) which is no longer the recommended method to connect to private data sources. There are no plans to re-test this blog on the latest release of OAC. This blog will remain active for customers who are still using RDC and may benefit from it - Jay Pearson (A-Team) *
For other A-Team articles by Richard, click here
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:
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.
<_div>Installing the Remote Data Connector<_em>
Confirm External Connectivity<_em>
Creating Additional RDC Data Sources<_em>
Creating a Direct Database Connection from Data Visualizer<_em>
Creating a Direct Essbase Connection from Data Visualizer<_em>
Configure the RPD for RDC Connections for both Database and Essbase<_em>
1. Download the Remote Data Connector from this page on OTN. Make sure you download the 1.5 version (or later if available)
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.<_span>
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,
<_div>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.
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.<_span>
For the obiee.xml<_span> 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 :<_u>
<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:<_u>
<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:<_u>
<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:<_u>
<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:<_u><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:<_u><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:<_u>
<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<_span> 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>
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<_span>
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<_span>
apple<_span>
OBF:1saj1wn71xfj1wmz1sar<_span>
MD5:1f3870be274f6c49b3e31a0c6728957f<_span>
[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<_span>
apple<_span>
OBF:1saj1wn71xfj1wmz1sar<_span><_span>
MD5:30b83510e294d96b711261f2ffe50591<_span>
Stop and start the jetty server with the scripts in this folder.
$RDC_INSTALL_HOME_domain_bin
Note
This functionality requires Oracle Analytics Cloud 5.2 or later.
<_div>1. From the OAC console, select the hamburger menu item, select 'Data Set Management<_span>'
2. Select the 'Remote Connections<_span>' 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<_span>' 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.
1. In OAC, create a new connection.
2. Select 'Oracle Essbase<_span>' 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.
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<_span>, 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<_span>' tab, make sure that 'Use SQL Over HTTP<_span>' is set to true
For an Essbase connection<_span> <_span>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.
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
Previous Post