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.

So what if we would like to export data to a file via CalcScript DATAEXPORT function, and then use the output file to be feed to FDMEE? How do we do that? Easy: call it from a Maxl script, wrap that into a batch file, and call it from the BefImport, so the file is there at Import step. All this was done using the knowledge Francisco has shared a while ago on the OTN.

But let’s look into the exact steps to take:
1) Create a simple Maxl script (which I encrypted because reasons, and so it’s called mxl.mxls):

2) Create a CalcScript that looks like something similar to this (and named Export, as seen above):

Why like that? ‘Cause I set up in the DLR to use the file output.txt in the inbox:

3) Then make a batch file (mxl.bat), with the following content – & just feeding Essbase back his private key, so it can decrypt the script for itself:

4) As the last step put the following code as BefImport script:

Quick and painless!