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!
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.
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.
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!
No comments:
Post a Comment
Note: only a member of this blog may post a comment.