Monday, 7 March 2016

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.

No comments: