EPM and ORDS – Part 4

On to the final part of the series looking at Oracle Rest Data Services (ORDS) and EPM, a quick recap of the previous posts:

Part 1 – Overview of ORDS and process to get up and running with it.

Part 2 – ORDS administration using SQL Developer and “AutoREST” functionality.

Part 3 – Developing REST resources using SQL Developer and protecting resources with security.

In this final instalment I will go through a few REST examples using ORDS with EPM and cover some of the functionality that has not been discussed up to now.

I am going to assume you have read through Parts 1 to 3 so have a good understanding of what ORDS is all about.

In the last part I went through an example of creating a resource against a planning applications audit database table, the audit table is probably one of the most common tables you would want to access directly as currently with on-premise planning there is no access to the data through the user interface.

So say you have provided one or more REST resources to the table and delivered this in a structured format, up to now I have only used JSON as the data format but it is also possible to change this to deliver the content in CSV format.

To do this I am going to take my existing “audit” module and select “Add Template”

I am going to name this resource “expcsv”

In the “Data Format” drop-down list there are the options of JSON or CSV so for this example I have chosen CSV

I don’t believe the pagination size is honoured when using CSV so be careful to restrict what is returned using SQL if the results contain a large amount of rows.

I copied the same SQL from the example in Part 2 and uploaded the module.

If you enter the resource URL into a browser or REST client the response will be outputted in CSV format.

In scripting terms, it is extremely simple to generate a CSV file from the REST resource.

Now you have a locally generated CSV file, in the desired format and can be delivered whenever required.

I know this would be beneficial for many planning on-premise users out there.

Let us take another example that could be useful in the planning community and that is dimension metadata.

There are a number of standard ways to get dimension information out of planning but it is not so customisable and this is where SQL and ORDS can deliver.

I have created a new module and template in exactly the same way as all my examples.

As for the SQL to generate if you have a search on the internet there are some resources out there which will get you on your way.

I have created a SQL query to return product member information from the demo planning application.

The query generates the following output:

I have kept it simple and the SQL can be enhanced to output any of the member property information.

Once the module has been uploaded the dimension metadata will be available in JSON or CSV format depending on the data format type chosen.

Once again this is very easy to translate into a script and with a few lines of code you have access to the metadata which you can then further process if required.

Right, time to take it one step further and look at changing settings in a planning application, please be aware this is just an example and I am only demonstrating it to show what is possible with ORDS.

A common setting change in a planning application is maintenance mode, it is certainly possible to do this via command line but wouldn’t it be nice to have a REST resource to achieve this functionality.

I am going to create a new module called “maint”

In the template I am going to use a bind variable called “type” using the format {varname}

When using the resource, you put a value into the URL for the variable and then this is passed in and the value can be accessed which I will demonstrate shortly.

For the first time I am going to be using “PL/SQL” as the source type as this allows us to get a little more creative than just using SQL.

With the resource created the PL/SQL can be created in exactly the same way as when using SQL.

The table HSP_SYSTEMCFG has a column called LOGIN_LEVEL which basically relates to the maintenance mode.

If the value is 0 then all users can access the planning application, if 2 then only administrators can access and if 3 then just the owner.

There is a possible value of 1 which means interactive but that is not part of the functionality in the user interface or through the command line so I am leaving it out.

In the script there is the variable “:type” and the value in the REST resource URL will be passed into this variable, I am using friendly names for the variable instead of a number.

As planning uses a caching system it is not enough to just update the table, to get around the caching the HSP_ACTION table can be populated to update the cache, if you want to understand more about updating the cache then you can read all about it here.

To put this into action couldn’t be easier and can be achieved in a browser or a one-line script.

To put the application into administrator maintenance mode the URL includes “admin”

If a success response is returned the maintenance mode should have been updated

A quick check in the planning application confirms the setting has been applied.

The application will not be accessible for planning users.

By entering “all” into the request the application be can be returned to all user access.

The maintenance mode setting has been successfully updated.

Users can now access the application.

So this solution provides an effective alternative to using the command line utility without the hassle of having to run it from the planning server.

I know that the focus up to now has been around planning so let us look at a few other EPM areas.

The Shared Services database has a lot of useful content, take LCM for example and migration status information.

The migration status report can be accessed through Shared Services but there is no option to automatically generate this information for download and no way to customise it.

ORDS can come to the rescue again, I created a new module and two resources, one for returning LCM status information and the other for LCM failures based on passing in the migration ID.

The SQL is based on tables starting with "LCM_*" and the output provides more information than is currently available through the Shared Services report.

In scripting terms, it is doesn’t take much effort to produce an enhanced migration status report.

Filtering the returned rows to show only failures could be achieved using parameters in the URL or in a script.

The response provides the migration ID which then can be used in the second resource to return detailed information about a failure.

This provides quick access to details on why the migration failed without the requirement to log into Shared Services.

A report could be automatically generated and distributed which is currently not possible through Shared Services.

Moving on to FDMEE, a mass of information to choose from but how about batch scheduler details which is currently not easily obtained without having to log into the ODI studio or console.

A bit of custom SQL and batch schedule details can be returned with ease.

The same goes for scripting the output.

Next on to Calculation Manager, wouldn’t it be nice to be able to get a report of all the rules and the script content.

This can be achieved by either going to the calc manager database tables or the planning applications, I found it easier to parse the entries in the planning application tables.

The following example filters on a specific rule, if you wanted to return all the rules then just remove the filter parameters in the URL.

To replicate this with a script I created the following.

I then redirected this to a CSV file.

Now I have list of rules and script content which can be opened and viewed in Excel.

Not a bad way to document business rules and can be run whenever required without any convoluted processes

With all the examples I have been using the GET method so how about inserting data using the POST method, well say I have an automated process that loads member information from a database table, an easy way to populate this table would be to put it into the hands of ORDS to manage.

One way of doing this could by using the “AutoREST” functionality and all that would be required would to enable the database table for REST services and create an alias.

I created an alias of “prod” for table “VISPROD”, to insert a row into the table I could POST the following JSON.

The table will then have the row inserted.

This is fine for small inserts but what if I want an easier method to insert multiple rows using CSV format.

The good news is “AutoREST” also comes with a batch load option and is available to any table that has been REST enabled.

I can insert multiple rows using CSV format with the following operation.

The batch load functionality has many parameters available and in the above example I am truncating the table before inserting.

The response will include the number of records processed and failed.

The rows should then be visible in the table.

If I wanted to load data directly from a file, then this is pretty simple to do as well.

The post to the REST resource would just need the CSV file to be included.

To delete a row from a table can be achieved using the DELETE method and including filter parameters to specify the row.

The response will confirm the number of rows deleted.

Well I am going to leave it there for the series on ORDS as I have covered a hell of a lot, as you probably gathered I am a big fan of the product and if you are an Oracle database customer then I really don’t see why you wouldn’t consider using ORDS as there is no extra cost involved, there is so potential to assist in delivering beneficial solutions without a big hassle.

If you are interested in understanding more about ORDS and how it could help you then please feel free to get in touch.

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.