Integrating Oracle EPM Planning Cloud with PostgreSQL

Posted by Taysols on Apr 14, 2021 8:45:00 AM
Find me on:

Migrating a PostgreSQL server to Oracle EPM Planning CloudIntegrating Oracle EPM Planning Cloud with PostgreSQL

By Omar Shubeilat, MSc, OCP, Principal Consultant at Taysols

Original Article: https://www.linkedin.com/pulse/integrating-epm-planning-cloud-postgresql-omar-shubeilat-msc-ocp/


This post is about connecting and loading data in EPM Cloud from a PostgreSQL on-premise database, PostgreSQL is an open-source database, and it is my database of choice for on-premise personal development projects. As we know, EPM Agent can connect to any relational database as long as there is a JDBC driver. This should be relatively easy to demonstrate.

I'm not going to detail every single step of the configuration as I think there is enough documentation available. I will highlight the important steps that are different from setting up an Oracle or SQL database.

Let me start with the table I'll be going to load the data from, pbcs_data. As you may notice, there is nothing complicated about it.

pbcs_data PostgreSQL

The next step is to create an agent cluster, create a query, configure the agent on the server, and set up the agentparams.ini config file.


EPM Agent Cluster PostgreSQL


EPM Update Query PostgreSQL

To be able to complete the agent configuration, I must first download the PostgreSQL JDBC driver and copy it to the EPM Agent library folder.

PostgreSQL EPM Agent Jar

This is the agentparams.ini config file, I will not explain every field, but the most important thing is to add the JDBC driver to CUSTOM_CLASS_PATH property.


Finally, we can start the agent and move on to the Cloud Data Management setup.

Agent to Oracle EPM Cloud

In Cloud, we need to create and configure a new source application and create a data load rule.

Oracle EPM Cloud PostgreSQL uplift

The only thing worth mentioning here is the PostgreSQL JDBC URL. Other than that, it is all straightforward (I didn't change the JDBC Driver here and kept it as it is).

Oracle EPM Cloud Postgre SQL Application Details

The import format is also straightforward - I just had to add Period and Year as row dimensions to load multiple periods at once.

Oracle EPM PostgreSQL import format

Finally, I have a simple data form to show the loaded data as follows.

Oracle EPM data verification


The final step is to run the integration (the start period is Jan-FY20 and the end period is Dec-FY20). Notice the process id is 121.

Oracle EPM PostgreSQL integration

Job confirmation from the agent with the same process id.

Oracle EPM PostgreSQL job confirmation

Here is the data file generated by the agent that is getting uploaded and loaded in Planning.

PostgreSQL agent datafile

Oracle EPM Agent log.

Oracle EPM Agent Log

Finally, data is loaded and can be viewed in the data form.

Oracle EPM data review

I hope you find this blog helpful. Thank you for reading and following me.


Topics: EPM, Oracle