EPM Agent - The new way of Integrating

It was my birthday yesterday (14/07) so thought of sparing some time out of my busy work schedule for what I love...oh yeah thats sharing knowledge....so here I am with my first post, disseminating my experience with EPM Integration Agent.

Integrating custom sources (apart from the once for which cloud got standard adapters – Fusion / HCM / NetSuite) with EPM Cloud has been a pain – got to work with them by creating files. Gosh hell lot of manual tasks!!!

Anyways when I was wondering (last year probably) why Oracle has not launch the features in Data Management something like UDA (Universal Data Adapter) which is with FDMEE where we can integrate Oracle Database / SQL Server tables like a charm, Oracle launched EPM Agent aka Integration Agent. Intresting hmmmm...so lets have a quick look on it:

The TYPES – Synchronous & Asynchronous. I will not go into much theoretical details as Oracle has got some nice documentation and one can dig into John Goodwin’s blog. Usually it is set up in Asynchronous mode which only requires outbound connectivity from your network to the cloud so I used for my demo too.

The SETUP - First and Foremost, Install the Agent from your cloud environment -

Navigate to Data Integration and download the Agent:

This will download the zip file which you can extract anywhere on your machine, I preferred the C drive.

We will require the EPM Cloud certificate to be downloaded in the cert folder of the Agent which will be utilized for kicking it off. You can download the certificate from your browser, I generally use chrome so I downloaded it like:

P.S. The certificate has a expiry date so you need to place the renewed certificate again post expiry.

Next step would be to create the application folder to have its own logs, data and config repository like we have it in Data Management.

We have to make some changes in the initialization file, just route to config folder under you application folder:

The Agent can be started as Windows Service too but I didn’t gave much effort as I needed it for Demo as of now and went ahead with the love – Command Prompt…

Cool so now it started the polling, lets have a look in the Data Integration:

Tada…I got my Agent configured, started and assigned. Lets create the SQL query as well here:

So its time to set up your data management.

Lets first have a look on my source Oracle table, how it looks, it got some Operational Expenses like Travel and Facility expenses which I am going to load in the Financials cube. For the demo I used Oracle as source DB, we can have any source (SQL Server too, we just need the drivers needed to be downloaded and configured to make it work as source, I will cover them in my next blog)

I have prepared the csv file with the field names which I need for my Integration like:

Few points to note:

1) A custom application needs to be created in Data Management and it will use the csv file name as application name so choose your file name wisely 😊

2) You just need the field names in the file which will be created as Dimensions in the custom target application (ya ya ya I know its source but that’s how it works)

So lets proceed with Data Management then:

Setup the data source:

You can give a Prefix to your custom application, though its not mandatory, I gave ORCL_

Leave the Target Dimension Class as it is ‘Generic’. Just switch to the next tab ‘Application Filters’ and edit the values:

Remember I used some variables in my SQL query as filters so it’s the time to create and define the bind variables.

Once all these are done, rest are the usual Data Management steps like creating Import format, Location and Data Load Rule, Data Load Mappings which I will not cover. Lets jump over the DLR execution straight forward:

Notice we got the bind variables as source filters here as we set them up at Rule level. All settings done so what to wait for…..just hit it!!!

There you go…the rule executed successfully and data got loaded as well to the EPM cube.

Lets see what happened at the Agent window, as its Asynchronous mode which Polls at certain interval (mentioned in the initialization file), it got the job request:

The LOGS – what they says:

Lets VALIDATE the data in application:

Pre DataLoad:

Post DataLoad:

Dang...the expenses got loaded into application for Jan’20.

That’s it for the day, will keep posting as I explore more….Lets learn together 😊

Hey hey hey waittttt......do share your experience and yeah hope you find my post interesting..