Understanding @IDESCENDANTS function

It is always good to know as how certain function works so that we can get desired, appropriate and expected results.

@IDESCENDANTS ("Zone"):

@IDESCENDANTS function populates all descendants including the specified member however there is a way by which it retrieves the data.

The following screenshot shows how @IDESCENDANTS populates members. Sequence indicate the order in which it populates the members during calculation.

Also, note that this is same as @IDESCENDANTS ("Zone",0)

@IDESCENDANTS ("Zone",-1):

Using negative integer for example -1, system will fetch / populate all members from level 1 upto the selected member. In short, it will exclude level zeros.

See the screenshot below:

@IDESCENDANTS ("East",3):

When you provide a positive number it takes as generation number relative to hierarchy and not the selected member.

So based on the example shown in the screenshot, system will fetch all generation 3 members along with the selected members. 

Note that if the selected member's generation number is less than or equal to the parameter then system will only fetch the specified member.

For example, for @IDESCENDANTS("ENT_E5",2), system will only fetch "ENT_E5" member as generation number of "ENT_E5" is 3 which is less than 2 (parameter).

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

Calculation Manager Business rule / Script validation issue

Business rule / Script not getting validated when line of code exceeds certain number

We have a calculation manager business rule that calls a script which calls a template around 22 times by passing different entity values. When we compiled the business rule it threw the following error:

A validation error was received from the Planning server 'http://<URL>:80/HyperionPlanning/servlet/HspAppManagerServlet?appname=<appname>'.

'Error:Error parsing formula for [<member name>] (line 5106): infinite loop in macro processing; current macro [@VAL] Rule <Rule Name>'.

On analysis, it is observed that when we made less than or equal to calls (17 – where line of code is less that 5000) to the template then it successfully compiled. In short, any further calls (more than 17 – i.e when line of code is probably greater than 5000) to the script system failed to compile.

To confirm where this is the issue with number of line, I created a rule with entire logic written as part of single script which had more than 5000+ lines. Again this time the system failed to validate and gave the same error.