EPM Agent Installation and Configuration
The EPM Agent is a recent addition to Oracle’s EPM Cloud products. It’s a utility that can be installed within a customer’s LAN to provide access to on-premises data directly from Data Management load rules.
To try it out, I installed it on a Windows 10 workstation and attempted to connect to a SQL Server database.
EPM Agent installation is not a quick or simple process. The installation guide contained 9 steps, each of which contained several additional steps. I began by downloading the agent ZIP file from the Data Exchange section of PBCS. At this point you must also decide whether to create an agent cluster, or just work with the default cluster called EPMAGENT that is provided. Clusters offer a way of segregating different data sources in large estate by installing multiple agents connecting to different sources.
Downloading the Agent from Data Exchange
Unlike Qubix Cloudbridge, to allow the EPM Agent to connect to EPM Cloud you must download the EPM server’s certificate file. Oracle don’t provide any way of doing this from the UI, but you can do it by clicking on the padlock icon in your browser address bar.
Downloading the EPM Cloud certificate from a Chrome browser
After unzipping the agent, I had to run createAppFolder.bat to create a set of folders that would contain data, logs, configuration etc. I don’t know why the ZIP file didn’t come with these folders to save this step, but it does allow you to choose the location for these folders.
The next steps were to fill in the connection details of my PBCS server in the agentparams.ini file. You’ll need to know the URL, domain name, username and password. A tool called encryptpassword is provided to give you an encrypted password to put in this file. I can see this creating a frustrating administrative overhead in the future. Whenever the password used here expires, the data integrations will fail, and only somebody with access to this configuration file and server will be able to enter the new password and restart the agent. In Qubix Cloudbridge, users can update all credential information via the UI, and no restarts are required.
At this point the agent can be started - either from the command line or by installing it as a Windows service. You can check the agent startup in the logs folder, and see it running in PBCS by choosing Agent from the Actions menu of the Data Integration page.
Agent startup recorded in the log
Agent listed in PBCS
Loading Data from an On-Premises Database
With that agent running, I could create a connection to the data in my on-premises SQL Server database. The first step was to create an SQL query to select the data I wanted. This is done by choosing Query from the Action menu on the Data Integration screen and clicking the + button to add a new query. There’s no SQL editor here - just a text box into which you paste a query already written elsewhere.
Creating a SQL query
At this point I was surprised to discover that having used the Data Integration section in the modern UI to create the agent and the source query for my data, I now had to switch to the old Workspace UI and do the rest of the configuration in Data Management. Instead of a seamless step by step process, you have to jump between different modules and user interfaces to create the integration.
In the Data Management Setup screen, I registered a new Target Application. Strangely, even though this is a data source, you must add it under Target Applications, rather than Source Systems. At this point I had to create a CSV file containing a header row so that Data Management knew what columns I would be selecting from the database.
Uploading a list of column names to Data Management
Given that there is an agent to connect to the on-premises data, and that I had defined all the column names earlier in the SQL query, it was surprising that I still had to go through this manual process involving a CSV file to provide a list of column names.
After adding the target application, I chose the Application Filters tab for it to select the query to run and the connection details to the on-premises database. Oddly, although picklists were provided for some settings, there was no picklist to choose the named query that I had created earlier, so I typed in the name. In their documentation, Oracle state that the agent can select data from “on-premises relational databases”. In practice, the only JDBC drivers that you can select when creating the target application are Oracle and SQL Server. You will need to know how to construct the JDBC connection string for your database. It also states that other data sources can be used, but it appears that in practice this is only possible by writing scripts to first load the data into the relational database.
Filling in the connection details for the on-premises database
Once the target application has been created, to process to follow is the normal one to create a Data Management load rule. When the rule is run the EPM Agent will be invoked to extract the relational data and upload it to the EPM server.
It was disappointing that the only way to use the EPM Agent to perform a data load was via a Data Management load rule. It’s not possible to use the agent as a data source for dimension and data import jobs outside data management. Qubix Cloudbridge can load data from on-premises and cloud data sources via Data Management, dimension import jobs and data import jobs. It also provides a seamless UI for defining data sources and building multi-step process flows to load metadata and data.