Monday, 4 July 2016

FDMEE and DRM integration - Part 4

Continuing on with the FDMEE and DRM integration series, just to recap at the end of the last part metadata had been extracted from EBS, loaded to FDMEE and then pushed into DRM.

I am going to return again to loading metadata from FDMEE to DRM because I want to cover the use of the blender option in the DRM import profile as it will help with the process to push mappings into FDMEE.

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

If you open up an import profile in the target tab there is the option to set a blender profile.

Using a blender will basically allow the imported dimensions from FDMEE to be merge into an existing DRM version.

As part of the FDMEE template there is a blender profile named “FDMEE Blend” which has already been preconfigured.

By selecting this option once the import form FDMEE has completed the blender profile will be called.

The import profiles that are part of the FDMEE template also have added parameters.

The "LOADID" parameter is used when manually running the import profile, when running from FDMEE the load id will be passed in.

The parameter defines which load id to use when extracting information from the FDMEE tables, I am going to be running the import profile manually at first so I have set it to value which was part of a previous load to DRM just so I know it has metadata against it.

The “BLEND_TARGET” parameter will be passed to the blender if the import profile is run manually.

The parameter has value of “FDMEE_MAP_VERSION”, this relates to a DRM version variable which is used when mappings are extracted from DRM and loaded to FDMEE.

This variable needs to be assigned to the version you wish to extract mappings from, I set the variable to existing version which contains a planning application entity dimension.

If you take a look in the blender “FDMEE Blend” you can see that the target version is picked up from the “BLEND_TARGET” parameter so it will be using “FDMEE_MAP_VERSION” as the target

The Source version has been left blank as it will pick up that information from the FDMEE “FDMEE AIF_HS_DRM_LOADS” database table.

So let us run the import profile manually from within the DRM web client.

The import was successful and the blender was run, a new version has been created with the naming convention using the format of :

The version contains the hierarchy for member "2999" which I defined in the last part as the starting parent for the metadata rule.

As I mentioned I have already a planning entity dimension which I set the “FDMEE_MAP_VERSION” variable against, before the import profile was executed the looked like the following:

As you can see there is a base member “2999” which is also the top member of the hierarchy imported from FDMEE so let us take a look at the hierarchy after the blender was run.

Now the imported hierarchy has been blended into the existing hierarchy and includes the location name which will be used when loading the mappings back into FDMEE, I will get on to the mapping process shortly but the “SRCKey_Name” property will form the source mapping and the “HP Member” will be the target mapping.

You don’t have to use a blender for use with FDMEE mappings as it can be accomplished using different methods such as action scripts, queries or the compare functionality and manually creating the mapping relationship.

If you going to use the import profile associated with a blender from FDMEE then you will need to update the blender target version from using the “BLEND_TARGET” parameter as even though it will look to run successfully from the FDMEE process and logs it have failed, delving into the details in DRM you will see the following error generated:

DRM-12487: The parameter 'BLEND_TARGET' is not defined.

After FDMEE executes the import process it then calls the DRM Web Service operation “getJobStatus” and looks for a status of done which it interprets as successful.

I feel FDMEE should also call the “getImportResults” operation as this returns more detailed information and highlights that the process completed with a fatal error.

When running the process from DRM it picks up the parameters set in the import profile but unfortunately when this is called from FDMEE using the DRM Web Service operation “startImportByName” these parameters are ignored as it expecting them to be passed in at runtime.

To get around this the target version of the blender can be assigned to an existing version or you can use the “FDMEE_MAP_VERSION” variable to make it more dynamic.

Ok, so now we have the blended hierarchy we are ready to start looking at exporting the mapping from DRM and loading them into FDMEE.

Going back to part 2 in this series in FDMEE I set up the “Export from DRM Profile (Mapping)” property in the DRM options of the target application

This means that FDMEE is going to execute the “FDMEE Planning Map Export” export profile

The version in the profile should be set as the variable “FDMEE_MAP_VERSION” as this is the version that will be passed in from FDMEE.

There is a filter that will export only leaf members and the property “EPM Location Names” is set to the parameter value that is passed in from FDMEE when the export is run.

In the Target tab of the profile you can see which columns are being extracted from DRM and their corresponding column in the FDMEE "TDATAMAP_STG" mapping table.

As I mentioned earlier the "SRCKey_Name" in DRM will be source member mapping and “HP Member” will be the target member mapping.

If the profile provided as part of the FDMEE template does not suit your requirements for mapping functionality it can be updated or a new profile created.

To import the mappings, the “Import from DRM” option can be selected in the Data Load Mapping section in FDMEE.

Just like with other mapping imports there is the option to set the import mode to merge or replace and to validate the target values against the members in the target application.

The process details will provide information on whether the import from DRM was successful or not.

The explicit mappings should now have been loaded and visible in the data load mapping area.

Only explicit mappings are currently supported with the DRM to FDMEE integration.

This is all very nice but what is happening behind the scenes to achieve this, first of all the “startExportByName” DRM Web Service operation is called.

FDMEE generates the input parameters and the export name is defined in the DRM options in FDMEE and in my example is “FDMEE Planning Map Export

The “fromVersionName” and “toVersionName” is hardcoded to “FDMEE_MAP_VERSION” so this is why it is important to set the version to use this variable in the DRM export profile.

The “runtimeParameters” contains the load id, location name and location key.

I can demonstrate the process by replicating the Web Service operation execution by using a SOAP client, I have removed the header information to make it more readable, if you want to understand the requirements for the SOAP header have a look at my previous posts.

The response contains information like the job ID and current status.

FDMEE checks for a status of “Done” by calling the operation “getJobStatus”.

The export is basically extracting records from the DRM applications database tables and inserting them into the FDMEE mapping staging table “TDATAMAP_STG

Even though the Load ID and Location Key are passed into the DRM Web Service operation call they are not used in the export profile, once the export has completed FDMEE executes SQL to populate the LOADID, PARTITIONKEY and DIMNAME columns.

If you want to see the SQL that is generated turn up the logging level in FDMEE and then view process details log after importing the mappings.

The mapping information is then taken from the staging table and populated in the core FDMEE mapping table.

When viewing the mappings in FDMEE the "TDATAMAP" table is queried and the results are then displayed in the interface.

Now that the mappings are in place the data can be extracted from the source system, mapped and then loaded to the target application.

So that pretty much completes the FDMEE and DRM integration process, I hope you have found it useful and informative.


Suyog Mandloi said...

Thanks a lot John for this post. Really helpful

suyog mandloi said...

Thanks for this wonderful post John