EPM Cloud - Integration Agent Part 2 - Get up and running

Moving swiftly on to the next part in this series on the EPM Cloud Integration Agent. In the last part I went through an introduction to the agent and in this post, I am going to dive straight in and go through the process of getting the agent up and running.

I will be going through an example on a Windows machine, but the concept will be similar on Linux/Unix type operating systems.

A summary of the tasks is:

• Install JRE 8+
• Install Groovy or Jython (optional)
• Download Agent
• Extract Agent
• Create application folders
• Generate encrypted password for EPM Cloud
• Populate agent initialisation file
• Copy EPM Cloud SSL certificate to agent folder
• Start agent

The agent requires a Java Runtime Environment (JRE) 8+ to run. JRE 1.8 update 221 was downloaded and installed.

If you want to use custom scripting and override default functionality in the form of event scripts, then either Groovy or Jython can be installed. If you want to stick with the default functionality and no scripting, then there is no need to install. It is also possible to implement your own Java classes without the need for Groovy/Jython.

I am not going to say which to install as it is personal preference or maybe company policy that defines this. If you work with on-premise FDMEE then you are probably going to be more used to Jython, for this reason I am going to install Jython but will no doubt cover both in future blog posts.

Currently the latest version of Jython is 2.7.1 and can be downloaded here.

To install Jython, the jar file can simply be executed, and the installation steps followed.

Next, the agent needs to be downloaded from Data Integration.

Go to Data Exchange.

Select Actions and there will be an option to download the agent.

Once downloaded, the zip file simply needs to be extracted to a suitable location.

The bin directory is where you need to start to configure and run the agent. There are scripts available for both Windows or *nix type platforms.

“createAppFolder” – will create the application folder structure, it can be generated within the same or separate location to the agent. It is possible to create multiple application folders if you want to run more than one agent and keep them segregated.

For simplicity, I am going to create the application folders in the same location as the agent in a directory named “appdata”.

Running the script will create the following structure.

The config directory contains an initialisation file for the start parameters of the agent, this will need populating before starting the agent, I will get on to that shortly.

The data directory will hold the data files that are created after extracting the source data. The files will be uploaded to EPM Cloud as part of the integration process. You will need to consider housekeeping to maintain this directory over time.

The logs directory will contain a log for the agent and each process, the process log also gets uploaded to EPM Cloud and appended to the main process log.

I will be covering the data and logs in the next part when I look at extracting data.

The scripts directory can contain any of the four available event scripts. As I mentioned earlier, event scripts are optional and once again I will be covering them in a later post.

Back to the agent initialisation file, which from now on I will refer to as ini file.

The file will be automatically populated with the EPM_AGENT_HOME and EPM_APP_DATA_HOME property values.

The EPM_APP_DATA_HOME directory is the one created earlier with the “createAppFolder” script.

AGENT_NAME is a unique name for the agent and can only contain alphanumeric characters. It is optional to define it in the file as it can set at runtime.

CLUSTER defines the name that the agent belongs to. Multiple agents can belong to a cluster and there can be one or many clusters. Like the agent name it can only contain alphanumeric characters.

Clusters provide a way of organising integrations. It may be that you want to use a cluster for only certain integration types, for example one cluster for extracting data from an Oracle database and another for connecting to other cloud providers. You can assign rules, locations or target applications to clusters. I plan on covering this in more detail in a future post.

Clusters are created in Data Management and can either be defined to operate in synchronous or asynchronous modes. I gave an overview of these modes in the previous post.

A cluster must exist before starting the agent. By default, the ini file will be populated with a cluster named “EPMCLUSTER”, this cluster also pre-exists in Data Integration.

Just for demo purposes I am going to have two clusters, one for each type of mode.

To create, view, edit and delete clusters, first go to Data Exchange.

Then select Agent.

There will already be “EPMCLUSTER” created, which has been assigned to asynchronous mode. I deleted this cluster, but it is your choice if you want to keep and use it.

Now a cluster can be added.

Two clusters were created, one for each mode.

I am going to keep it simple and configure the agent in asynchronous mode first, then update the ini file and restart the agent in synchronous mode.

I could have easily had two ini files, one for the agent in asynchronous and the other for synchronous.

Moving on to the next property in the ini file.

PORT is the port the agent will run on and the default is 9090.

CLOUD URL, DOMAIN and credentials should be easy to understand.

You will, however, need to encrypt the users’ password with “encryptpassword” script in the agent bin directory.

The encrypted password must be manually copied to the ini file. It is worth noting that every time the cloud password expires or is changed, the password will need to be encrypted again and the ini file updated.

It is a shame that the encryption method is not the same as EPM Automate. This means if you are running EPM Automate on the same machine as the agent, you will need to manage multiple encrypted passwords. In a perfect world, the encrypted password could be held in one place and be shared between the agent and EPM Automate.

On to the remaining properties.

LOG_LEVEL can be either ALL, INFO or ERROR.

POLL_INTERVAL is for asynchronous mode only, it is the time interval in seconds that the agent checks whether a process has been initiated in the cloud.

REGISTER_WITH_IP defines whether to register the agent against the host name or IP address.

EXECUTION_POOL_SIZE is the number of executions that the agent can run in parallel.

JYTHON_HOME and GROOVY_HOME is optional, they specify the installation location. Earlier I installed Jython to C:\jython2.7.1\ so this is used for the JYTHON_HOME parameter value.

CUSTOM_MEM_ARGS sets the minimum and maximum JVM memory sizes. The default values are just a guide and can be tuned after monitoring memory usage from running integrations.

CUSTOM_JAVA_OPTIONS allows you to add additional Java parameters, for example if the agent needs to go through a proxy to reach EPM Cloud.

CUSTOM_CLASS_PATH provides the option to add additional jar files to Java classpath, for example additional JDBC drivers.

CUSTOM_INTERFACE_CLASS_NAME is only required if custom Java classes are implemented to override default functionality through events.

Here is an example of my completed ini file.

There is one remaining task required before being able to start the agent. The EPM Cloud SSL certificate needs to be exported and copied to the agent cert folder.

I am going to use an example using Chrome, but it is a similar process for other browsers.

Log into one of your EPM Cloud instances and click the padlock icon.

Select Site settings > Certificate

This will open the certificate

Click the Details tab and select ‘Copy to File’.

Save the file to the agent cert folder.

Please be aware that the SSL certificates will expire so make a note of the valid date, you will need to replace it close to the expiration date or the agent will stop working.

Now we are ready to run the agent in asynchronous mode.

Update 19.12 release - The agent can now be run as a windows service. I have put together a post about this and you can read all about it here.

If you want to start the agent from command line, then you can use the “epmagent” script in the agent bin directory.

The syntax is:

epmagent <path to ini>

if the agent name is not defined in the ini file or you want to override it, you can set the agent name at runtime with the following syntax:

epmagent <agent name> <path to ini>

When the agent starts up, the initialisation file and SSL certificate will be read. The agent will be registered in the cloud against the cluster.

If you go back into Data Integration and select the cluster you will see the agent has been registered.

The Physical URL will be the machine and port the agent is running on.

In asynchronous mode the Web URL is not important, this only comes into play with synchronous mode, which I will get on to shortly.

As the agent is running in asynchronous mode, it will poll the cloud to check whether any processes have been initiated. I had set the interval poll to be 30 seconds.

Each time a poll is made the “Last Ping” will be updated in the Cloud. At least this gives some indication that agent status is healthy.

The agent name can be defined at start-up which will override the ini file.

There will now be two agents registered against the cluster even though only one is running.

Agents will stay registered against a cluster, if they are no longer required, they can be simply deleted from the cluster in Data Integration.

You might be wondering whether there is an option available to run the agent as a Windows service. I have raised this and apparently it is being looked into, so it might appear in a future release.

Let us move on to synchronous mode. I am just going to update the ini file to change the cluster name. I could have run both the agents at the same time, but I will be covering multiple agents, clusters and assignments in more detail in a future post.

I restarted the agent so it will pick up the new cluster name.

You can see that the agent mode is now synchronous.

In Data Integration the agent will be registered.

In the last post I provided an overview of the two modes so please refer to that for more details, but as the agent is running in synchronous mode the cloud requires an inbound connection over HTTPS to the agent. This would usually be achieved by running a load balancer or https with an internet facing URL which will proxy to the agent and port.

In my testing, I have a domain name routing to an AWS application load balancer. The Load Balancer has an SSL certificate applied which is registered against the domain name. The load balancer has been configured to only accept HTTPS traffic from Oracle Cloud, this is then proxied to the machine running the agent. The machine running the agent only accepts traffic on the agent port from the Load Balancer.

As you can see, when the agent is first registered with the cloud the Web URL is the same as the Physical URL. The Web URL can be updated by typing the internet facing URL for the agent.

It would be good if there was an option to also set the Web URL from the agent side when it starts up. It is possible to update using REST which will be covered in a separate blog post.

At this point there is no option to check whether EPM Cloud can connect to the agent. It is not until the point when you run an integration that you will find out, though to be honest depending on the problem there is not a great amount of detail in terms of logging. Personally, I think there should be an option to test the agent and check the connectivity. Maybe one for a future release?

It is possible to put the internet facing URL for the agent into a browser and check whether you get a valid response, this doesn’t guarantee that it will work from the cloud though.

You really need to decide whether you need synchronous over asynchronous. Are you happy to wait for polling the interval which can be set or do you have to run processes instantly? Synchronous mode will certainly require working closely with a network team.

Anyway, now that we have the agent running it is time to move on to extracting data. Watch out for the next part arriving very soon.

EPM Cloud - Integration Agent Part 1 - Introduction

One of the problems with data integration in EPM Cloud is that the list of data sources has been quite limited. Unless you want to integrate with an Oracle source like Financials, HCM or NetSuite then you are restricted to generating a file in the correct format and uploading. If there is the option of on-premise FDMEE then the hybrid functionality is a workaround for these limitations. Unfortunately, this is not viable for the majority of EPM Cloud customers.

These limiting factors have almost all been eliminated with the release of the Integration Agent which has arrived in EPM Cloud 19.10.

Here is an excerpt from the 'What’s New' documentation providing an insight to the details of the agent.

“The EPM Integration Agent, which provides connectivity between the EPM Cloud and on-premises sources including SQL data sources like Oracle and Microsoft SQL Server, E-Business Suite GL, and PeopleSoft GL, is now available:

In addition to easy access and setup for these sources, the agent may also be extended using scripting to connect to virtually any data source including 3rd party REST API based systems, or systems that can be accessed using a Jython or Groovy script. The agent, implemented as a data source for Data Management and Data Integration, responds and provides the specified information when a user executes data load rules.  Agent based integrations may be scheduled like any other data rule which provides users the flexibility to define integrations to any source in an automated fashion.”

One thing I disagree with in the first paragraph of the above statement is the reference to on-premise. The SQL data source can be in the cloud or on-premise, it doesn’t matter as long as the agent has good connectivity to it. The same goes for the agent, it can be run in the cloud or on-premise with the basic requirement that it has HTTPS connectivity to EPM Cloud.

The beauty of the agent is that with either Jython or Groovy custom scripting it goes beyond the default functionality of connecting and extracting data from SQL sources. It has opened up a whole new area of possibilities and I know there is going to be a lot of interest in it.

I personally think the release of the agent is one of the biggest enhancements in a long time for EPM Cloud integration.

In this first part I am going to give a quick overview of the agent before moving on to getting up and running with it. There is no point in overloading with too much information at this stage as it will all start to make more sense from the next post. There is a lot to cover so I am unsure how many parts there will be.

The agent is a lightweight Java application which is similar in concept to EPM Automate. As it is Java-based, it can run on most operating systems. Currently it stands at just over 6MB in size. The agent acts as a HTTP server and servlet engine with the use of Jetty, no surprises that the bulk of the functionality is built around a REST API.

In my testing I have been running the agent on a low tier AWS machine which has network connectivity to EPM Cloud and various data sources.

The agent requires a JRE to run which should be version 8+ and is not included in the download. This means either a supported JRE will need to be installed before running the agent, or if you are concerned about Java licensing restrictions and EPM Automate has been installed on the same machine, it is possible to point to the version of Java that is deployed with EPM Automate. I can imagine in a lot of cases the agent will be run on the same machine as EPM Automate.

There are configuration parameters for the agent that are defined in a file, I will be covering this in the next post.

The agent can currently be downloaded from Data Integration but I believe the longer-term plan is to move it into the downloads section but we will see.

By default, the agent will connect to relational data sources using JDBC and execute SQL queries to extract the required data set. A data extract file will be generated which is uploaded to EPM Cloud. The SQL query is defined in Data Integration. The database connection can either be set up in Data Management or in a file stored in the agent config directory.

There is also the option to extract data from EBS and Peoplesoft which are basically pre-seeded SQL queries. Though to be honest it might be better to fine tune the SQL to fit your requirements and use that instead.

There is no requirement to use the custom scripting option if you want to stick with default functionality to extract data. To be able to use custom scripting then either Jython or Groovy will need to be installed on the machine hosting the agent.

If you have used custom scripting in on-premise FDMEE then it is very similar to event scripts. The scripts allow you to override the default functionality and connect to any data source. I will cover this in a future post.

There are four events where custom scripting can be applied:

• Before extract – Before the agent connects to the data source and executes the SQL query
• After extract- After the agent has connected to the data source and produced an extract data file
• Before upload – Before the data file is uploaded to EPM Cloud
• After upload – After the data file has been uploaded to EPM Cloud.

Processes are executed in the same way from either Data Management, Data Integration, EPM Automate or REST.

It is possible to run multiple agents and cluster agents, once again I will cover this in a later post.

The agent can run in two different modes, the first being:

Asynchronous mode 

This is probably going to be the most common type of deployment as the agent only requires outbound connectivity over HTTPS to EPM Cloud, similar to EPM Automate requirements.

In this mode the agent will poll EPM Cloud using REST to check whether a load rule has been executed. The poll interval is defined in the agent configuration file.

If no rule has been executed the REST response will not return a Job ID, nothing will then happen until the next time the agent polls the cloud.

If a rule has been executed a payload is returned containing the Job ID. The agent makes another REST call with the job ID and the response contains the connectivity information, the SQL data source and the SQL query to execute.

The agent will then connect to the data source, execute the query and generate a data file. This file is uploaded to EPM Cloud using REST.

A REST call is made to inform that the process was successful.

The agent process log file is also uploaded to the cloud and appended to the main process log.

Standard functionality then takes over and the data file will be imported and validated.

Synchronous mode 

This requires EPM Cloud to connect over http(s) to the agent. This would usually be fronted with an internet facing load balancer or HTTP server which directs traffic to the EPM Agent on the designated port. The machine running the EPM Agent would not need to be internet facing and would be configured to only accept traffic from the load balancer/HTTP server.

An example being:

It does not have to follow the above design, there could be just a load balancer or a HTTP server or both. It is even possible to connect directly to the agent but that is not something I would promote for security reasons.

In my testing I ran an AWS application load balancer over HTTPS, this proxied requests to the EPM Agent machine and port. The machine hosting the agent was configured to only accept traffic from the load balancer.

What I will say is currently there is not much in the way of functionality or logging when testing the agent in synchronous mode. If you are considering this option, then you will need to work closely with a network team to configure, and put monitoring in place in case of configuration issues.

In Synchronous mode when a data load rule is executed, a REST request is made from EPM Cloud to the internet facing URL for the agent. The payload of the REST call contains the job ID. The agent then operates in the same way as Asynchronous mode. A request will be made based on the Job ID for the SQL connectivity and query information. The query is executed, file produced, uploaded to the cloud. The process status is updated, and process log file uploaded.

I think the decision of which mode to use really depends on whether you want to go down the route of configuring an internet facing solution and being able to run a process instantly or can wait for the poll interval. Remember the poll interval can be defined at agent level.

I will go into much more detail of how the asynchronous and synchronous modes work in a future post.

Communication between the agent and EPM Cloud is not only secured using HTTPS and TLS but also the REST payloads are encrypted using a public/private key management system.

Each time the agent is started it will generate a private and public key pair.

The private key is stored in the agent’s memory. The public key is uploaded to EPM Cloud and stored.

The REST payloads from EPM Cloud are encrypted using the public key. This can then be decrypted with the private key of the agent.

This means the agent can only be paired with one EPM Cloud instance and any attempt to communicate with the agent without the key and encryption will not work.

With that brief intro covered it is time to move on to the good stuff and get the agent up and running. Part two is available here.

Data Management/FDMEE – Extracting data and member aliases

I was recently asked whether Data Management could display the alias for account members where the source is Oracle Financials. The reason behind the request was the users found it easier to understand and map data based on the source Financials account alias.

I am going to go through a possible solution and lead on to extracting data from a source application and producing an export file with alias members.

This post is going to be based on EPM Cloud but most of it can be replicated with on-premise FDMEE.

At first, I thought maybe the alias information was being pulled through from the source. If all columns are displayed in the workbench there is quite a bit of additional information available which is held in attribute columns.

This does not include any member aliases though.

In the source filter of the load rule there is an option to define the members to extract data for.

If the filter condition is selected this will open the member selector. Selecting List View will display the member and description.

So the type of member information I am after is being stored. Populating the member information is controlled by selecting “Refresh Members” from the target application.

It is a shame that the source values can not be selected in the Data Load Mappings even though they are being stored, it is only possible to select target values.

Anyway, the member information is being stored in a database table named “AIF_TARGET_APPL_MEMBERS”. There is no access to this table in EPM Cloud, but it is possible to track down the details in on-premise.

There is currently no scripting available in Data Management, but there is an option to use SQL mappings, which means it should be possible to query the member name and return the alias.

To achieve this, you could add a lookup dimension to the target application. This dimension will hold the account alias members. As it is a lookup dimension it will not be loaded to the target.

The lookup dimension does not need to be mapped in the import format.

A SQL mapping can be added to the lookup dimension. The SQL will return the alias member based on the source account member.

I am sure the SQL could be improved, this is a simple example of how to go about it, and I am not saying it is the only or right way.

Once the mapping has been created the validation can be run again.

Now the data in the workbench contains the Financials account and alias. To keep the source information up to date then it is important to run refresh members.

This leads me on to a question that has been raised on numerous occasions; which is whether it is possible to extract data to a file, but populate with member alias instead of member name?

Based on the logic that I have just gone through then it should be possible. Currently in the cloud or on-premise there is no option to extract data by member alias, yes you can argue that mappings could be added to map the members in the extracted data to alias. This would require a process to keep them in sync and there is nothing wrong with going down that route. I just want to provide an alternative.

So, let us go through an example of extracting data from a source EPM Cloud Planning application and generate an output file including member alias.

I am going to base it on the “Data Export to File” which I went through in a previous blog post, if you are not aware of all the functionality it offers then have a read of the post. It should also be possible to use a custom target application.

I have created my target application which includes a dimension for account alias.

As with my previous example, I have added a SQL mapping to map the member name to alias.

In the data load rule, I have defined a download file name. You don’t have to do this if you are happy with downloading the .dat file version.

Once the rule has been run, the data in the workbench includes the account alias.

The output file can be downloaded from Process Details, the filename will be in the format of
<TARGET_APPLICATION>_ <PROCESS_ID>.dat

As a filename was specified in the data load rule options, it can also be downloaded from the inbox/output explorer.

Alternatively, it can be downloaded using EPM Automate or REST.

The target file includes a column for account member name and alias.

If I only wanted to include the member alias the target application would only need to contain the required dimensions.

A similar SQL mapping can be added for each dimension.

The workbench includes both the source member and the alias as the target.

 

The output file will only contain the member aliases. I also defined pivot by period in the data load rule options.

Nice. How about automating refreshing the member information so it is in sync with the source application.

This is possible using REST. A post request can be made containing the application name in the body.

Unfortunately, this doesn’t work for Oracle Financials as ‘refresh members’ is processed in a different way, it should work for most EPM type applications.

This can be scripted and automated using a language of your choice.

After executing the script, you will be able to validate the refresh members process has been run in Process Details.

If you have Enterprise planning, you can use Groovy and the REST API to run the refresh members process. I wrote a post detailing how to use the REST API with Groovy which you can read all about here.

In summary a Groovy business rule can be created which makes a REST call.

Once deployed the rule will be available in planning.

After running, Job Details contains some of the response from the REST call.

The process ID can be matched to confirm the refresh members process has been run.

To keep the member information in sync then the rule can be scheduled to run on a regular basis.

This means that when the data extract is run it will be populated with up to date member aliases.

I am going to leave it there, hopefully you have found this useful. Until next time…