EPM and ORDS – Part 3

In the last part I went through getting up and running with administration using SQL Developer and delving into the “AutoREST” functionality.

In this part I will take it one step further and look at developing REST resources using SQL Developer and touch on protecting access to the resources with security.

In order to develop RESTful services using SQL Developer a user is required with the “SQL Developer” role, the process for creating a user and assigning a role is exactly the same as I went through in the last part for the administrator user.

This can be achieved through command line with the following syntax:

java -jar ords.war user <username> "SQL Developer"

Before you can start developing you need to be able to connect to a REST enabled schema and this can be done by creating a new development connection in SQL Developer.

Add a new development connection.

The username is the user that I have just created with the “SQL Developer” role.

The hostname/port and server path are for the web server hosting ORDS

The schema is an Oracle schema that has been enabled for REST services, I am using the same schema alias I used in the last part which holds the Planning Vision application.

A connection will be required for each REST enabled schema you want to develop REST resources for.

To access the development area in SQL Developer, go to View > REST Data Services > Development

This will open the REST Development pane

Click the connect icon and select the connection.

The password is then then entered for the user with the “SQL Developer” role.

Once connected REST Data Services will be populated with Modules and Privileges.

I am sure it doesn’t mean much at the moment but hopefully it should become clearer as I go through the steps of creating a new REST resource.

To do this we need to create a new module, a module is just a way of grouping REST resources together.

In the following example I am going to create a REST resource to view results from the job information table in the planning vision applications schema.

In the last part I used the same table but with the “AutoREST” functionality, this time I want to add some control over it and create custom SQL to return the results in a desired format

Selecting “New Module” will open the RESTful Services Wizard which guides you through creating a new resource.

The first part of the wizard is to specify the module information.

The module name as I mentioned is just a grouping for the REST resources together.

The “URI Prefix” identifies the resource module, so in my example I have called it “jobinfo” as it will be part of the REST resources which return results from the job information table.

You can see the URI that will be used to access the resource module being displayed as an example, it follows the format of:

http://ords_server:port/server_path/schema/module

I have enabled the “Publish” option so that once the resource has been created it will be available for use.

The Pagination Size is the number of rows to return in JSON format and the default is 25, you may ask well what is the query returns 100 records, well if you use the default then only 25 rows will be returned, this doesn’t mean you can’t access the remaining 75 rows, don’t worry I will go into more detail later.

I am not going to get into “Origins Allowed” but basically you can restrict which hosts can access the REST resource.

The next step is in the wizard to specify the template for the REST resource.

It is possible to have a number of different resources that are part of a module so this step is just to define the URI for the resource.

I have called it “view” as it is going to view the results of the job information table, now we have the full resource URL using the format:

http://ords_server:port/server_path/schema/module/resource

I am not going to cover Priority and Entity Tag as they are not relevant for any of the examples I will be showing, if you wish to find out about these areas then refer to the documentation.

The next step is to define method for the resource template, there are four options available.

GET is typically used to read data which is what I will be doing, POST is used for inserting data, PUT is for updates to data and DELETE for deleting data.

As I using the GET method there are a list of source types available.

Here are the definitions of each one:

• Collection Query -  Executes a SQL query and transforms the result set into an ORDS Standard JSON representation.
  
  
• Collection Query Item - Executes a SQL query returning one row of data into a ORDS Standard JSON representation.
  
  
• Query - Executes a SQL query and transforms the result set into an ORDS legacy JSON representation.
  
  
• Query One Row -  Executes a SQL query returning one row of data into an ORDS legacy JSON representation.
  
  
• Feed - Executes a SQL query and transforms the results into a JSON Feed representation. Each item in the feed contains a summary of a resource and a hyperlink to a full representation of the resource.
  
  
• PL/SQL - Executes an anonymous PL/SQL block and transforms any OUT or IN/OUT parameters into a JSON representation.
  
  
• Media Resource - Executes a SQL query conforming to a specific format and turns the result set into a binary representation.

There may seem like a lot of types to choose from but the first four are similar and only differ in the result set format and to be honest in EPM terms you will probably be using either one of them or the PL/SQL option, the others I doubt you would ever bother with.

For this example, I am going with the "Collection Query" type.

Depending on the source type selected the results can be returned either in JSON or CSV format.

I am going to stick with JSON for now but later I do have some CSV examples.

You also have the option to set the pagination size again and this will override the setting defined early at the module level.

The final step of the wizard displays a summary of the RESTful service that will be created.

It may seem like a lot to digest but most of the time you will probably use the same set of options and will start to fly through creating a new resource.

Once created the module, resource and method will be shown under Modules in the REST Development pane.

The final stage now is to create the SQL query for the resource and this can be done by selecting open on the resource method.

A SQL worksheet will open where you can define the SQL query.

I created a SQL query against the job info table HSP_JOB_STATUS which formats the results into a more readable format.

Just like with standard SQL Developer functionality the query can be run and the results viewed.

Once happy with the SQL statement and the results the module can validated and uploaded to the ORDS web application.

As the option to publish was enabled earlier in the configuration then after the module has uploaded the resource should be available to access.

To access the resource, I am going to use a free REST client again and it is as simple as entering the URL and firing off a request.

So now we quickly have the rows returned from the SQL query as a http response in JSON format

Putting that into a scripting world like PowerShell couldn’t be easier and with three lines of code we have the results in a nicely formatted table.

For a system administrator this provides quick access to what has been happening with jobs in planning and then can act on the results.

Let us take another example from Planning and one that comes up time and time again, audit records.

You can enable auditing through the planning web interface and select various options to track what has been happening in planning.

The document has always had the following about accessing the audit information:

“View results in the HSP_AUDIT_RECORDS table using a RDBMS report writer.”

Not the most helpful and sometimes can be a bit of pain to provide a process to access to this information.

Well ORDS can help out here and deliver the information in a structured format, once set up you don’t have to worry about it too much.

To demonstrate this, I am going to create a new module using pretty much the same configuration as the last example.

The only difference is the module and resource template name, there is no need for me to repeat with the same screenshots as the summary has all the information you need.

This time I have created a module called “audit” and a resource template “view”, the rest of the configuration is exactly same as before.

Just like earlier the new module, resource and method will be shown under Modules in the REST Development pane

Now to write a simple SQL query against the planning applications audit table.

I am trying to keep this as basic as possible, obviously you can write the SQL to be as complex as you want to match your requirements.

After uploading. the results can be returned in JSON format by issuing a GET request.

The pagination size has been kept as the default value meaning that 25 rows will be returned.

As part of the JSON response there is additional information provided on the current data set.

“hasMore” provides us with whether there are more rows of data available.

“limit” is the maximum number or rows to return in the result set.

“offset” is the starting point of the returned result set.

“count” is the number of rows that have been returned.

It is possible to control the paging of the result data using parameters.

So if we need to return rows 25 on wards the “offset” parameter can be used.

The returned “offset” value will reflect the parameter used in the request.

To override the value set for “pagination size” then the “limit” parameter can be set.

Now 100 rows have been returned.

It is possible to use a combination of parameters.

Just like I covered with the “AutoREST” functionality, filter parameters can also be applied to return subsets of data.

This can again easily be put into a script and in the following example I have filtered on data audit records.

To provide an idea of the number of records by type in the audit table I added a new resource template named “count” to the audit module.

The resource has the following SQL assigned and after validating the module it was uploaded again.

With only needing to change the resource URL from “view” to “count” a summary of records in the audit table can be returned.

Pretty quickly you can build up a set or resources that can help make those everyday EPM requests much easier.

Up to now these REST resources can be accessed by anybody that knows the URL which is not ideal so I will go through protecting these with privileges.

I am going to start by creating a new user with one of the available default roles:

RESTful Services - This is the default role associated with a protected RESTful service.

The method to create the user is the same as before and can be achieved from command line.

java -jar ords.war user <username> "RESTful Services"

The next step is to create a new privilege from within the REST Development pane in SQL Development.

This will open the create privilege window.

A name, title and description for the privilege can be entered, the title and description are optional.

I have selected the “RESTful Services” role so any users that have this role assigned will be able to access.

I have selected the “audit” module to be protected, it is possible to protect down to individual resources though.

After applying this privilege will appear under privileges.

The privilege still needs to be uploaded to the ORDS web application before it will become active.

To best demonstrate whether it is working or not is to enter the URL to the resource in a browser.

The resource cannot be accessed due to authorisation being required.

The user credentials can be added as basic authentication to the request header.

To achieve this depends on the method you are using to access the REST resource.

For the REST client I am using there is an authentication tab in the request.

This is then automatically base64 encoded and added to the request header

Adding the authorisation header can be achieved in scripting and the following is an example in PowerShell.

So now we have successfully protected the REST resource from being accessed without authenticated credentials.

I am going to leave the post here for today and in the next and final part I will go through further examples of creating REST resources across EPM products.