Friday, 30 September 2016

EPM and ORDS – Part 2

Moving swiftly on to part 2 in this series about EPM and ORDS, in the last part I gave a quick overview of ORDS and went through the process of getting up and running with it.

In this part I am going to look at administrating ORDS and enabling EPM related Oracle schema objects for REST access.

Now it is possible to do most of the administration and development in ORDS through PL/SQL statements but to make life much easier I am going down GUI route and will be using SQL Developer.

To be able tale advantage of this functionality you must use SQL Developer 4.1 or later.

If you want to administrator ORDS using SQL Developer, you must first configure an administrator user.

There are a number of predefined roles available in ORDS and the ones I will be interacting with are:

Listener Administrator - Users who want to administrate an Oracle REST Data Services instance through Oracle SQL Developer must have this role.

SQL Developer - Users who want to use Oracle SQL Developer to develop RESTful services must have this role.

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

So to be able to administrator we need to create a user with the “Listener Administrator” role and this can be achieved by the following command line:

java -jar ords.war user <username> "Listener Administrator"

The credentials will be stored in the ORDS configuration directory.

In SQL Developer a new connection to ORDS can be created with the admin user.

Under Tools there is menu called “REST Data Services” and then “Manage Connections

This will open a manage connections window and a new administration connection can be added.

The username that has just been created and the ORDS web application information is entered.

To access ORDS administration you can go to View > REST Data Services > Administration

Once ORDS Administration opens then right click and select ”Connect

This will open the connection window and the connection that has just been created can be selected.

Enter the administrator user details.

Now ORDS Administration will connect to the web application and be populate with all the configuration details

So now you can manage any of the administrative settings from within SQL Developer, any changes can then be uploaded back to the web application.

Let us move on to the more interesting stuff and look at “AutoREST” which means the automatic enabling of schema objects for REST Access.

The documentation provides the lowdown to what it is all about:

"If Oracle REST Data Services has been installed on the system associated with a database connection, and if the connection is open in SQL Developer, you can use the AutoREST feature to conveniently enable or disable Oracle REST Data Services access for specified tables and views in the schema associated with that database connection. Enabling REST access to a table or view allows it to be accessed through RESTful services.

AutoREST is a quick and easy way to expose database tables as REST resources. You sacrifice some flexibility and customizability to gain ease of effort. AutoRest lets you quickly expose data but (metaphorically) keeps you on a set of guide rails. For example, you cannot customize the output formats or the input formats, or do extra validation."

So basically with AutoREST you can quickly enable REST access for specified tables and views in a schema but you are limited to what you can do, I will be going through manually developing REST resources using SQL Developer in the next part as this provides much more flexibility.

To enable REST Services, open a connection to a schema in SQL, I am going to be using the planning vision application schema as an example.

Right click the connection and select “REST Services” > “Enable REST Services

Enable and the schema and provide a schema alias which is used as part of the REST URL to access the schema objects.

I did not enable “Authorization required” as I will be looking at restricting access in the next part.

A summary is then displayed.

It is possible to view PL/SQL that has been generated and will be executed as all that SQL Developer is really doing is running PL/SQL behind the scenes.

All being well the schema should be REST enabled.

The schema has been enabled but there is not much we can do yet until we enable tables or views.

To enable a table then all you need to do is right click the table name and select “Enable REST Service

In my example I using the planning application repository table “HSP_JOB_STATUS” which hopefully you can guess what type of information it stores.

The setup is pretty much the same to enabling a schema except this time it is an object.

Once again a summary of the configuration is shown.

To understand what is going to be executed you can be view the SQL tab.

Very quickly we have enabled a table so we can start getting somewhere with accessing the REST services.

As I will be accessing REST resources I am going to be using the free REST client called boomerang which is available for chrome but there are many different REST clients available.

To view all the REST enabled tables or views in a schema you can access the REST resource with the following URL pattern:

GET http://<HOST>:<PORT>/ords/<SchemaAlias>/metadata-catalog/

In the last part I configured OHS to proxy requests to ORDS so I can access the REST services over the same host/port as EPM products, this makes is extremely useful if you are providing REST access to a EPM user base as there will be no need to open firewalls and manage additional URLs as it will be the same as the one used for EPM.

The schema alias is the one that was defined earlier.

The response in JSON format provides all the table/views that have been enabled which is only one at the moment, URL links are also included to access the REST resource on each enabled object.

You will notice there are two links for each table, the canonical link retrieves the metadata for the table, the format is:


This returns the following information.

With one request we can find information like primary keys, columns and column types for the table.

Putting this into the scripting world is extremely simple as well, I am going to provide an example using PowerShell just because it is accessible on pretty much any machine running Windows, there is an IDE available and it is easy to work with, you can use REST with most scripting languages so feel free to pick one you are most comfortable with.

So with a few lines of code I can return the table structure and then process it how I want.

To access the records in a table is just as easy and follow the following format:

GET http://<HOST>:<PORT>/ords/<SchemaAlias>/<ObjectAlias>/

Each row in the table in returned as a JSON object and contains the column and data.

This does not require much effort to put into a script and return only the columns we are interested in.

With a few lines of code, I have information about the jobs that have been run in planning, ok I could convert the run status value to a more meaningful description like completed but hopefully you get the idea of simple this is.

To return a table row using the primary key then all that is required is the primary key value to be added to the end of the URL, the format is:


The primary key in the HSP_JOB_STATUS table is the JOB_ID column so an example to return a row by job id would be:

There a large amount of different options available to filter queries and this can be achieved by using the parameter q=FilterObject , where FilterObject  is a JSON object providing the information to filter on.

It is easier to show with an example, let us filter on job name and return rows where the name is “Refresh Database”, the JSON object would be:

{"job_name":"Refresh Database"}

then add this to the URL as a parameter

If I wanted to return all the jobs that have failed so that would be any with a run status value of 3 then I could use the equals operator.

Or using a script

There are far too many filtering objects to go through so please check out the documentation for further details.

I have only touched the surface with what you can do with “AutoREST” and all the examples have been using the GET method, other methods can be used such as POST to insert data, PUT to update data and DELETE to delete data.

Hopefully you have seen the potential and how easy it is to enable a table/view for REST, not forgetting this can also be applied to any of the EPM repositories.

The “AutoREST” functionality is great but can be limited so in the next part I will go through creating REST resources with more flexibility by using custom SQL and PL/SQL, I will also cover restricting access with security.

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 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:


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.

Monday, 22 August 2016

Smart View and Essbase Runtime Substitution Variables

Back in August 2015 when Smart View was released the readme had reference to new functionality for runtime substitution variables, the readme contained the following information:

"In Smart View, you can now execute Oracle Essbase calculations that, when launched, prompt you to enter variable information, called runtime prompts. Calculation scripts and runtime prompts are created by your Essbase administrator for your specific system.

Essbase administrators define calculation scripts so that Smart View users can work with runtime prompts. For information on setting up your calculation scripts with runtime prompts, see the Oracle Essbase Database Administrator's Guide, “Using Calculation Scripts with Runtime Substitution Variables in Smart View.”

After a while it was confirmed this functionality was not yet available and the information was subsequently removed from the readme.

The Essbase patch readme contained reference to runtime substitution variables in the documentation updates section:

 “Correction: Using Runtime Substitution Variables in Calculation Scripts Run in Smart View
The following information updates the “Using Runtime Substitution Variables in Calculation Scripts Run in Smart View” section of the Oracle Essbase Database Administrator's Guide for”

I have searched for the section in the Essbase documentation and could not see it but it is a different story if you look at the Essbase 12c documentation.

At the time of writing this the latest patch release of Essbase and the functionality still doesn’t look available which I am sure will change in a future patch.

Currently Essbase 12c is only available in OBIEE 12c and I wrote a detailed post about it last year looking at the changes.

So are runtime substitution variables available in Smart View with Essbase 12c, well this would be a short post if they were not :)

It is worth pointing out that it doesn’t look like it is the version of Smart View or Essbase that is holding back the functionality in, it is code differences in Provider Services that are making the difference.

In this post I thought I would look at a few extremely simple examples of using the runtime substitution variable functionality in Smart View.

The examples are using Smart View and Essbase/Provider Services, there is no EAS in 12c but the good news that EAS is now supported with Essbase which makes life much easier, you are still under the constraints of OBIEE security but at least you can use the majority of functionality once the Essbase 12c server has been added in the EAS console.

In OBIEE 12c you can still use a Smart View connection URL and the only difference is the default port will be 9502.

The same goes for private connections:

The default Essbase cluster name is “bi_cluster

Before going any further with Smart View we need a calculation script to run that includes runtime substitution variables.

The syntax for using Smart View runtime substitution variables in a calc script is:

   rtsv = POV
         <type>member | string | number</type>
         <choice>single | multiple</choice>
         <allowMissing>true | false</allowMissing>

The different options within the tags will become clearer as I go through some examples.

Let us start with one runtime sub var.

I will breakdown the above definition of the sub var
“Market = POV” - A variable called “Market” has been defined which is set to POV, when set to point of view (POV) it basically means it will pick up information from the current member(s) within the grid in Excel.

<RTSV_HINT> and <svLaunch> are a requirement of using runtime sub vars in Smart View.

<description> just describes the runtime sub var and is shown in the runtime prompts section in Smart View.

<type> can be set as member, string or number, in this example I want the sub var to be a member.

<dimension> this is the dimension that the sub var is defined against which in my example will be the “Market” dimension

<choice> has been set to single to assign one member to the sub var, the other option is multiple.

<alllowMising> the definition of this is

“Specifies whether to allow or suppress data cells for which no data exists in the database”

To be honest I have not seen this actually make any difference whatever it is set to, I am probably missing something obvious.

So in summary all that definition means is that the current Market member in the Excel grid will be picked up and assigned to the “Market” sub var, the sub var is then used in the FIX.

It makes more sense with a demo of it in action, I have created a simple retrieve in Excel.

The market dimension member in the retrieve is “Florida” which should be picked up and assigned to the sub var.

Select “Calculate” to bring up the calculation scripts window.

Once the calc script I have created has been selected a “Runtime Prompts” section is displayed.

The description of the sub var defined in the calc script is shown and the member “Florida” has been correctly picked up from the grid.

The calc script was successfully run and a quick check of the Essbase application log confirms the member “Florida” was assigned to the sub var and used in the fix.

Aggregating [Product(All members)] with fixed members [Market(Florida); Scenario(Actual)]

A refresh of the sheet shows the product dimension has been aggregated.

The runtime sub var definition can be easily expanded and this time I have added a Scenario variable into the script.

It is probably worth pointing out that if you do validate the script in the console you will be hit with an error.

The log will contain:

Error: 1200315 Error parsing formula for [POV] (line 5): invalid object type

This error can be ignored but one to watch out for if you need to validate a complex script.

Anyway, back to the example, if I run the calc on the same grid in Smart View this time the current Scenario is picked up.

After running the calc the log verifies the sub var was correctly assigned.

Aggregating [ Product(All members)] with fixed members [Market(Florida); Scenario(Actual)]

It is working fine for single members but how about multiple members.

This time I updated the definition to set choice to multiple for the Market sub var.

The Excel grid now includes two Market members.

Selecting the calc script now displays a different message in the runtime prompt box.

The message does not quite it on and expanding the window makes no difference but the full message is

“Market Dimension Members on Row/Col/POV will be used.”

After launching the calc the log confirms that both Market members were successfully assigned to the sub var.

Aggregating [ Product(All members)] with fixed members [Market(New York, Florida); Scenario(Actual)]

So what happens if choice is set as “single” but there are multiple members in the grid.

I set “choice” back to “single

In Excel I have purposely not selected any Market member cell.

Select the calc script and the following warning is displayed.

The calculation script window is still displayed with the Market runtime prompt empty which cannot be changed so the only option is to close the window.

This time I have selected a Market member cell.

No warning this time and the selected member is assigned.

Ok, moving on to some of the other options available in the runtime sub var definition.

If you don’t want to use the POV method and manually enter the value for a sub var then the “string” type can be used.

In the above example “type” has been set to string and POV updated to a Scenario member.

Interesting that in the patch readme the documentation update states:

“The value of the runtime substitution variable must be set to POV; it cannot be set to a member name.”

The way I read that is what I am doing now cannot be done or maybe as usual it is some cryptic message that can only be understood when all the planets align.

The “choice” value will be ignored and can be removed as it is only valid when “type” is set as “member

The calc script was run again and now the runtime prompt is populated with member set in the sub var definition.

The runtime prompt can be updated or left as the default.

A comma separated list of members is also a valid input.

A quick check of the log verifies the string type is working.

Aggregating [ Product(All members)] with fixed members [Market(Florida); Scenario(Actual, Budget)

If member selection is required, then the “type” can be set back to “member

The runtime prompt will now be enabled for member selection.

As “choice” has been set to single only one member can be selected.

As expected the member is correctly assigned to the sub var in the calc script.

Aggregating [Product(All members)] with fixed members [Market(Florida); Scenario(Actual)]

To select multiple members then “choice” can be updated to “multiple

The member selector will now accept multiple members.

Aggregating [ Product(All members)] with fixed members [Market(Florida); Scenario(Actual, Budget)]

On to the final “type” which is number

In the above example the sub var was defined as “SalesRate” and given a default value of 1.2, “type” was set as “number” and the “SalesRate” sub var is then used in a calculation.

The following Smart View grid was set up in Excel.

This time when selecting the calc script a new runtime prompt is displayed with the “SalesRate” sub var.

The default value can be overridden by entering a new value.

The calc ran successfully.

Calculating [ Measures(Sales)] with fixed members [Product(100-10); Market(New York, Florida); Scenario(Budget)]

A refresh of the data and confirmation that Sales data has been multiplied by 1.2.

If you are not aware it is possible to include logging of the runtime variable values in the application log by using the ENABLERTSVLOGGING configuration setting.

I added the following to the Essbase configuration file so runtime sub vars would be logged for the Sample application.


After running the calc script again the log contained the runtime sub var details.

Executing calc script [] with runtime substitution variable { Market = "Florida","New York" }

Executing calc script [] with runtime substitution variable { Scenario = Budget }

Executing calc script [] with runtime substitution variable { SalesRate = 1.2 }

I did notice a bug in Smart View when using multiple members with the sub var set as POV and also having a sub var defined as a member.

There is no problem when the member selection is not selected so the defaults are kept.

If you do open the member selection and then run the calc the following error will be generated.

The Essbase application log highlights the issue as instead of passing the members from the grid through it has sent the default message “Market Dimension Members on Row/Col/POV will be used”

Invalid Calc Script syntax [
FIX(Market Dimension Members on Row/Col/POV will be used, "Budget","Actual")

Smart View basically sends XML to provider services and by capturing what is being sent from Smart View you can see it is incorrect

It is an issue with Smart View and not provider services though I am sure it will be fixed in a future Smart View release.

Well I think I am going to leave it there for today, once the functionality is available in I will update this post to reflect that.