FDMEE and DRM integration - Part 3

Back again to continue the series on FDMEE and DRM integration, just to recap this what has been covered in the previous parts.

• Part 1 – Going through the process to get up and running with DRM Web Services.
  
  
• Part 2 – Detailing the configuration process in DRM and FDMEE to be able to start the integration.

If you have not already read the previous post then I recommend in doing so as I not going to detail again what has already been discussed.

In this post I will be looking at the process to extract metadata information from an ERP system and load into FDMEE and DRM.

Going back to my simplistic diagram, the flow shown with green arrows will be the area I will be focusing on in this post.

Currently FDMEE (11.1.2.4.200) only supports loading metadata from the following source systems

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

In my example I will be using Oracle EBS 12 Vision instance as the source ERP system.

Before I start I would like to stress that I am not a DRM consultant so I may go through things in a different way than you are used to, this post is not aimed at providing details on how to perform master data management in DRM.

The metadata is not going to be realistic of a live financial system and is only there for a guide on how the FDMEE to DRM integration process operates.

In FDMEE I have already added the EBS source system and initialised.

An import format has been created with the EBS system as the source, the accounting entity selected and the Vision planning application as the target.

Mapping have been added to the import format to map the source to the target dimensions.

A location called “EBS_UKVISION_LOC” has been created with the import format set as the one shown above.

Now that everything is in place a new metadata rule can be created to extract CoA from the EBS system and load into FDMEE, the rule is set up in pretty much the same way as would be done when integrating with a source ERP system.

In the mapping details I have selected Entity as the dimension for my example.

As DRM has been enabled in the target application options there is now an option to select a DRM domain.

From the docs:
“Domains are optional for the integration, they are needed when the dimensions from the source systems contain nodes of different types with the same name. An example would be both an account 1234 and a product 1234. Domains can make these nodes independent of each other with the use of the domain qualifier.”

I went through the process on how the domain information is extracted from DRM and pushed into the FDMEE database tables in the last part.

If “Refresh DRM Profiles” has not been previously run there would be no domain available to be selected.

For demonstration purposes I had already created a domain in DRM.

To define which part of the hierarchy I want to extract from the EBS system I added a new hierarchy region.

I selected member “2999” as the starting parent to be extracted.

This will extract member “2999” and any descendants in the hierarchy.

I selected “Ignore” as the option for processing orphan members as this should only extract member “2999” and its descendants, the available methods are:

• Ignore—No orphan members from the source are extracted.
• Create as Root Member—Root members are created, and orphan members are not. All members are created at the top level of the hierarchy.
• Create as Children of—Orphan members are placed as children of the member specified in the entry field to the right "Create as Children of field."

Before running the rule there is an additional piece of configuration that is required in DRM relating to the import profile, I didn’t cover it previously as I thought it would be more relevant leaving it to now.

Basically the DRM import profile will extract the metadata information from the FDMEE database tables and load them into the DRM applications database tables.

In the last part I selected “FDMEE Planning Import” as the profile in the target applications DRM options.

If you open the import profile in DRM there four FDMEE database table/views that need to be assigned.

These are the same tables that were selected when external import database connection was configured in DRM.

The tables and views have been specifically created in FDMEE to match the corresponding DRM database information making it easier to perform the extraction from FDMEE to DRM.

If you are interested the “Columns” tab provides more information on what information is being selected from the FDMEE tables/views.

In the Target tab there is also the option to use a blender after the import which I will cover in the next part

Once the import profile has been saved the metadata rule can be run in FDMEE.

The Process Details provide a summary of the metadata load.

The overall process is pretty much similar to running a standard FDMEE metadata rule which I am not going to cover as it involves more SQL than you would ever want to see.

If we take a look in DRM then a new version has been created in the format of FDMEE_<FDMEE_LOCATION_NAME>

It is worth pointing out that if the metadata rule is run again for the same location the version will be deleted first from DRM and then created again.

The properties of the version include information extracted from FDMEE such as the Source System name, Accounting Entity, Location Name, Target EPM application and so on.

The load ID is the FDMEE process id.

A new hierarchy has been created in the version using the description of the member “2999” which was defined as the starting parent in the metadata rule.

The properties of the hierarchy also include FDMEE related information.

The hierarchy contains the members as defined by the FDMEE metadata rule, these have been extracted from EBS, loaded to FDMEE and then loaded to DRM.

If you have a look at the FDMEE tables that were set in the DRM import profile you can view the metadata that has been extracted from FDMEE and imported into DRM.

AIF_HS_DRM_LOADS - Version import section

AIF_HS_DRM_LOAD_HIERARCHIES - Hierarchy import section

AIF_HS_DRM_HIERARCHY_V – Relationship import section

AIF_HS_DRM_MEMBER_V – Node import section

The above information is imported into DRM by executing the confgured import profile, the metarule process calls a DRM Web Service operation using the same method  that I explained in detail in the last part.

The FDMEE web application passes parameters into a jython script which passes them to a batch script and then these are passed into a Java class which calls the Web Service operation.

The DRM Web Service operation that is called is “startImportByName” and the main parameters passed in from FDMEE are Load ID, Location name and import profile name.

So going back to my example the input parameters in the Web Service request would be:

importName = “FDMEE Planning Import”
versionName and versionDescription = “FDMEE_” + “EBS_UKVISION_LOC”
runtimeParameters = LOADID/1005

I can demonstrate what is happening with a Web Services client using a SOAP request.

As explained previously the header has the OWSM policy information to authenticate the user and includes the DRM API parameters.

The body of the request  contains the operation “startImportByName” and the required input parameters.

The response provides DRM job information including a summary of the process and the current status.

The status in the response is showing the job as “Queued”, FDMEE needs to know when the job is complete or if it fails so to achieve it calls another Web Service operation.

The operation is “getJobStatus”

The job id that was returned from the previous Web Service call is used as input for this operation.

Once again this can be simulated though this time I will only show the body because the rest of the SOAP request is the same as the previous example.

This time the response has the status of “Done” which FDMEE is looking for so can update the process details and end.

There are another couple of operations that are called before running the import profile, these are to check if a version with the same location name exists and if it one does then delete the version.

First the “getVersionNames” operation is called.

The operation does not require any input parameters.

As the response includes a version includes the location that is going to created using the current metadata rule it needs to be deleted and this is done using the “startDeleteVersion” operation.

The only input parameter required is the name of the version to delete.

The response includes job information for the deletion.

The status can be queried like before using the “getJobStatus” operation.

That concludes the post for today and in the next part I will look at using a blender to merge the version imported from FDMEE into an existing version and then export mappings back into FDMEE.

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.