Smart Merge in FDMEE

A while ago I got asked to come up with a solution for Smart Merge. Now I’m more a database developer then much else, so my first though was lets do it on the DB. As I spend much time coding this and I got to say I’m not yet really happy with my jython skills I’m not sharing the code, but the idea. Hopefully it will help you get trough getting it developed.

I used 2 additional artefacts on the DB. One table and one view. The view is droped and recreated during the BefLoad. Basically what I did is create a table that is similar to TDATASEG and into which before a new load to TDATASEG is done copies the lines for the pertinent CATKEY, PERIDKEY and ENTITY, to the auxiliary table.

The overview of steps executed:
I. In BefImport: Data from TDATASEG for previous relevant load is stored in TDATASEG_SM, and view TD_TO_TDSM (which holds: TDATASEG_SM – TDATASEG) is created for current CATKEY, PERIDKEY and ENTITY
II. In AftExportToDat, based on the view we append lines with NODATA

The additional candy is the fact there a function that sorts trough the file and makes a list of all entities in there, so to query dynamically which Entities are present in the file.

Got questions about it? Just leave me a comment bellow and I will try to explain, without giving out the code 🙂

UPDATE: After few months of testing I had to move the load of TDATASEG_SM into the AftLoad Event script so to ensure the data loaded in there is the data that is relevant for the next load.

2 comments

    1. Hello, sure I can do that. The idea is you have to somehow keep the latest data set that was successfully loaded to HFM. So in that moment (AftLoad.py) with an insert statement, I move the relevant data, so depending on the number of UD you are using you might need more columns, and you don’t need to insert amounts, as you don’t mind about that you simply, mind it was loaded. I would suggest to filter by Entity, Scenario, Period and possibly either location or DLR.

      Once you have this part down, what you need to do is make a difference between what you just loaded into FDMEE and what you loaded before, so what is in your TDATASEG_SmartMerge table. You can do that by a view, where you make a difference of those two tables.

      Last part write the missing lines to the file which FDMEE will create for its own load, this should be in the AftExportToDat step.

      Overall this solution is mostly based on the database and only used file manipulation for adding the NoData lines in the AftExportToDat.py

      Hope this gives you some more information. If you ask more specific question it will be easier for me to help you on the points that might still not be clear.

Leave a Reply

Your email address will not be published. Required fields are marked *