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

Function: @Return

@Return is one of the very useful function that is introduced in 11x. I have been using it more frequently now and some of the cases are listed below:
Syntax:

@RETURN ("ErrorMessage", ERROR)

1. In custom Workforce planning when business need to add TBH (To be Hired) with basic information such as Start Date, Grade, salary. While adding, system need to validate that date and salary is provided and this is where @return comes handy. If validation fails, one can use @Return function with appropriate message.

2. In one of the requirements, there was a need to archive existing projects to dummy members (user selected) and if required restore back to existing available project member. system must not allow user to move project to a member that already has data. So, it was necessary to give appropriate message to the user. I used the following command in case the validation fails:


@RETURN(@CONCATENATE
(
@CONCATENATE
(
            @CONCATENATE("ERROR: Failed to archive selected project [", @NAME({Project})),
@CONCATENATE("] under Entity [", @NAME(@CURRMBR("Entity")))
)
,
@CONCATENATE
(
@CONCATENATE("]. The target project [", @NAME({Archive_Project_to})),
"] is already in use. Try using another new / archived project."
)
)
, ERROR);

The message looks as follows:

"ERROR: Failed to archive selected project [Project1] under Entity [Entity1]. The target project [Temp_Proj1] is already in use. Try using another new / archived project."