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.
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.
Agent
Query
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.
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.
In Cloud, we need to create and configure a new source application and create a data load rule.
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).
The import format is also straightforward - I just had to add Period and Year as row dimensions to load multiple periods at once.
Finally, I have a simple data form to show the loaded data as follows.
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.
Job confirmation from the agent with the same process id.
Here is the data file generated by the agent that is getting uploaded and loaded in Planning.
Oracle EPM Agent log.
Finally, data is loaded and can be viewed in the data form.
I hope you find this blog helpful. Thank you for reading and following me.