Sunday, 25 September 2016

EPM and ORDS – Part 1

Over the last year or so I have focused on the different Web Services options available in EPM products and paid particular interest to REST which in my opinion is the clear winner over the SOAP protocol and the standard for the future.

In the ever changing world of EPM the need for REST Web Services is becoming more apparent, you only have to take a look at EPM Cloud and see that REST is the core for automating processes and moving around metadata and data.

Oracle are having to move quickly to implement more and more RESTful based functionality with the rise of their cloud offerings, Oracle spent so much time bringing EPM products together that they didn’t really focus on Web Services when they should have but now that is all having to change.

Today I am going to continue with the REST theme and look at Oracle REST Data Services (ORDS) which I believe can help provide solutions to common situations that you encounter with EPM products.

So what is ORDS:

"Oracle REST Data Services (ORDS) makes it easy to develop modern REST interfaces for relational data in the Oracle Database and now, with ORDS 3.0, the Oracle Database 12c JSON Document Store and Oracle NoSQL Database. ORDS is available both as an Oracle Database Cloud Service and on premise."


So in basic terms ORDS is a web application that allows you to execute REST resources against Oracle databases.

You see there is a catch that this is not going to be everybody as ORDS will only run against Oracle databases, the good news though is that if you are licensed for Oracle database then you are also licensed to use ORDS.

As the majority of EPM products store information in a relational database then being able to simply make a call over HTTP to return something meaningful is only going to be a good thing.

I have lost count the number of posts I have seen or questions that have been asked about running SQL against the EPM repositories and this is where ORDS can help put some control over it and at the same time take away the need to access the database directly, we all know how much DBAs love giving out access to users.

Wouldn’t it be great if you could deliver the results from all those SQL queries that you have been asked to run without having to develop some cumbersome process and also provide access restrictions using inbuilt security options, well hopefully I am going to show how this can easily be done with ORDS and look at some examples to everyday EPM scenarios.

It may mean nothing to you at the moment but after seeing some examples of where ORDS can help I am sure you will be won over.

I am going to start out with going through the installation which to be honest is not complex but will need some discussions with your DBA as to get ORDS configured you will need administrative database privileges.

First of you all you need to download ORDS but don’t worry it is not a monster of an application and weighs in at less than 60mb.

The version of ORDS I am using is 3.0.6 which is the latest at the time of writing this post

As with any product there are system requirements that need to be met and for this version of ORDS they are:
  • Oracle Database (Enterprise Edition, Standard Edition or Standard Edition One) release 11.1 or later, or Oracle Database 11g Release 2 Express Edition.
  • Java JDK 1.7 or later.
ORDS requires a Java EE application server and the following are currently supported:
  • Oracle WebLogic Server - 11g Release 1 (10.3.6) or later
  • GlassFish Server - Release 3.1.2 or later
  • Apache Tomcat - Release 7.0.56 or later
There is also the option of running ORDS in standalone mode which does not require a Java EE application server but this is only suitable for develop use so it is required to go down the supported application server route.

As ORDS is an Oracle product then naturally WebLogic Server is supported which you may think great as I already have WebLogic Server installed as part of EPM, not so fast, first of all it is only limited-use license that is included with EPM which means you can’t deploy ORDS into your existing EPM domain, ORDS also requires Java JDK 1.7 or later and as we all know EPM is so far behind with the version of Java and is still running with version 1.6

There is no stopping you if you are fully licensed for WebLogic Server but you would need a separate install that is running a supported JDK version.

I have chosen to go down the Apache Tomcat route which to be honest is extremely simple to use and get up and running with.

A quick overview, download, run the installer, select the type of install and components which can be left as default.


Select the ports to run Tomcat on, I changed from port 8080 to 8020 as it was already being used on the machine I was installing on.


Select the supported Java location.


Select the location to install Tomcat to.


And for standard functionality that is pretty much all there is to it.

So back to ORDS, once downloaded extract to a desired location.


ORDS can be installed and configured through command line or with SQL Developer, I am going to use command line but in the next part I will introduce SQL Developer to make life easy administrating ORDS.

There are two options for installing ORDS, simple which uses a parameter file and advanced using command line prompts.

I have chosen advanced but it is hardly advanced once you see it.

Before starting out you will need an Oracle database user with SYSDBA privileges which can be temporary as it is only required for the configuration.

To understand what database users and privileges the configuration creates then refer to the documentation which definitely will be useful in discussions with your DBA.

To start installing and configuring then make sure you are using a supported JDK and run the following command:

java -jar ords.war install advanced

The first part of the configuration is to enter the location to store the ORDS configuration data and the Oracle database connection information.


Next keep the default to create the REST Data Services schema as it has not been created yet.

Enter a password for the ORDS_PUBLIC_USER.


The ORDS_PUBLIC_USER is the database user that ORDS uses to communicate with the database and invoke RESTful services in the ORDS enabled Oracle schemas.

Next steps are to enter the details for the SYSDBA user and provide tablespace information or keep the defaults.


The final input is to select 2 as we are not using Application Express (APEX)

ORDS was formerly known as APEX listener and part of Application Express, ORDS can now be independent of APEX which is how I will be using it.


That is the configuration done, pretty simple.

A configuration file ords_params.properties will have been created.



To validate the configuration, you can run the following.


To deploy ORDS in tomcat is once again extremely easy.

Move ords.war into the Tomcat webapps folder.


Tomcat will then automatically deploy the ORDS web application


To test the deployment was successful go to the following URL for ORDS:

http://<hostname>:<port>/ords/

Do not worry about the 404 Not Found message as this is to be expected when ORDS is not being used with APEX.


As a final step I added the ORDS web application to the OHS EPM configuration file.


This allow ORDS to be accessed through OHS so end users can go through the same route as they would to access EPM products and no need to start worrying about opening ports and additional URLS to manage.


I am going leave it there for today now that ORDS is up and running, in the next post I will start looking at administrating ORDS and creating REST resources against EPM database repositories.

No comments: