Clear only unique members from data file – FDM


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 Winking smile)

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 Sad smile

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 Smile

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 Winking smile

Hope it saves some time of yours Smile


About Celvin Kattookaran

I’m an EPM Consultant, my primary focus is on Hyperion Planning and Essbase. Some of you from Hyperion Support team might recognize me or have seen my support articles, I was with the WebAnalysis Support Team. I'm an Independent Consultant with “Intekgrate Corporation” based out of Aurora office. I’m from God’s Own Country (Kerala, India), lived in all southern states of India, Istanbul and Johannesburg (and of course United States). I’m core gamer :) and an avid reader. I was awarded Oracle ACE Director for my contributions towards EPM community.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.