Saturday, 14 November 2015

... FDM batchloader

It’s been almost two months since my last post but I can say that all this time I had been working on a module that it is really interested but I had not worked before.

So in this post, I am going to describe my experience with FDM Batchloader. I know that it is a bit outdated topic since FDM has been replaced by FDMEE but in my defence not all the companies have upgraded yet.

Proem

As a summary, FDM Batchloader is a module that automates the steps that the user has to execute in order to import a text file with ERP data in the FDM tables, map the data to the HFM or Essbase members and finally load them in HFM or Essbase.

In order to use the FDM batchloader, the administrator has to create a custom script in the FDM application and schedule Task manager to execute the script whenever is required. The script will be triggered whenever a file will be save in the folder: \Inbox\Batches\OpenBatch.

Main

 

The file

In order to describe things easier, I will focus only on the standard text file and not the multiload.

FDM batchloader requires a specific structure in the filename in order to be able to process the file. After a lot of attempts, I believe that the structure of the filename should be similar to:

[HFM username]~[Location]~[Scenario]~[Period]~[Load Method]~[ERP Entity]

  • HFM username: The username of the person that will check the data in HFM (This will be used as a source in order to identify the recipient of the e-mail).
  • Location: The FDM location that the file should be imported, e.g.: Greece_Actuals
  • Scenario: The FDM scenario that the file should be imported, e.g.: Actual 
  • Period: The FDM period that the data should be imported, e.g. Nov - 2015. 
  • Load Method: This is a combination of the two characters. The first character defines how the data will be loaded in the FDM table and it can be "A" for Append or "R" for replace. The second character defines how data will be exported in HFM and will be "A" for Append and "R" for replace (So, if you would like to replace the data both in FDM and HFM, you need to use the load method "RR").
  •  ERP Entity: This is the ERP location. If you import data from more than one ERP locations in one FDM location, you need to differentiate the files.

This file should be saved under the folder: \Inbox\Batches\OpenBatch. The structure of the file is defined by the import format of the location.

Finally, after the completion of the batchloader routine, the file will have been moved in a new folder under: \Inbox\Batches\

The script

The basic script looks like:

However, FDM is a really flexible tool and the developer can build a huge number of additional functionalities that will allow the user to process any error quickly. The majority of the scripts can be found in the FDM Workbench as well as in the FDM manuals. Some of the functionalities are:
  • Manage the file that will be processed e.g. rename the file, copy the file in another folder, and delete the file from the folder.
  • Read the FDM database and retrieve the status and the progress of each file of that is processed by the batchloader.
  • Run the FDM reports, save them as a pdf file and summarise the data and the mappings that have been processes.
  • Retrieve the xml intersection validation report from the folder API.DataWindow.Connection.PstrDirOutbox & "\Logs”.
  • Create a custom message to guide the users to fix any possible errors.
  • Create and send e-mails to the users.

The username

In order to run the FDM batchloader, a user has to be created and this user needs to have write access both in the FDM locations as well as in the HFM entities.

Troubleshooting

The most usual issues are:
  • The file with the ERP data has not been moved to the \Inbox\Batches\OpenBatch.
  • The filename is not correct.
  • The file is empty or the structure is not as expected.
  • Missing mappings
  • Wrong intersections between mapped accounts and custom dimensions

Epilogue


After having used the batchloader, I think that it is safe to say that it is an accelarator for the FDM related tasks. The user extracts the file from the ERP to the predefined folder, the file triggers the FDM batchloader and if there are no issues the data are loaded in HFM (1 to 2 minutes process?).

This means that the company does not need to train users in the FDM processes and the users do not need to spend time on data management tasks but focus on business related analysis and reporting tasks.

As in my previous post I will say the same. I am not a FDM guru, however I believe that the majority of the FDM users do not use more than 60% of FDM's functions. Small and quick changes as the one mentioned above will not cost a lot of money and will have a great impact on Finance users.
 
Good luck,

Cheers,

Thanos

...Smartview and OneDrive

Recently (July 2017), I discovered a new functionality of OneDrive which allows multiple users to work on the same file concurrently a...