EPM Cloud - Groovy and Web services continued

In my last post I went through the recent addition to EPM Enterprise Planning Cloud, which allows Groovy to interact with external web services. In the post I concentrated on connecting to the same Cloud instance to use the available REST resources.

With the Groovy functionality, it is possible to take advantage of any of the growing list of EPM Cloud REST resources. For example, you could put together a list of common admin tasks which then could be accessed from a single location, like a form or running rules directly. I did put together a demo where a selection of admin tasks could be run from a menu attached to a form.

The inclusion of Calc Manager variables further enhances the functionality in some of the tasks.

You could argue that it is possible to do this all from within the UI or with scripting and EPM Automate. I agree, it is just there as an example to make life easier and maybe spark off some ideas.

I am not going to cover this in any more detail in this post as my last blog provided enough information to get started using the Groovy functionality, add this to all the posts I have written about REST API resources in EPM Cloud and you should be well on your way. If you do require assistance though, then please feel free to get in touch.

The idea of this post is to demonstrate how it is possible to connect to an external web service and consume data that can possibly enhance a planning process.

As usual I want to try and keep things as simple as possible, so I have come up with an example of extracting exchange rate data and then pushing this to planning. There are lots of sites available that offer exchange rate data using a REST API. There are free and paid options available, to get all the features they mostly have to be paid for, which you would probably choose if you wanted to load reliable exchange rate data on a regular basis. I did find a good site which was full of features, but the free option only allowed connection over HTTP and it looks like if you are connecting to an external web service from an EPM Cloud instance it must be over HTTPS.

Anyway, I did find a site that provided enough detail to put this example together.

The objective is to extract exchange rates for the input currencies for a planning application, except for the base currency which is GBP. The rates will be extracted against the last day of a month which will be driven by month and year substitution variables. So, if I take the following form I want to populate the ending rates for June.

Before jumping into the Groovy it is worth taking a look at the REST resource to return exchange rates. I will be using a REST client to demonstrate.

The REST resource I will be using requires an API key as its means of authentication, this is included as a parameter.

A parameter is required to convert from the source to target currency, the format is q=<FROM_CURRENCY>_<TO_CURRENCY>

The following example returns the exchange rate from GBP to Euro for the current day.

This is more detail than I actually require in the response, to restrict the information returned there is a parameter that can be included.

Adding the parameter and value “compact=true” returns the following response.

It is possible to include multiple conversions by comma separating the currencies.

As I stated earlier, I want to return the rates at the last day of a month, to do this I can include a date parameter with the date in the following format.

So now I am able to return exactly what I am looking for with a REST request. Time to move on to extracting this data from within Planning.

I set up a new connection to the web service from the planning UI. As the API key and compact mode will be static, I included them as parameters in the connection configuration.

Now on to the Groovy script which will call the REST resource to return the exchange rates, this will be for the last day of the month defined by month and year substitution variables. The response from the REST call will be stored and a data grid will be built and populated with the exchange rate data.

The substitution variables are:

I will break the script into chunks and explain what is happening. I must stress that I am sure there are different ways of writing the script, and I am not saying this is the way it must be done. It has just been written for demo purposes.

In the first section the “Plan1” cube is stored as this is required throughout the script. The exchange rate data will be held in this cube. The substitution variable values are also returned and stored.

If this section is run the substitution variables are written to the job console.

From the substitution variable values, the last day of the month is calculated and the date stored in the correct format for the REST resource parameter.

There is probably a more elegant way of returning the date, but it works. :) In the past I have also subtracted a day from the 1st day of a month to return the last day of the previous month.

If this section is run, the Job console prints out the date that will be used as part of the REST request.

The next section will extract the currencies which require exchange rate conversions. These will be based on the children of the “Input Currencies”.

The children of “Input Currencies” are retrieved and the member names stored. The base currency is then removed from the list of members.

Running this part of the script will print the currency members I want exchange rate conversions for.

Next, the correct currency format for the REST API is generated. Each currency code is prefixed with the base currency and an underscore.

This outputs the following to the job console.

The REST request can now be made with the above information. I covered this is in detail in the last post so please refer to that if it is not clear. The request is made against the “exchange rates” connection including the parameters.

This generates the following URL to make the get request against.

https://free.currconv.com/api/v7/convert?q=GBP_CAD,GBP_EUR,GBP_SEK,GBP_USD&date=2019-05-31&apiKey=2487c34bd2e74b3e226c&compact=ultra

The response in JSON format is stored, the JSON is exactly the same as I provided earlier with the REST client example.

The JSON is converted into a key/value Map so the values can easily be processed.

The Map contains the currency as a string and the date and exchange rate value in another Map.

Now that the exchange rate data has been extracted and stored, I can move on to building a data grid which is the equivalent of a form grid to store the exchange rate data.

The above would produce the following POV and column in terms of a data form.

The final section iterates through each of the stored currencies and values. The currency code is extracted as it stored with the base currency. For example, if GBP_EUR is stored then EUR will be extracted from the string. These are added as rows to the grid. The grid is then saved so the data is written to the cube.

This will have basically produced the following rows of data in the grid.

Let us now put it all together. The rule is added to a menu and the menu included in the exchange rate form.

A simple right click in the form displays the menu option which, when selected, runs the rule.

There we have it, seamless loading of exchange rates using Groovy and a REST API.

The Groovy and REST API functionality opens up lots of new possibilities, look out for more in the future!

EPM Cloud – Recent Web service and Groovy additions

There have been a few updates to EPM Cloud recently that are definitely worth highlighting. New functionality has been added, allowing connections to Web services to be created. These on their own are not much use, but with new classes added to the Groovy API, it is now possible to interact with these Web Services. This opens a whole new array of possibilities.

In the 19.06 release the following was contained in the ‘what’s new’ document:

“Service Administrators can now create connections to external Web services and specify optional query or header parameters while defining external connections. These connections can be referenced or used in a Groovy script to create a communications link between the Groovy script and the external Web resource.”

It was certainly possible to add new connections but there was not much information on the Groovy side. The API documentation was certainly lacking in detail.

Moving on to the 19.07 release and the ‘what’s new’ document had the following snippet:

“Planning Groovy object model APIs have been enhanced with the ability to call external REST APIs.”

This time the documentation contained details of new Groovy classes to work with REST APIs.

As you have probably noticed I have been blogging about REST APIs for a number of years now, so this new functionality has really taken my interest.

If you think about it just from an EPM Cloud perspective, there has been a large investment in developing and enhancing the REST API capabilities. EPM Automate is built on top of the REST API. With the new Groovy functionality, it allows you to call any of these REST resources. If you have multiple EPM cloud instances, then these resources can now be called from planning business rules. It removes some of the dependency for them to be run externally by scripting or EPM Automate. It doesn’t even have to be a separate cloud instance; the REST resources can be called against the same instance, which I will get on to later.

It doesn’t stop there, with the majority of cloud providers having a REST API of some sort then this has opened up lots of new areas to explore. This could be loading/exporting data or metadata to a cloud instance, running integration or administrative tasks. You could have a cloud-based ERP system that you want to interact with or maybe OAC that is heavily built on REST which I have covered in previous posts.

There is a downside though, this is based on Groovy which is only available in Enterprise Planning. It is frustrating that Groovy is only available in EPM Enterprise Cloud Services, I don’t understand why it can’t be included with standard and I know that I am not alone in feeling this way. Hopefully Oracle will one day see sense and allow it in both Cloud Services.

Going through a couple of examples should provide a greater insight to these new features and make it clearer how it operates.

Let us start out by calling a REST API resource against the same planning instance. To keep it simple I will call the resource that returns basic information about the application.

Using a REST client, I can make the following GET request.

This same REST call can be achieved using a Groovy script. There are two ways to make an HTTP/HTTPS connection, an on-demand connection which requires the full URL and username/password to be included; alternatively, a named connection can be used which can be set up from connections in the planning application. I am going to concentrate on the named connection as it does not expose the credentials in the script and is better for maintenance as it can be reused across multiple scripts.

If you navigate to connections and create then you will see an option for “Other Web Service Provider”

The connection name will be the one that is referenced from the Groovy script.

The URL can be the fully qualified external name for the cloud instance. As I am going to run the REST calls against the same instance and know the port the web server is running against, I have defined the URL with localhost. It works well and doesn’t require the URL to be updated if the connection is migrated across instances, so I am sticking with it until Oracle say otherwise :)

Under advanced options, parameters and headers can be defined. This can also be set in the Groovy script, if it is something that is going to be used in all calls then it probably makes more sense to define it in the advanced options.

I could have put in the full URL to the REST resource and had different connections for different types of calls but as each connection requires credentials, it would become a maintenance headache to keep updating them when the password changes. It is one of the downsides of connections that once the password has changed it has to be manually updated.

Take note that to use all the REST resources, then it should be a service administrator account that is defined in the connection. Also think about who has access to the Groovy rules that will be calling the REST resources as behind the scenes it will be the administrator account being used.

Before I get on with the Groovy side of things I want to point out that I am not a Groovy programmer and there will definitely different ways to write the code. I just want to try and keep things simple and I will let you get carried away with the coding. I will say that Calculation Manager has definitely not been developed as an IDE for writing Groovy and patience can be required. It can be challenging when you don’t know all the classes that are available and with static type checking enabled it can push you to the limit :)

To replicate the earlier REST request which returns application information is extremely simple in Groovy.

In the above example, the connection details that were just configured are retrieved, the URL is then concatenated with the path contained in the get method. A GET request is made and the response is returned as a string. The body of the response is then outputted and can be viewed in the job console.

You can see that the JSON response is the same as the one shown earlier using the REST client. The JSON response is not the easiest to read and process, there are various classes available to manage JSON. One of these is "JsonSlurper" which I will use in the following example:

It looks a bit messy because of having to define the types like Map and List because static type checking is enabled. Basically, the JSON body of the response is converted into a Map which means it will contain key/value pairs. The items key is extracted as a list and the first index entry converted back to a Map, the value from the name key is then extracted, stored and printed. I am sure it can be simplified, though with this type of JSON response I prefer to use different classes which I will include in later examples.

The HTTP response also has methods to extract the headers, status code and text.

This produces the following output.

The status code is useful for checking the HTTP request returned successfully.

As mentioned earlier there are two ways to make a connection, the named connection which all the previous examples have been using. It is also possible to use an on-demand connection, based on the same REST resource call, this can be achieved with something similar to:

// call REST resource to return application information
HttpResponse response = connection("https://<cloud_instance>/HyperionPlanning/rest/v3/applications","username","password").get().asString()

My choice when using REST calls with authentication would be to use the named connection so the credentials are not displayed in the rule. It also makes more sense to manage the connection details centrally instead of having them in multiple rules.

The previous examples have provided the basics of making a REST request but just returning the application is not really that exciting or useful. For example, what if I wanted to run a Data Management rule from a form which will load data from Fusion Cloud or a file or another cube in the application or even a different EPM Cloud instance? Soon I will be able to add on-premise sources to that list with the forthcoming EPM Agent. Anyway, Data Management has a REST API which I have covered in previous blogs, so with a bit of Groovy scripting it is now possible to execute a data load rule from a business rule.

In the next example I am going to run a Data Management load rule from a form, the start period of the rule will be based on substitution variables. This can all be achieved with REST calls and the concept is similar to something I covered in a previous blog, though this time there is no need for external scripting.

Once again to keep it simple, I have a basic form which has data populated up to May.

The idea will be run the rule to load data for June which is based on two substitution variables for current month and year.

The current month sub var value can be returned using the following REST request.

To return the current year sub var value just requires a change to the variable name in the URL.

The load rule can be executed with a POST request to the Data Management REST API URL, the body of the request contains the rule parameters in JSON.

Now we just need to replicate these REST calls in a Groovy script.

Let us start with the current month variable.

It is pretty much the same as the earlier examples, a GET request is made to return the sub var information. This time I am using the "JSONObject" class to extract the sub var value from the returned JSON.

If the script is run, the sub var value will be written to the job console.

On to the current year variable.

This will produce:

The start period for the Data Management rule can now be generated from the stored variable values.

The start period is now in the correct format.

Next the body of the REST request can be created in JSON. Once again, I am using the “JSONObject” class to handle this.

The JSON created matches the example shown earlier in the REST client.

A REST request using the post method which includes the JSON in the body can now be made.

The remaining part of the script checks if the return status code equals 200, if it doesn’t then an exception is thrown. This will generate an error message back to the user with the status code and text.

If the request was successful, the returned JSON information in the response is processed. The rule status code, rule status text and process ID are stored.

If the rule status code is not -1 (Running) or 0 (Completed) then an exception is raised, an error message will be displayed to the user with the process ID and the rule status.

If the script is run and the Data Management load rule is executed successfully the job console will contain.

If it is not a long running rule, then you could enhance the script so it checks until the rule completes.

In Data Management or Data Integration process details you will see the rule has been run.

Now all that is left to do is add the rule to the form. I added a menu option to the form which runs the rule.

Selecting the menu option then runs the Groovy script business rule.

Once the data load rule completes, the data will have been loaded and viewable in the form.

To provide an example of the data load rule failing, I updated the script to include an invalid URL.

I am going to leave it there for today, in the next post I will go through connecting to an external Web service which will return exchange rates, these will then be loaded directly to planning.