OAC Connection to On-Premise Essbase via Remote Data Connector

For other A-Team articles by Richard, click here

Introduction

This article walks through the steps to set up the Remote Data Connector (RDC), for Oracle Analytics Cloud, to connect to an On-Premise Essbase source.  

Two methods will be described:

  • A direct connection from the Data Visualization tool without the need to use a repository (RPD)
  • A connection from the Analyses tool, that does use a RPD connection

Notes

The Essbase.war file requires Java 1.8.  This impacts the version of the web-server that it can run in.  For instance, weblogic 10.3.6 is not certified for java 1.8, but weblogic 12c is.  The ‘Jetty’ version of RDC (covered in this article) works with Essbase, and does not require a weblogic or tomcat web-server, so that may be an option for 10.3.6 customers.

This functionality requires the use of a Weblogic or Tomcat Server ‘on-premise’.  A future version of the Remote Data Connector will eliminate that dependency.  That will be covered in a future article.

This article will walk through the steps for Weblogic.  If you need to configure Tomcat instead, see the ‘OAC Data Replicator 1.4 Getting Started Guide’ found on this page.

This article will not cover the installation of the Remote Data Connector tool iteslf.  For details on that, please see this article.

Although the Remote Data Connector tool is NOT a pre-requisite for this.  If you just wish to connect to Essbase sources with the remote data connector, and not to relational databases, then you can skip that step and just follow this article to install the Essbase component.

 

Install the Essbase RDC Components into Weblogic

The Essbase connector requires a minimum of version 1,3 of the Remote Data connector tool.  If that is already installed, you can continue with the steps below, otherwise please follow the steps in this article.

1. Download the OAC Remote Data Connector from this page on OTN.  The Essbase war file is included in the package.

2. Extract the files to a directory that is accessible from the weblogic server.

3. From within the Weblogic Enterprise Manager, select ‘Lock & Edit’, and then from within ‘Deployments’, select ‘Install’

4. In the ‘path’, navigate to the folder where the extracted files are available, and select the ‘essbase-remotedataconnector-v1-0.war’ file, then ‘Next’.

5. In the following screen, select ‘Next’ to continue

6. Select the target server for the installation.  In this example we will install it into the weblogic admin server, then ‘Next’.

7. On the next screen, the default selections should be okay, so hit ‘Next’

8. On the final screen, be sure the ‘Yes, take me to the deployment’s configuration screen’ is checked, then hit ‘Finish’

9. After the installation, go the the ‘Configuration’ tab, and then scroll to the bottom and change the ‘Context Root’ to be /essbase

Then hit Save.

10. Activate the Changes

11, In a browser with access to the server, enter the following URL to test the installation.

http://<rdc-server-host>:<rdc-server-port>/essbase/capi/<Essbaseserver-Name>/essbase-port

As an example:

http://mycompanyserver.com:7001/essbase/capi/myonpremiseessbase.mycompany.com/1423

 

If the test is successful, you will receive the following message:  CAPI HTTP v1.0

If the test is not successful, try restarting the weblogic server, then trying again.

 

Download Key File and Add to Path

1. In OAC, go to ‘Service Administration’ and then ‘Manage Connections’

2. Using the drop down menu in the far upper right of the screen, select ‘Get Public Key’ and download the file

3. Copy the key .pem file to the weblogic file system.  It is recommended to save it to the folder where the essbase.war file was installed into.

In this example, that folder was

/app/oracle/epm/user_projects/domains/EPMSystem/config/deployments

You can identify this folder by grepping on the file system for the ‘essbase.war’ file

4. The following environmental variable needs to be set for the session that starts the weblogic server. It should point to the folder where the key file was saved in step 3.

export PUBLIC_KEY_FOLDER=/app/oracle/epm/user_projects/domains/EPMSystem/config/deployments

5. Stop and restart the weblogic server where the essbase.war file was installed, in this case the Admin server, to make sure the path is set for that session.

6. From a command line, confirm the variable was set, by first identifying the process ID for the Admin server

ps -ef | grep weblogic

From the output, find the process ID for the correct weblogic server.  In this example PID is 11681

oracle 11681 11552 99 13:35 pts/3 00:00:34 /app/oracle/epm/jrockit_160_37/bin/java -jrockit -Xms256m -Xmx512m -Dweblogic.Name=AdminServer -Djava.security.policy=/app/oracle/epm/EPMSystem11R1/../wlserver_10.3/server/lib/weblogic.policy -Dweblogic.ProductionModeEnabled=true -XX:-FlightRecorder -da -Dplatform.home=/app/oracle/epm/EPMSystem11R1/../wlserver_10.3 -Dwls.home=/app/oracle/epm/EPMSystem11R1/../wlserver_10.3/server -Dweblogic.home=/app/oracle/epm/EPMSystem11R1/../wlserver_10.3/server …………..

Then run this command to confirm the path is set in that session.

strings /proc/PID/environ | grep PUBLIC_KEY_FOLDER

If it is, the command will return the path.  If no path is returned, you will need to stop the admin server again, and make sure it is started with the path set correctly for the session.

$ strings /proc/11681/environ | grep PUBLIC_KEY_FOLDER
PUBLIC_KEY_FOLDER=/app/oracle/epm/user_projects/domains/EPMSystem/config/deployments

 

As with the RDC configuration, make sure the firewall rules allow the traffic on the HTTP and / or HTTPS ports for weblogic to be redirected to the weblogic server.  In this case on the Admin server, the HTTP port is 7001.

 

Create a Direct Connection in Data Visualizer

1. In OAC, create a new connection.

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

3. In the DSN, use the same URL format from earlier – in this example:

http://mycompanyserver.com:7001/essbase/capi/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 to use Essbase

1. Open an RPD that already has an Essbase connection.

2. Edit the connection pool, and change the Essbase server to be a URL used previously:

http://mycompanyserver.com:7001/essbase/capi/myonpremiseessbase.mycompany.com/1423

The user name / password are for the Essbase connection, and not the weblogic server (in this example, weblogic was the Essbase user)

3, Upload the Repository to the OAC instance

4. Create an Analyses with the Essbase subject area.

 

Summary

This article walked through the steps to set up and configure a the Remote Data Connector to allow on-premise Essbase connections.

For other A-Team articles by Richard, click here

 

Add Your Comment