FDMEE and DRM integration - Part 2

Swiftly moving on to part 2 in this series on FDMEE and DRM integration, in the first part I went through the steps to get up and running with the DRM Web Service which is the core component to the integration piece.

In this part I will go through the configuration steps within DRM and FDMEE to get ready to start the integration work.

These are the areas which I will cover:

• Importing DRM FDMEE template
  
• Configuring DRM external connections
  
• Setting up the DRM options in the FDMEE target application
  
• Explaining how the DRM import/export profiles are pushed to FDMEE

The first step is to import the FDMEE application template into DRM using the migration client, the template provides all the metadata to be able to support the integration.

In my integration example I am going to be using a classic planning application as the target so I have already imported the planning template into DRM, depending on what source systems or target applications you will be integrating with it is advisable to import the relevant template in before the FDMEE one.

The FDMEE template is available in the app-templates directory of the DRM product installation.

There is also an html file available which details all the metadata objects in the template though you can also use the view file option in the migration client.

Start up the DRM migration client and select Load.

Select the FDMEE application template and the template information screen will be displayed, if you have already view the html file then it is the same information.

Provide login connection information to the DRM engine and verify the connection is working.

It is possible you pick and choose which metadata objects you want to import.

Depending on what properties are available in DRM some missing dependency errors will be displayed, in my case these are mainly around EPMA because I am thankfully not going to be using EPMA so I have not imported the template.

Missing dependencies can be ignored or can be resolved in DRM before proceeding.

If you are using EPMA and have imported the template then at the moment you will need to edit the FDMEE template xml to update the spelling mistake from "EMPMA" to "EPMA".

I am sure this will be fixed in later patches.

Once loaded the output log can be reviewed for any serious errors or warnings.

On to the next configuration step in the DRM web client which are the external connections.

By looking at the amount of spelling mistakes I am not sure how well this template was checked over :)

There are two external database connections one to control the export of mappings from DRM to FDMEE and the other to import metadata from FDMEE to DRM.

Let us start with “FDMEE Export DB” where the connection information to the FDMEE relational database will need to be entered to match the environment.

By default a schema/owner will be set for the TDATAMP_STG table

Remove this table from the selected area and add the table back in from the list of available tables to set the schema/owner correctly.

Next repeat and enter the same information in for the “FDMEE Import DB” connection.

Once again the selected tables will have a default schema/owner so remove them and add them back in.

Make sure you enable “Include Views”.

These FDMEE tables/views will hold the hierarchy and member information extracted from the ERP system which is then pushed into the DRM database.

• AIF_HS_DRM_LOADS - Version import section
  
• AIF_HS_DRM_LOAD_HIERARCHIES - Hierarchy import section
  
• AIF_HS_DRM_MEMBER_V––Node import section
  
• AIF_HS_DRM_HIERARCHY_V––Relationship import section

Right, on to setting up the DRM options in the FDMEE target application.

If you go to the Target Application area in FDMEE you will see a button “Enable DRM”

Select the target application and click “Enable DRM” and this will add a DRM options tab to the application details section.

Enter the DRM API adaptor URL which I covered in the last part, the API is called from the Web Service and the format for the URL is:

http://<server>:<port>/oracle/drm/apiadapter

I also covered the Web Service in the last blog and format for the URL is:

http://<server>:<port>/oracle-epm-drm-webservices/DrmService

I usually go through the web server but if not the server and port of the WebLogic managed server that the DRM Web Service application was targeted against should be entered.

Next enter the username and password of the DRM user that is going to execute the Web Service operations, the user needs to exist in Shared Services, WebLogic and have the correct DRM roles, it makes sense for it to be an external directory user as the user does not need to be created and password managed in multiple places.

The “Import to DRM Profile (Metadata)” is the DRM import profile which will perform the task of extracting the ERP metadata information from the FDMEE tables/views and transform into the correct DRM database tables.

After importing the FDMEE template into DRM you will see that the following import profiles have been created.

As my target application is a planning application I will be using the “FDMEE Planning Import”

I will be covering the import in more detail in the next part.

The “Export to DRM Profile (Mapping)” is the DRM export profile that extracts member mapping information from a DRM version and loads this into the FDMEE staging mapping table.

The following exports are available after importing the FDMEE template.

As I am integrating with Planning I will be using the “FDMEE Planning Map Export”

To be able to select the import and export the profiles they first have to be extracted from DRM and loaded into FDMEE.

Not surprising this is achieved by the “Refresh DRM Profiles” button

Once the button has been clicked a pop up window will be displayed informing that the profile has been submitted.

This does not mean the profiles are available to be selected yet, if you check the profile details there will be a “Refresh DRM Profile” process and the status will be updated when the process is complete.

It is worth pointing out that the log should be checked because a successful status is not always the true picture, I saw errors in the log indicating there was in an issue with the execution of the DRM Web Service operation yet the status was displayed as successful.

Once the process has definitely been completed successfully you should see all the available import profiles when you select “Import to DRM Profile (Metadata)” in the DRM options

In my example there are additional profiles to the FDMEE ones as they are part of the DRM planning template.

If you select “Export to DRM Profile (Mapping)” in the DRM options the list should be now populated with available profiles.

The DRM options are now complete.

If additional profiles are required then it is also possible to set them at location level which will override the ones in the DRM options.

So the profiles have been extracted from DRM and populated in FDMEE but how was that accomplished, well this is where the DRM Web Services come into play and I am going to go into a little more detail on what happens behind the scenes.

The actual process of how the DRM Web Services are called doesn't really make much sense to me but I am sure Oracle have a reason to the way they have gone about it.

The FDMEE web application executes a Jython script which is located within the FDMEE installation structure.

A number of parameters are passed into the Jython script including what type of action to carry out such as “refresh profiles”.

The Jython script then calls a batch script passing in the same type of parameters.

The batch script then calls a java class which carries out the integration and calls the relevant DRM Web Service operation.

Finally if the action being run is a mapping export the Jython script will then update FDMEE mapping tables.

So the process is Java > Jython > Command Line > Java

This process seems so inefficient to me and open to failures, I don’t quite understand why the Java class is not being called from the FDMEE web app instead of going through this convoluted process but like I said there must be a reason behind it.

If you are running FDMEE on *nix then currently there is no shell script included so the process will not work at all, you would need to translate the contents of the windows batch script into a shell script and update the Jython script to call to the shell script, this will no doubt be addressed in a future patch.

Anyway, in terms of refreshing DRM profiles the flow is:

• Delete current profile information from FDMEE database tables.
  
• Execute DRM Web Service operation to return export profiles.
  
• Execute DRM Web Service operation to return import profiles.
  
• Execute DRM Web Service operation to return Domain names.

After each operation the returned values are inserted into FDMEE database tables.

To return a list of export profile names the DRM Web Service operation “getExportNames” is called.

As I mentioned in the last post the DRM Web Service uses the SOAP protocol which basically requires sending an XML formatted request to the web application.

Here is an example of the request that it is generated from FDMEE and sent to the DRM Web Services application.

The SOAP request has authentication information inserted into the header which conforms to the OWSM security policy “oracle/wss_username_token_client_policy”

“This policy includes credentials in the WS-Security UsernameToken SOAP header for all outbound SOAP request messages. Both plain text and digest mechanisms are supported. This policy can be attached to any SOAP-based client.”

This is required because is the DRM Web Service application has been configured to use the “oracle/wss_username_token_service_policy” which I covered in more detail in the last post.

The header also includes parameters to define the DRM API adaptor information.

The SOAP XML formatted response contains the profile names which are then processed and loaded to the FDMEE database.

To return import profiles the SOAP request is exactly the same except the operation is called “getImportNames”

To return domain names the operation name in the request is set to “getDomains”

Domains can be assigned in FDMEE metadata rules which I will cover in the next part of the series.

After completion of each Web Service operation a record is inserted into the FDMEE AIF_LOOKUP_TYPES table.

So for example after the “getExportNames” operation a new lookup id is generated and a type which follows the format of DRM_<TargetAppName>_EP, EP meaning export profile.

The returned values are then inserted into the table AIF_LOOKUP_VALUES

So in FDMEE each time you go to select a profile or domain the values that are displayed in the selection windows are generated by querying the above tables.

The values entered into the DRM options tab for a target application are stored in the table AIF_TARGET_APPL_PROPERTIES

I did notice that the DRM password stored in the table is not encrypted, hopefully this will be addressed in a future patch.

I am going to leave it here for this part in the series and in the next part I will go through the process of exporting Chart of Accounts from an ERP system into FDMEE and then importing it into DRM.

FDMEE and DRM integration - Part 1

The 11.1.2.4.200 PSU release for FDMEE added new functionality to allow integration with DRM, the integration provides the following capabilities:

• Import chart of accounts values and hierarchies from source ERP systems to Data Relationship Management
  
  
• Map ERP source values to EPM target dimension members within Data Relationship Management
  
  
• Export ERP source to EPM target member mappings from Data Relationship Management to FDMEE

Here is my simplistic view on the flow between FDMEE and DRM

It is important to note that FDMEE currently only supports loading metadata from the following source systems

• Oracle E-Business Suite
  
  
• Oracle Fusion Financials
  
  
• PeopleSoft Enterprise Financial Management

This means if you want to use the export metadata from FDMEE to DRM then you will need to be using one of the above source systems.

Even though importing data load mapping from DRM to FDMEE is aimed at mapping from ERP systems to EPM applications it is still possible to use the functionality if the source is not an ERP system.

To be able to integrate with DRM then not only do you need to be on FDMEE 11.1.2.4.200+ you also need to be running at least DRM 11.1.2.4.340.

Assuming you are running the required versions then there are a number of other prerequisites that need to be in place before you can jump into the FDMEE and DRM integration.

In this first post I am going to go through getting DRM up and running with Web Services as this is the core mechanism in the integration between the two products.

DRM has two types of Web Services available, the Web Service that has been around for a long time which uses the SOAP protocol and requires Oracle Web Services Manager (OWSM) and the latter addition which is the DRG Rest Service for use with the EPM mobile application.

FDMEE integrates with DRM using the SOAP protocol which adds a lot of configuration overhead compared to the REST service, hopefully one day Oracle will standardise and convert DRM to only use REST which seems to be more favourable these days and in my opinion a lot easier to work with.

The documentation provides a good architecture diagram to show how DRM all fits together with the Web Services

I will be focusing on the FMW App Server area in the diagram to get the Web Services up and running.

The assumption I am going to take is that DRM 11.1.2.4.340+ has been installed and configured to use Shared Services for authentication.

To be able to use the Web Services functionality the following areas need to be completed:

• Metadata Services Schema (MDS) creation for OWSM.
  
  
• OWSM configuration.
  
  
• WebLogic configured to use an external provider.
  
  
• Deploy the DRM Web Services application.
  
  
• Attach OWSM security policy.
  
  
• DRM API configuration.

So let us start with the MDS schema creation for OWSM, to create the schema the Repository Creation Utility (RCU) is required and as EPM 11.1.2.4 uses FMW 11.1.1.7 then that is the version of the utility that should be downloaded.

You should be able to download this from Oracle Software Delivery Cloud or the Oracle Technology Network.

The utility can run from any machine that has access to the database where the MDS schema will be created.

Once the utility is started the steps are to Create, enter the database connection details and then on the select components step choose “Metadata Services”

The remaining steps are relatively straight forward though if you need detailed information refer to the RCU documentation

Now that the schema is available OWSM can be configured.

OWSM is installed but not configured by default in 11.1.2.4

To configure OWSM start up the Fusion Middleware Configuration Wizard on the foundation server by running:

<MIDDLEWARE_HOME>\wlserver_10.3\common\bin\config.cmd/sh

Select Extend an existing WebLogic domain

Select the domain directory

Select “Oracle WSM Policy Manager”

At the “Configure JDBC Component Schema” section enter the database connection information to the MDS schema.

Accept the default settings through the remain sections and all being well the domain should be extended to include OWSM

Now that OWSM has been configured the next step is make the deployment available and this is done through the WebLogic admin console.

Log into the console and go to deployments and then locate “wsm-pm” and select Start > Servicing all requests.

You may need to restart one of the managed servers but all being well the state of the deployment should show as active.

To verify the policy manager status a validation web page can be accessed.

Right on to the next step which is to an external provider in WebLogic admin console to allow authentication in the Web Services.

The configuration has to match that of an external directory configuration in Shared Services.

In my example I am using a MSAD connection which has already been configured in Shared Services.

In the WebLogic admin console create a new authentication provider and select the type of provider.

I usually set the control flag as sufficient for both the newly added provider and the default authenticator to stop authentication issues with multiple providers.

“SUFFICIENT—the user is not required to pass the authentication test of the Authentication provider. If authentication succeeds, no subsequent Authentication providers are executed. If authentication fails, authentication continues down the list of providers.”

Then it is just a case of populating the provider connection information, the problem with WebLogic is you never know you have got it right until a restart.

Once the admin server has been restarted you should be able to view users from the external directory.

If you don’t see the directory users then analyse the errors in the admin server logs.

Now time to deploy the DRM web services application.

The web application should be located in the api directory of the DRM installation.

In the WebLogic admin console install a new deployment and go to the above path and select the web services ear file.

Install the deployment as an application and then select the target cluster to deploy to.

As I am going to be using the Web Services primarily with FDMEE I chose that as the target.

The remaining configuration screens were kept as default.

Activate the changes and restart the target managed server if it is running.

Start servicing all requests and the state should then change to active meaning the DRM Web Services application should be running.

Now that the application has been configured an OWSM security policy can be attached to protect the Web Service

The documentation provides which policy to apply for integration with FDMEE

“This policy uses the credentials in the WS-Security UsernameToken SOAP header to authenticate users. Both plain text and digest mechanisms are supported.”

It is possible to attach a policy through Enterprise Manager or the WebLogic Scripting Tool (WLST)

Policies can be directly attached to Web Services or globally attached by creating a Policy Set, I am opting for the Policy Set method.

Enter the EPM WebLogic domain name and DRM Web Services application name.

Attach the policy that FDMEE requires when integrating with DRM

Once saved the policy should be applied the next time the managed server is restarted.

Next the API adaptor requires configuring the DRM configuration console.

Enable the API enable and then verify using the test URL

One final configuration I usually add is to the OHS configuration to allow the DRM Web Services to be accessed through the web server.

On to testing to confirm all these configuration steps have been successful.

First to verify the Web Service can be accessed through the web server and that the policy has been attached which can be done by accessing the WSDL

The WSDL is returned which confirms the DRM Web Services are running and can be accessed through the web server, the policy has also been correctly attached.

Finally to test one of the Web Services operations using Enterprise Manager, it is a bit clunky but does its job.

Enable OWSM security policies and use a valid external directory user that has been provisioned in DRM.

To be able to execute the operation successfully a header is required to be inserted into the XML view which defines the parameters for the DRM API adaptor.

If all is well no errors will be generated and a successful response should be returned.

I am going to leave the post here as the Web Services have now been confirmed to be running as expected.

In the next part I will go through the steps to start integrating FDMEE with DRM and look in more detail at what is happening behind the scenes with the Web Services.