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:
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.
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.
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.
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:
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.
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
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
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.
To return a list of export profile names the DRM Web Service operation “getExportNames” is called.
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.
Hi John, thanks for a great post. Just to let you know, your post has a typo that caused me a bit of grief until I spotted it. You have the API Adapter URL as:
ReplyDeletehttp://:/oracle/drm/apiadaptor
It should be
http://:/oracle/drm/apiadapter