Back again to continue the series on FDMEE and DRM integration, just to recap this what has been covered in the previous parts.
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
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:
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
- 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.
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
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."
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.
great as always!
ReplyDeleteVery informative post John. Many thanks
ReplyDelete