Though this feature came in last year and I have been a bit late in writing about, I thought it would be still worth making a record of the same to help me (and others if people are reading) to make use of the same. The update to EPBCS (though it no longer is PBCS or EPBCS, let’s just stick to it to symbolise it as availability of Groovy) , Oracle had made its Groovy features more powerful by adding an ability to interact with external web services. What this means is, we can now do HTTP GET or POST with external webservices. If you like to know more about the methods, you can check W3School. In Groovy API, we have Get(), Post(), Delete() and Put(), which do majority of the tasks.
A very basic example would be the ability to update FX rates into our application in an automated way. Usually this is one of the forecast preparation tasks for finance users to ensure FX rates are updated and they might be crunching these rates manually to put in an average or current value. I will be using the https://exchangeratesapi.io/ for fetching the FX rates, but any other service which returns FX rates or data via web service can be used.
A call to external web service can be made directly in the business rule or by adding it as a named connection within EPM cloud connections. A named connection can be added from ‘Connections’ –> ‘Other Web Service Provider’. In this case, I have added exchange rate API URL and named it FXRates. Since this service does not require any user name and password, I left these fields blank.
Lets look at the usage of Exchange rates API and how requests can be made to receive JSON responses. I prefer to use Postman app to manually check REST requests but any REST client can be used.
Sending a request to fetch FX rates for particular date https://api.exchangeratesapi.io/2019-06-30
Restricting the rates to specific currency codes and desired base currency https://api.exchangeratesapi.io/latest?base=AUD&symbols=USD,EUR
Defining the start and end date and combining it with base currency and desired output currencies https://api.exchangeratesapi.io/history?start_at=2020-05-01&end_at=2020-05-31&base=AUD&symbols=EUR,USD
For my application, I need to pull both average and ending rates with base currency as AUD and conversion rates for USD and EUR. The FX rates for May is empty and should eb populated with average and ending rates
On executing the business rule, it asks for a date, this could be any date within a month for which rates should be populated, say we choose May 31, 2020.
On completion, the rates get populated for the month
Let’s dig into behind the scene mechanics to achieve this
- Section 1 shows the declaration of the RTPs used in the business rule, it is the date prompt which appears on execution of the business rule. RTP is defined in Calc Manager as date type
- Section 2 imports the required classes to work with JSON and calculation
- Section 3 breaks the year, month and day into separate variables
- Section 4 declares a Calendar object. This will be used to generate a date and determine the month and year for EPM month and Year
- Section 5 determines the financial year based on month. Since Aus has financial year Jul to Jun, if month is greater than 6, year should increase by one
- Section 6 generate values for month and day from the calendar object. Also we declare a list ‘Months’ with all 12 months. This will be used later to pull the month name. It might be possible to return a month name using Groovy but found this to be the easiest way
- Section 7 captures the first day and the last day of the month in the selected date
- Section 8 checks if the application has a connection with name FXRates, calls the REST service. The lastDayDate variable holds the date in YYYY-MM-DD format with the base currency and exchange rate currencies
- Section 9 captures the response as Map object. The data captured in response are the ending rates for the date with base currency as AUD and reporting currencies as AUD and USD. These are passed to job console
- Section 10 makes second call to the exchange rates API to get rates from day 1 to last day of the month. the base rate being AUD and exchange rate currencies are USD and EUR
- Section 11 captures the response as Map object. the JSON response returns the rates for all the days
- Section 12 adds up all the rates captured in the response. This will be used to determine the average by adding up all rates and dividing it by the number of day tracked by counter
- Section 13 passes the values captured from response to job console. The month and the financial year captured from calendar object determines the members of Period and Years dimension
- Section 14 uses the DataGridBuilder to build a write grid by passing the dimension members and data. The average rates and the closing rates are written to application
- Section 15 checks the number of accepted and rejected cells as a result of saving data to the cube
Below is the code to copy paste the logic as explained above.
/*RTPS:{Groovy_Cal_Date}*/
import org.json.JSONObject
import groovy.json.JsonSlurper
import java.math.BigDecimal
def yearPart = rtps.Groovy_Cal_Date.toString().substring(0,4)
def monthPart = rtps.Groovy_Cal_Date.toString().substring(4,6)
def dayPart = rtps.Groovy_Cal_Date.toString().substring(6,8)
//println yearPart
//println monthPart
//println dayPart
Calendar calObj = Calendar.instance
//println Math.round(calDate/10000,0).toString()
calObj.set(yearPart.toInteger(), monthPart.toInteger()-1, dayPart.toInteger() )
calObj.set(Calendar.DAY_OF_MONTH, calObj.getActualMaximum(Calendar.DAY_OF_MONTH))
//print calObj
//print "Month:"+ calObj.get(Calendar.MONTH)
int varYear
if(calObj.get(Calendar.MONTH)+1>6)
varYear = calObj.get(Calendar.YEAR) + 1
else
varYear = calObj.get(Calendar.YEAR)
int varMonth = calObj.get(Calendar.MONTH)+ 1 /*Month in Calendar object stat from 0*/
int varDay = calObj.get(Calendar.DAY_OF_MONTH)
def Months = ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec']
def endRateRespObject
def lastDayDate = calObj.get(Calendar.YEAR).toString() + '-' + varMonth.toString().padLeft(2,'0') + '-' + calObj.get(Calendar.DAY_OF_MONTH)
def firstDayDate = calObj.get(Calendar.YEAR).toString() + '-' + varMonth.toString().padLeft(2,'0') + '-01'
if(operation.application.hasConnection("FXRates")){
HttpResponse<String> response = operation.application.getConnection("FXRates").get("/$lastDayDate?base=AUD&symbols=USD,EUR").asString()
//print response.body
JsonSlurper slurper = new JsonSlurper()
endRateRespObject = slurper.parseText(response.body.toString()) as Map<String,Map>
//def ratesObject = slurper.parseText(respObject.rates.toString()) as Map
//println endRateRespObject.rates.size()
println "AUD to EUR Ending:" + endRateRespObject.rates.EUR
println "AUD to USD Ending:" + endRateRespObject.rates.USD
}
double totalEUR, totalUSD
int counter
if(operation.application.hasConnection("FXRates")){
HttpResponse<String> response2 = operation.application.getConnection("FXRates").get("/history?start_at=$firstDayDate&end_at=$lastDayDate&base=AUD&symbols=USD,EUR").asString()
// print response2.body
def avgRateRespObject = new JsonSlurper().parseText(response2.body) as Map<String,Map>
avgRateRespObject.rates.size()
if(avgRateRespObject.rates.size()>0){
def rateEntries = avgRateRespObject.rates.entrySet()
rateEntries.each { rateEntry->
def entryValue = rateEntry.value as Map<String,Double>
totalEUR= totalEUR + entryValue.EUR
totalUSD= totalUSD + entryValue.USD
counter=counter+1
}
}else{
counter=1
}
}
println "totalEUR:"+ totalEUR
println "totalUSD:"+ totalUSD
println "counter:" + counter
println "AUD to EUR Average:" + totalEUR/counter
println "AUD to USD Average:" + totalUSD/counter
println "Loading data to "+ Months[varMonth-1] + " and " + 'FY'+varYear.toString().substring(2,4)
//Write Grid to push FX rates
DataGridBuilder fxWriteGrid = operation.application.getCube("FINRPT").dataGridBuilder("DD/MM/YYYY")
fxWriteGrid.addPov('E_NA', 'Month', 'Units', 'COA_NA', 'Working', 'Act_DE', Months[varMonth-1], 'FY'+varYear.toString().substring(2,4) )
fxWriteGrid.addColumn('Local_AUD_Average','Local_AUD_Closing')
fxWriteGrid.addRow(['EUR'],[totalEUR/counter,endRateRespObject.rates.EUR])
fxWriteGrid.addRow(['USD'],[totalUSD/counter,endRateRespObject.rates.USD])
DataGridBuilder.Status writeGridStatus = new DataGridBuilder.Status()
fxWriteGrid.build(writeGridStatus).withCloseable { writeGrid ->
println("Total number of cells accepted:" + writeGridStatus.numAcceptedCells)
println("Total number of cells rejected:"+ writeGridStatus.numRejectedCells)
operation.application.getCube("FINRPT").saveGrid(writeGrid)
}
Great post!
ReplyDelete