Note

The jetty version of RDC is linux only.

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

• DB2<_li>
• Hive *<_li>
• Impala *<_li>
• My SQL *<_li>
• My SQL - CE*<_li>
• Oracle<_li>
• Spark SQL *<_li>
• SQL Server<_li> <_ul>

  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>

   

  This article contains the following sections

  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>

   

  Installing the Remote Data Connector

  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>

   

  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.<_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>

   

  Obfuscate Passwords<_h3>

  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

   

  Creating a Direct Connection from DV

  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.

  

   

  Create a Direct Essbase Connection in Data Visualizer

   

  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.

  

   

   

  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<_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.

  

   

  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