FDMEE Hybrid and the REST Part 1

The recent FDMEE 11.1.2.4.200 PSU added new hybrid functionality which in basic terms means it is now possible to integrate between on-premise and Oracle cloud applications.

I thought it would be useful to go through getting up and running with the hybrid functionality and then have a more detailed look at what is happening behind the scenes, this all ties in nicely with my previous posts on EPM REST web services as hybrid is no different and relies heavily on the REST API.

To get started here is what the documentation has to offer on the new addition to FDMEE:

Oracle Hyperion Financial Data Quality Management, Enterprise Edition can be used as a primary gateway to integrate on-premise and cloud based applications.

This features allows EPM customers to adapt cloud deployments into their existing EPM portfolio.

You might use the integration to import data from existing on-premise ERP applications, or synchronize data from on-premise EPM applications.

For example, a Oracle Hyperion Financial Management customers can add Oracle Hyperion Planning data or a Planning customer can add more Planning applications.

In addition, this integration includes writing back from a cloud to an on-premise application or other external reporting applications.

This type of integration supports on-premise sources such as:

•    On-premise ERP applications from supported source to Oracle Cloud
•    On-premise EPM applications to Oracle Cloud
•    Oracle Cloud to on-premise EPM applications
•    Oracle Cloud to EBS/Peoplesoft GL
•    Oracle Cloud to on-premise external sources using custom application

 
I am going to concentrate on the integration with PBCS which in the April 2016 release added hybrid support, the new features documentation included the following information:

"Starting with this release, you can use the on-premises FDMEE Release 11.1.2.4.200 to load data to and from the service without the need to set up Data Management content in Oracle Planning and Budgeting Cloud.

You can load data to the service from any on-premises source that FDMEE supports and then extract data from the service back to the onpremises sources directly, including drill-through and writeback."

So FDMEE now provides the ability to load data from any supported on-premise source and then exported into a PBCS application, alternatively data can be extracted from a PBCS application and loaded into an on-premise target.

When you look at the amount of different source systems available to the on-premise version of FDMEE compared to PBCS then you realize why hybrid could be a good option.

Besides all the above sources it is possible to configure an EPM application as the source and have a PBCS application as the target or the other way round.

Currently in PBCS data management you can only use a file or an Oracle Fusion cloud application as a source.

With on-premise FDMEE there is also the advantage of being able to use scripting while PBCS is heavily restricted on that front.

There is the argument of if you are going to invest in having FDMEE on-premise then why not have planning as well but I am not going to get into the politics around cloud deployments.

Anyway before you can even start to use the hybrid functionality you will need to be on FDMEE 11.1.2.4.200+

The 11.1.2.4.200 patch number is 22452414 (ignore this if you are on a newer version)

The patch readme provides the information on how to apply the patch using Opatch.

There are some additional configuration steps that are required which are not covered in the readme but are in the FDMEE documentation, the FDMEE documentation was not released when I first configured so I had to go through the pain of trying to get hybrid to work.

I will go through the steps shortly but I want to show the errors that occur in FDMEE if they are not implemented.

In FDMEE the PBCS application will need to be added as a target, there is now a cloud option when you add a target.

This will open a window to enter your cloud credentials so in my case PBCS credentials.

Now because I have not done any of the additional steps after applying the patch the following error is generated.

The reason behind this error is because EPM 11.1.2.4 deploys WebLogic 10.3.6 and by default implements Certicom SSL which does not support SHA256 algorithms that the PBCS SSL certificate has been issued with.

The workaround for this is to configure the FDMEE WebLogic managed server to use Java Secure Socket Extension (JSSE) SSL which is able to handle the certificate.

From WebLogic 12.1.1 JSSSE is the only SSL implementation that is supported and the Certicom based SSL implementation has been removed.

To configure the WebLogic admin server will need to be started the admin console accessed.

Select the FDMEE WebLogic server (still called ErpIntegrator(n)), you may have multiple servers to configure depending on your environment.

Go to the SSL tab

Under advanced enable “Use JSSE SSL” and apply.

We are not finished with the configuration but I will show you want happens if you only implement the above change and then try to add the PBCS target again in FDMEE.

The reason for this error is that the PBCS SSL certificate has been issued using a wildcard.

By default, the WebLogic configuration does not support wildcard certificates so to rectify this issue a couple of changes are required.

In the same place as the JSSE SSL was enabled there is a field called “Hostname Verification” and this requires updating to “Custom Hostname Verifier”

Add “weblogic.security.utils.SSLWLSWildcardHostnameVerifier“ to  “Custom Hostname Verifier”

Apply and activate these changes.

Now the documentation includes an additional step which I did not need to implement and did not encounter any issues, it may only be required if the on-premise environment has been configured to use full SSL but I can’t confirm that yet.

To be complete I will include it, the process involves importing the PBCS SSL certificate into the Java KeyStore (JKS) that WebLogic has been configured to use.

First the certificate needs to be exported which can be done from your web browser, log into your PBCS application and you should see a lock icon next to the URL.

It will be in a slightly different location depending on the browser type and version, click the icon and view the certificate

In the details tab then there will be an export button if the browser is Firefox, if it is IE there should be button with “Copy to File”

Save the certificate and transfer it over to the Web application servers.

As this is only for demo purposes I am going to import into the WebLogic demo keystore, depending on your environment WebLogic may be configured to use a different keystore especially if it is production.

The certificate can be imported using the Java keytool command line interface.

You should be prompted to trust the certificate.

 The list command can verify whether the certificate has been added to the keystore.

Once the changes have been implement restart the FDMEE web application server.

This time when the target application is added in FDMEE no error should be generated and you will be presented with the option to select an application.

I am going to leave it there for today and in the next part I will go into detail on how the hybrid functionality works and interacts with the REST API.

FDMEE Web Services Part 2

Back again to continue looking at what FDMEE has to offer in terms of Web Services, in the last part I looked at what is currently available in the on-premise world of FDMEE and covered the Web Services using the SOAP protocol and a possible alternative accessing the java web servlets.

Today I am going to look at what is available using the FDMEE REST web service which will be focusing on PBCS as unfortunately this option is not currently available for on-premise, maybe this will change in the next PSU or further down the line, we will have to wait and see.

Update December 2016
REST functionality is now available for on-premise FDMEE from PSU 11.1.2.4.210

I have covered the planning REST API with PBCS in previous posts so it may be worth having a read of them if you have not already because this is going to be in similar vein and I don’t want to have to go into the same detail when I have covered it before.

As you may well be aware if you have worked with PBCS that it is bundled with a cut down version of FDMEE which only known as data management.

If you have used the epm automate utility you will have seen that it has two options when integrating with data management, the documentation states:

Run Data Rule

Executes a Data Management data load rule based on the start period and end period, and import or export options that you specify.

Usage: epmautomate rundatarule RULE_NAME START_PERIOD END_PERIOD IMPORT_MODE EXPORT_MODE [FILE_NAME]

Run Batch

Executes a Data Management batch.

Usage: epmautomate runbatch BATCH_NAME

I am not going to cover using the epm automate utility as it is pretty straight forward and I want to concentrate on the REST API.

If you look at the PBCS REST API documentation there is presently nothing in there about executing  the run rule and batch functionality, it must be available as that is how the epm automate utility operates.

Well, it is available which is good or that would have been the end of this blog, maybe it has just been missed in the documentation.

Before I look at the data management REST API I want to quickly cover uploading a file so that it can be processeed, it would be no use if you did not have a file to process.

I did cover in detail uploading files to PBCS but there is a slight difference when it comes to data management as the file needs to be uploaded to a directory that it can be imported from.

To upload a file using the REST API there is a post resource available using the URL format:

https://server/interop/rest/{api_version}/applicationsnapshot/{applicationSnapshotName}/contents?q={“isLast”:”boolean”,”extDirPath”:”upload_dir”chunkSize”:”sizeinbytes”,”isFirst”:”boolean”}

{applicationSnapshotName} is the name of the file to be uploaded

The query parameters are in JSON format:

chunkSize = Size of chunk being sent in bytes

isFirst = If this is the first chunk being sent set to true

isLast = If this is the last chunk being sent set to true.

extDirPath = Directory to upload file to, if you are not uploading to data management then this parameter is not required which is why I didn't cover it in my previous post.

When you create a data load rule in data management and select the file name a pop up window will open and you can drill down the directory structure to find the required file.

 

It is slightly confusing that it starts with inbox and then has another directory called inbox, anyway I am going to load to the inbox highlighted which mean the extDirPath parameter will need to be set with a value of “inbox”.

Just like in my previous posts I am going to provide an example s with PowerShell scripts to demo how it can be simply achieved, if you are going to attempt this then you can pretty much use any scripting language you feel the most comfortable with.

In the above example the username and password are encoded and passed into the request header.

The file is uploaded from the local machine to the data management inbox using the “Invoke-RestMethod” cmdlet.

The response status codes are:

0 = success
-1 = in progress
Any other code is a failure.

The response was 0 which means the upload was a success and the file can be viewed and selected in the data load rule setup.

Now the file has been uploaded and there is a data load rule the data management the REST API can come into play to execute the rule.

The API can be reached from the following URL format:

https://server/aif/rest/{api_version}/jobs

The current api version is V1

I am going to use boomerang REST client for chrome to demonstrate.

Using a GET request against the REST resource returns the jobs that has been executed in data management.

The response includes the total number of jobs and a breakdown of each job including the status, id, who executed the job and output log.

If I run the same request against the 11.1.2.4.100 on-premise FDMEE the following is returned.

I deliberately executed the request with no authentication header just to show that the REST resource is available, adding the header returns the following response.

It looks like required Shared Services Java methods don’t yet exist in on-premise so that is as far you can get at the moment.

Back to PBCS and to run a rule the same URL format is required but this time it will be using the post request, the payload or body of the request has to include the job information in JSON format.

The parameters to supply are:

• jobType which will be DATARULE
  
• jobName is the name of the data load rule defined in data management.
  
• startPeriod is the first period which is to be loaded.
  
• endPeriod for a single period load will be the same as start period and for a multi-period load it will be the last period which data is to be loaded.
  
• importMode determines how the data is imported into data management, possible values are:
  
• APPEND to add to the existing POV data in Data Management
  
• REPLACE to delete the POV data and replace it with the data from the file
  
• NONE to skip data import into the data management staging table
  
• exportMode determines how the data is exported and loaded to the planning application, possible values are:
  
•       STORE_DATA merges the data in the data management staging table with the existing planning applications data
  
•       ADD_DATA adds the data in the data management staging table to the existing planning applications data
  
•       SUBTRACT_DATA subtracts the data in the data management staging table from the existing planning applications data.
  
•        REPLACE_DATA clears the POV data in the planning application and then load the data in the data management staging table. The data is cleared for Scenario, Version, Year, Period, and Entity
  
•       NONE does not perform the data export and load into the planning application.
  
• fileName (optional) if not supplied the file which is defined in the data load rule will be used.

Below is an example of the request payload in JSON format for executing a data load rule.

Once the request is posted the response will contain information about the rule that has been executed.

The rule status is shown as running, to keep checking the status of the rule then the URL that is returned in the href parameter can be accessed using a get request.

This time the status is returned as success so the rule has completed, the id can be matched to the process id in data management.

Converting this into a script is a pretty simple task.

The URL containing the job id can stored from the response and then another request executed to check the status.

Once again this can be confirmed in the process details area in data management.

Moving on to running a batch which is the same as running a rule except with less parameters in the payload.

The required parameters are “jobType” and “jobName” in JSON format.

The “jobType “value will be “BATCH” and the “jobname” will be the name of the batch, nice and simple.

The response is exactly the same as when running a rule.

In scripting terms it is the same logic as with running a rule, this time in my example I have updated the script so that it keeps checking  the status of the batch until a value other than running (-1) is returned.

Well that covers what is currently available with the data management REST API, hopefully this will make its way down to on-premise FDMEE in the near future, it may appear with the imminent release of hybrid functionality but we will just have to wait and see.

FDMEE Web Services Part 1

After covering Essbase and Planning Web Services in detail I thought I would move on to have a look at what FDMEE has to currently offer for both on-premise and PBCS.

I am going to start off looking at Web Services with the full offering of FDMEE which is on-premise, there has been a SOAP based Web Service available since the early days of ERPi which I doubt has been widely used.

To be able to use the Web Service there is a requirement to configure Oracle Web Services Manager (OWSM) first and unfortunately there is no option like the Essbase Web Service to bypass it.

It is also worth pointing out that the 11.1.2.4 documentation states the following:

“It is recommended that you use batch scripts rather than the web services”

Interesting that the recommendation is to use batch scripts which have to be run from the FDMEE server, not really an ideal situation if you are running processes that can take advantage of Web Services, anyway I will get on to the batch scripts later as there is another alternative to having to run the scripts.

I still think it is worth covering the Web Services because they are available and can be used, though personally I am hoping that the SOAP Web Services will be replaced with a REST version like the one available with PBCS which I will get on to later.

I am not going to go into detail on configuring OWSM as it is covered in the documentation and there are various guides out there which can be followed.

A summary of the steps are:

• Configure an external directory in WebLogic Admin console to match the external directory configuration in Shared Services.
  
  
• Run Oracle Repository Creation Utility to create the Metadata Services schema.
• Extend the EPM system WebLogic domain to configure OWSM.
• Enable OWSM in WebLogic Admin console.
• If required set up message protection to encrypt and sign the SOAP messages.
• Configure OWSM security policy in Enterprise Manager Fusion Middleware Control, if you are extremely bored and want understand policies in great detail have a look here and here.

It is possible to configure the Web Services with a set of default policies using batch scripts which are explained in detail here.

Right, OWSM is configured wasn’t that fun ;)

Depending on the FDMEE version and how OWSM was configured you may end up with the following security policy attached to the FDMEE web application.

oracle/wss11_saml_or_username_token_with_message_protection_service_policy

If you are interested the policy definition is:

“This policy enforces message-level protection (that is, message integrity and message confidentiality) and authentication for inbound SOAP requests in accordance with the WS-Security 1.1 standard.”

I am not going to use message protection as I think it is an overkill for demonstration purposes so I removed the policy and attached:

oracle/wss_username_token_service_policy

The policy definition is:

“This policy uses the credentials in the UsernameToken WS-Security SOAP header to authenticate users. The plain text mechanism is supported.”

Finally we can access the Web Service WSDL through the URL:

http(s)://:/oracle-epm-erpi-webservices/RuleService?wsdl

The current FDMEE documentation informs us that  the following operations are available.

• executeDataRule
• executeMetaDataRule
• getDataRuleNames
• getLocationNames
• getPeriodNames
• lockPOV
• unlockPOV

It is worth pointing out that the documentation has not been updated in a long time and the definition of some of the operations are not totally correct.

There are a number of different ways to test or access the Web Service, you can use the standard functionality within Enterprise Manager.

There are also quite a few browser plugin or applications that are available for browsers, like in my previous posts I am going use the  Chrome application Boomerang is a simple way to access SOAP and REST Web Services.

As you can see from above there are differences between the operations that are returned and what the documentation states are available, though it is worth pointing out some of the operations are part of the Account Reconciliation Manager product

I am going to cover the getDataRulesNames and executeDataRule operations.

An empty SOAP request for the getDataRulesNames looks like:

The documentation says a location name is required as an input but in reality it is the partition key of a location.

The partition key can be easily located in FDMEE.

If you try and execute the SOAP request with no header an error will be generated as no OWSM policy information was included.

To replicate the header for the OWSM policy I am using is pretty simple once you understand the format.

Executing the request will now return all the data rules and data rule properties for the location with the partition key 21.

There are 44 properties returned for each rule so you can pretty much find anything you want to know about them.

Executing the operation using a scripting language does not take too much effort and in the following example I using a simple PowerShell script but it can be achieved in pretty much any language.

The script reads in a SOAP XML template and then sets the operation, username, password and partition key before posting the request.

The required properties from the response can be stored and processed or just displayed like above.

The status property shows that the rule failed the last time it was run so let’s move on to running a rule through the Web Service.

It doesn't take a mastermind to know that the operation to use will be “executeDataRule”

The inputs for the SOAP request are:

• Rule Name
• Import from Source 
• Export to Target
• Export Mode
• Execution Mode – (equivalent of Import Mode in FDMEE) 
• Load Exchange Rate Flag
• Start Period Name
• End Period Name
• Sync Mode - (equivalent of offline/online mode in FDMEE)

These are self-explanatory and similar to what is available when you execute a rule from in FDMEE.

The SOAP request once again requires the OWSM policy header and the above inputs.

The response contains the Process ID and ODI session ID for the rule execution.

If I run the PowerShell script from earlier it confirms the rule has been run successfully.

This can also be verified in Process Details in FDMEE.

I am going to leave it there for the on-premise FDMEE Web Services and move on to the documentation recommends of running batch scripts.

Now as this post is about Web Services so I am not going to be running the batch scripts and look at an alternative solution.

There are various batch scripts available that run rules for loading metadata/data, write back, import mappings and batches

These rules can be found in
<EPM_ORACLE_INSTANCE>\FinancialDataQuality

Basically the scripts call another batch script in
<EPM_ORACLE_HOME>\EPMSystem11R1\products\FinancialDataQuality\bin

From there the passed in parameters are posted to a Java servlet in the FDMEE web application, so even though the process originates with a batch script in the end a request is made to the FDMEE web application, this means in theory we should be able to bypass the scripts and go direct to the web application.

The URL for the servlet is :

http(s)://<webserver>:<port>/aif/BatchExecutionServlet

Let us take the batch script for running a data load rule as an example.

The format for the load data rule (loaddat.bat/sh) batch script is as follows:

loaddata USER PASSWORD RULE_NAME IMPORT_FROM_SOURCE EXPORT_TO_TARGET EXPORT_MODE IMPORT_MODE LOAD_FX_RATE START_PERIOD_NAME END_PERIOD_NAME SYNC_MODE

So to run a rule it could be like:

loaddata admin password DelimitedFileDL Y N Store Replace N Mar-16 Mar-16 False

As the parameters are being posted to a servlet this can be replicated using the same Web Services client application as before.

First add a content-type of application/x-www-form-urlencoded to the header.

Next it is just a matter of adding each of the parameters

The password can either be clear text or encrypted, there is a script available to generate an encrypted password.

Alternatively the parameters could be entered directly into the payload.

Once the request has been posted a response should be return to confirm whether the rule was successfully executed.

The response is pretty much the same as the output from the batch scripts.

In terms of scripting this can be easily reproduced as the example below shows:

The parameters are stored and posted to the servlet and then a formatted response is output.

This concept can be applied to all of the batch script functionality, here is an example to run an FDMEE batch.

Well I think I am going to leave it there for today and in the next part I will look at the options available using the REST API which is currently only available with FDMEE (data management) in PBCS, maybe it will made available as an on-premise option in the next PSU, we will see.