Never thought that I’ll write something on FDM (I consider myself as a beginner on FDM and still need to learn a lot). However here I’m writing about customizing FDM load script.
After lot of struggle with the tool (I’m getting old, it not easy learning from here on) I was able to get the data load automated.
Let’s say I got help from couple of sources (Tony Scalese’s blog and from my colleague Matt Tyburowski)
I started the load and it was running forever!!!!!!
I was first blaming my lack of FDM knowledge, so decided to look at Essbase log and check what is happening.
I saw that each clear (fired by FDM) was taking 4.25 secs. Now you’ll think why is he bothered about 4.25 secs.
Let’s do some math.
I’ve 123238 lines in the load file and FDM load script runs the clear for each single line which makes
4.25*123238=523761.5 secs, 8729 mins close to 145 hours (I don’t think I can wait that long
)

The issue was with how the Calc script is generated by FDM.
'*** CALC SCRIPT *** 'NOTE: CALC SCRIPT CAN BE MODIFIED For Each CUSTOMER 'Clear the intersection of (Category, Period, Entity) strClear = "Fix(""" & strTCat & """,@Idescendants(""" & strEntity & """))" & vbcrlf strClear = strClear & "CLEARDATA """ & strTPer(0) & """;" & vbcrlf strClear = strClear & "EndFix"
It says @Idescendants (strEntity), which is a level 0 entity and the statement is generating an empty memberset and the script runs for the entire Entity dimension. Read about the issue with EMPTYMEMBERSET here.
I decided to update it with the set commands
'*** CALC SCRIPT *** 'NOTE: CALC SCRIPT CAN BE MODIFIED For Each CUSTOMER 'Clear the intersection of (Category, Period, Entity) strClear = "SET CALCPARALLEL 2;" & vbcrlf strClear = strClear & "SET CALCTASKDIMS 1;" & vbcrlf strClear = strClear & "SET EMPTYMEMBERSETS ON;" & vbcrlf strClear = strClear & "Fix(""" & strTCat & """,@Idescendants(""" & strEntity & """))" & vbcrlf strClear = strClear & "CLEARDATA """ & strTPer(0) & """;" & vbcrlf strClear = strClear & "EndFix"
I kept wondering why this is not there in the default script.
Thrilled by my discovery I started loading data again. The changes did reduce the time. It is now taking 0.016 secs, which is good. But is it?
Math shows that 0.015*123238=1971.808 secs, 33 mins (at-least it is not hours). Can we reduce it further?
As you know (and I learned after a long time) that FDM runs the clear starment for each single line. If you have an entity which is repeated 10 times, it will run the clear 10 times

Which is kind of dumb. Is there a way to get unique member list from FDM data file and run clear on those members? Yes there is

I created a dictionary to read each entity from the FDM generated data file.
The good thing about dictionary is I can check whether the entity is present in it, if not present add it to the dictionary, else proceed with next entity. This way I can get the unique member list (Entity) and run the script only for those members.
Here is the updated script.
'Initialize the file path strFileWrite = CStr(Left(strFile,Len(strFile)-4)) &"_entity" & ".txt" ' The script will write all unique entities to a Dictionary ' This is done to get a unique list of members while generating the clear script Set objReadEntityfrmDat = CreateObject("Scripting.FileSystemObject") Set objFReadEntityfrmDat = objReadEntityfrmDat.CreateTextFile(strFileWrite, 1, RES.FDMFalse) objFReadEntityfrmDat.Close Set objFReadEntityfrmDat = Nothing Set objReadEntityfrmDat = Nothing 'Create Dictionary to get unique values Set d = CreateObject("Scripting.Dictionary") Do While Not objFRead.AtEndOfStream ' Reading the load file generated by FDM strLineRead = objFRead.ReadLine strCurEnt = DW.Utilities.fParseString(CStr(strLineRead), CInt(intFldCount), CInt(intEntityFld), " ") If Not d.Exists(strCurEnt) Then d.Add strCurEnt, 0 End If X = d.Items Loop 'Do While Not objFRead.AtEndOfStream ' This opens the unique entity list file in append mode ' each unique entity from dictionary is written to the entity unique file Set objReadUnqEntity = CreateObject("Scripting.FileSystemObject") Set objFReadUnqEntity = objReadUnqEntity.OpenTextFile(strFileWrite, 8, True) For Each strKey In d.keys If strKey <> "" Then objFReadUnqEntity.WriteLine (strKey) End If Next objFReadUnqEntity.close Set objFReadUnqEntity = Nothing Set objReadUnqEntity = Nothing 'Open the unique entity file that was generated to get Entity Set objFSRead = CreateObject("Scripting.FileSystemObject") Set objFRead = objFSRead.OpenTextFile(strFileWrite, 1, RES.FDMFalse) Do While Not objFRead.AtEndOfStream strLineRead = objFRead.ReadLine strCurEnt = strLineRead 'Check to see if we're on the first line. If so we must calc the first entity If strEntity = "" Then strEntity = strCurEnt bolFirstLine = RES.FDMTrue End If If strTargYear = API.POVMgr.fPeriodKey(CStr(strPer(0))).strTargetYear Then '*** CALC SCRIPT *** 'NOTE: CALC SCRIPT CAN BE MODIFIED For Each CUSTOMER 'Clear the intersection of (Category, Period, Entity) strClear = "SET CALCPARALLEL 2;" & vbcrlf strClear = strClear & "SET CALCTASKDIMS 1;" & vbcrlf strClear = strClear & "SET EMPTYMEMBERSETS ON;" & vbcrlf strClear = strClear & "Fix(""" & strTargPD & """,@Idescendants(""" & strEntity & """),""" & strTargYear strClear = strClear & """,""" & strTargVer & """)" & vbcrlf strClear = strClear & "CLEARDATA """ & strTCat & """;" & vbcrlf strClear = strClear & "EndFix"
You can see that I was writing that to a file, you can remove it if you don’t want it. I’ve 1309 entities in the file and my clear script runs 21 secs

Hope it saves some time of yours