EPM Integration Agent: Configuration tips

EPM Integration Agent was introduced sometime last year providing ability to connect to in-premise data sources. Though the tool has ability to connect to third party REST APIs via Groovy or Jython scripting but I assume the majority of the use cases would be to connect to relational databases, usually warehouses which had been used to feed data to various reporting and planning applications in the in-premise world. Unless clients move to any cloud based ERP, the data warehouses  are going to stay for a while and the need to pull data too.

Oracle provides connectivity for Oracle and MSSQL server by default, but what about other data sources? Other than scripting, if any data source offers SQL capability, we can essentially pull data. I had a recent experience connecting to Netezza database via Integration Agent and thought of sharing some quick tips I learnt during this exercise.

• Integration Agent Port

Agent requires a port to communicate and by default it is 9090. Many organisations, specially those dealing with financials services would have their ports blocked. Moreover, 9090 is commonly used by many applications as their default port and might be already in use. Use the Telnet command  find out if the port is already in use. If not available use a separate port

• Start agent manually as process before registering it as a service

Agent now offers an ability to register it as a windows service. When attempting to do it, the service gets installed even though the agent couldn’t register itself with cloud application. Ensure to check the agent logs. What I prefer is to register the agent manually first using the process rather than service to ensure the INI was populated correctly and agent was able to communicate to cloud application

• Check Proxy Settings

Many organisations have proxy servers setup for external communications. Agent program is essentially a java based utility which would not be able to communicate via proxy unless proxy details are provided in the INI file. With EPM Automate, we can provide the proxy details as part of login command but with Integration Agent, this needs to be provided in INI file. Many of us tend to not go through documentation details about each and every parameter and that’s what I did myself. the INI file provides CUSTOM_JAVA_OPTIONS parameter to provide proxy details to be used when agent attempts to communicate to cloud. The proxy details can be provided in format -Dhttp.proxyHost=proxy.example.com -Dhttp.proxyPort=8080

• Installation paths for Groovy and Jython

When installing Jython or Groovy on the machine having agent, ensure the installation path does not have spaces in it. Usually the default path for Groovy is the Program files (x86) folder with spaces in between, agent does not seem to like it and fails to register

• JDBC drivers for source technology

Some of the source technologies would require JDBC drivers to be explicitly installed on the agent machine. This is different from the jar file copied over in the agent’s lib folder. A technology like Netezza requires JDBC driver to be installed on the machine to connect to server. Once JDBC drivers are installed, copy the jar file and place it in the lib folder.

Cheers !!!

Groovy Series 7: Submit Groovy generated rules to Essbase

Ever since Groovy functionality was introduced with Planning cloud, it has evolved over a period of time, offering new capabilities and methods to achieve what was not possible with traditional Calc script rules. Sometime, I have been asked “Is Groovy a replacement for Essbase Calc scripts” and my answer is a big ‘NO’. Groovy should not be perceived as a replacement to Calc script, rather consider it a booster which enhances user experience and provides flexibility to dynamically generate Calc scripts and improves performance.

Calc scripts are an integral part of how we run calculations on Essbase, whether we generate the code dynamically using Groovy or stick to traditional scripting. Lets look at different ways to submit these Calcs to Essbase and how Groovy has evolved.

• Return method

This had been the oldest and default method until May 2019 when Job Factory class was introduced. Justifying its name, this method is used to submit a dynamically generated Calc script to Essbase for execution. A simple example would be to declare a Stringbuilder(), capture Position dimension members from edited cells, build a script and submit with some logic and submit it to Essbase.

The limitation with this method is, once called, you cannot perform any further action in the script. If I have a scenario to perform certain calculation on edited cells, aggregate data and then perform another action, I would have to break it down into multiple or at least two rules, one where I submit the groovy generated script and two perform aggregation.

• Job Factory

This class was introduced in May 2019 update which allowed submitted an existing rule, ruleset or a template as a job. This allows making calls to existing calculations written in traditional Calc Script to be executed as part of a groovy rule. All we need to do is create a job of the calculation and submit it. It also allows passing values to runtime variable. This means we can have a groovy rule which performs certain task before groovy submits a dynamically generated script to Essbase.

Considering the same example, I first execute an existing rule as a job, passing the cost centre as the RTP value and then generate the script using edited positions and submit the script to Essbase. In this example, I haven’t put in the code to determine the status of the submitted job but in ideal case you would move ahead once the status is complete.

• executeCalcScript method

This one is the latest addition allowing a much flexible way to submit calculation to Essbase and continue performing further tasks without breaking it into multiple rules. This method belongs to the Cube class allowing us to execute the dynamically generated code on that specific cube.

A simple use case would be

1. Capture the edited cost centre
2. Perform certain calculation on the edited members in Cube1
3. Push data from Cube1 to Cube 2 using smart push
4. Perform another calculation on Cube2 on same edited cost centre

All of this is performed in a single calculation script.

All these methods offer flexibility to design a dynamic and efficient solution with full control on members that should be enacted upon and achieve desired performance.

Cheers !!!

Groovy Series 6: Beauty of cscParams

While working with Groovy scripts, a common task is to dynamically generate an Essbase calc script capturing the edited members from the grid. This script should then be submitted to Essbase for execution. But the important task is to ensure these edited members when passed on to the Calc script are written in required Essbase format, enclosed in quotes.

In past we have been using various groovy methodologies to identify unique members and converting them to Essbase format concatenating with quotes, commas and spaces. How about if all of this can be done in one step my enclosing the entire collection in a method which spits out strings, enclosed in quotes and separated with commas ? Yes that’s what cscParams do for us.

Let’s look at the above script example, in Step 1, I declare an empty list and push the edited accounts from the COA dimension into the list. Step 2 and 3 define two ways of converting this list into a string which I can use either dynamically generate a Calc script or pass as an override member list while executing a smartpush. Step 4 prints the strings generated from both.

Because the grid I edited had both base periods and YearTotal on the grid, when members are edited at month level, the YearTotal also is marked as edited and edited members get repeated.

In the first print statement, I have to explicitly filter the unique members and on converting them to a string does not enclose them in quotes and have square brackets around members. This is the literal conversion of list into string.

The second print statement uses the cscParams function and just passing the list of edited members, it filters the unique members, enclose them in quotes and separate them by commas. This is exactly what is required when passed on to dynamically generated Calc script or list of override members in smartpush.

The third print statement prints the str string without filtering the unique members and using the old way of concatenating the list members.

Its quite obvious how cscParams saves the effort of filtering or concatenating the list in just single step. This can be captured in another string object as shown in above example to avoid rewriting the method multiple times.

Cheers !!!

Groovy Series 5 : Interacting with external web services

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.

1. Sending a request to fetch FX rates for particular date https://api.exchangeratesapi.io/2019-06-30

   

2. Restricting the rates to specific currency codes and desired base currency https://api.exchangeratesapi.io/latest?base=AUD&symbols=USD,EUR

   

3. 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)
}

Cheers !!!