Rule / Script Performance: Condition on Sparse vs Dynamic Calc Member

Conditions in Business rules / Calc Script are quiet common but if not written correctly can take a very long time to execute (which may again depends on the database size and combinations it has to traverse).

Following is the classic example where when condition was placed on upper level of dynamically calculated dense dim member, it took substantial amount of time to execute and complete the script. Whereas when the same dynamic dense member data was copied to a temp stored member and then the stored member was compared, it executed within minutes.

Example Description:
Clear blocks depending on whether there exists any value in a mandatory assumptions and / or PAT.

Account, Period and View  are dense dimensions. The requirement is if there is no data in "Volume_Units" and "PAT" members at "Local" level, clear blocks from "Local","USD","AUD", and "NZD" members. Also, all upper levels of Account dimensions are dynamic calc members i.e. in our case "PAT" is dynamically calculated.

In the following code ,"PAT" is used in the condition and it took around 20 hours to complete.

//ESS_LOCALE English_UnitedStates.Latin1@Binary
SET UPDATECALC OFF;
SET AGGMISSG ON;

FIX (/*Year*/ "FY15",
/*Scenario*/ "Forecast",
/* Version */ "Working",
/* Currency */ "Local"
)
FIX(/*Customer*/  @Relative("Customer",0),
/*Product*/ @Relative("Product",0),
/*Entity*/ @Relative("Entity",0),
/*Currency*/ "USD","AUD","NZD",
/* Period*/  @Relative("YearTotal",0), "BegBalance"
)
"MTD"
(
IF(("Local"->"Volume_Units" + "Local"->"PAT" + 0 ) == 0)
"MTD" = #MISSING;
  ENDIF
)
ENDFIX
ENDFIX

The following code first copies "PAT" to a temporary stored member and then the stored member is used in the condition. This code just took around 20 minutes to complete.

//ESS_LOCALE English_UnitedStates.Latin1@Binary
SET UPDATECALC OFF;
SET AGGMISSG ON;

FIX (/*Year*/ "FY15",
/*Scenario*/ "Forecast",
/* Version */ "Working",
/* Currency */ "Local"
)
FIX(/*Customer*/  @Relative("Customer",0),
/*Product*/ @Relative("Product",0),
/*Entity*/ @Relative("Entity",0),
/* Period*/  @Relative("YearTotal",0), "BegBalance",
/*View*/ "MTD"
)
"TEMP" = "PAT";
ENDFIX

FIX(/*Customer*/  @Relative("Customer",0),
/*Product*/ @Relative("Product",0),
/*Entity*/ @Relative("Entity",0),
/*Currency*/ "USD","AUD","NZD",
/* Period*/  @Relative("YearTotal",0), "BegBalance"
)
"MTD"
(
IF(("Local"->"Volume_Units" + "Local"->"TEMP" + 0 ) == 0)
"MTD" = #MISSING;
  ENDIF
)
ENDFIX
ENDFIX

Loading Metadata: Improve Performance by using file compare utility

There are many reasons for an organisation to refresh metadata daily in Hyperion Planning and now a days you will generally have at least 1-2 hierarchies which could have 1000s of members. Not to mention if there are alternate hierarchies with shared members then outline load utility might take a while to delete and reload hierarchies.

There was a situation where my client had very tight schedule within which all the hierarchies needed to be reloaded and we were not able to fit that due to the source system’s variations in delivering the files.

So to resolve this issue and/or reduce the impact, it was decided to check every hierarchy whether there is any change compared to previous day’s file. If change exits then load it else skip and go to next. This was achieved by using Windows FileCompare utility and worked quiet well.

Following is the extract from the file which calls outline load utility to load hierarchies one after another.

LoadHierarchies.Bat (extract):

SET DimName=Account

SET InputFileName=%DimName%_dim.csv

SET LoadLogFile=FC_%DimName%_Load.log

CALL D:\script\Custom_FileCompare %InputFileName% %LoadLogFile%

if %CUSTOM_RESULT% EQU 0 goto :LOAD_Another

SET LoadLogFile=%LogFilePath%\%DimName%_Load.log

SET LoadExceptionFile=%LogFilePath%\%DimName%_Load.err

CALL Outlineload -f:%PasswordPath% /A:%App% /U:%UserName% /I:%DimFilePath%\%InputFileName% /D:%DimName% /L:%LoadLogFile% /X:%LoadExceptionFile%

:LOAD_Another

Other Code goes here...

In the following file “PreFilePath” is the path where previous day’s file(s) are placed while “DimFilePath” points to current or latest file which shall be loaded based on comparison.

Custom_FileCompare.bat:

SET CUSTOM_RESULT=0

SET CompFileName=%~1

SET FCLogFileName=%~2

SET File1=%PreFilePath%\%CompFileName%

SET File2=%DimFilePath%\%CompFileName%

FC %File1% %File2% > %LogFilePath%\%FCLogFileName%

SET CUSTOM_RESULT=%ERRORLEVEL%

if %CUSTOM_RESULT% EQU 0 ECHO Same files. Ignore this hierarchy load.