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
