Resolving Calc Script Validation Warnings

When you create and validate Configurable Calculations or On-Demand rules that use Essbase Calc Script, as part of the validation process, Financial Consolidation and Close provides warnings about issues that can cause performance degradation when you run the rule. To see these warnings, select the Errors and Warnings tab in Calculation Manager and click on the green button to run script diagnostics.

In the following screenshot, the cells highlighted in green are warnings from Financial Consolidation and Close. The other warnings displayed are from Calculation Manager.

Note:

The Financial Consolidation and Close Rule Validation warnings are also displayed in the Recommendations module. See Viewing Application Recommendations.

Warning messages are sorted and shown based on the line number of the script.

To view the rule validation warnings in Calculation Manager:

Open Calculation Manager and select the Errors and Warnings tab.
Click the Run icon to display the warnings.

The following sections provide details on how to resolve Rule Validation warnings.

Calc Manager Script Validation for Use of Anchor

Follow these guidelines for the correct usage of Anchor in Calc Manager scripts:

For Non-DSO Applications

If your FIX block has a single Movement member, Financial Consolidation and Close recommends that you use that Movement member as anchor.
If you have more than one Movement member in the FIX block, you can’t choose Movement as the anchor. In that case, you can choose a member from other Sparse dimensions as an anchor.
- Note for configurable calculations (insertion points), you can’t choose a member from Scenario, Year, Period,Entity or View as anchor.
- For On Demand Rules (ODRs), you can’t choose a member from Scenario, Year, Period, Entity, Consolidation or Currency as anchor.
Avoid using a member from a Dense dimension as anchor. In the case of non-DSO applications, Account is a Dense dimension. For Dense member blocks, CALCMODE(BOTTOMUP) will not provide any performance advantage.

For DSO Applications

In DSO applications, Movement is a Dense dimension. If you are moving from a non-DSO to a DSO application, you should make sure that a member from the Movement dimension is not your anchor. You can choose a member from the Account dimension as anchor for the member block, as Account is Sparse in DSO applications.
If you can’t choose an Account member as anchor, choose a member from any other Sparse dimensions as anchor.
- Note that for configurable calculations (insertion points), you can’t choose a member from Scenario, Year,Period, Entity or View (Dense) as anchor.
- For On Demand Rules (ODRs), you can’t choose a member from Scenario, Year, Period, Entity, Consolidation or Currency as anchor.
Avoid using a member from a Dense dimension as anchor. In DSO applications, Movement and Period are Dense dimensions. For Dense member blocks, CALCMODE(BOTTOMUP) will not provide any performance advantage.

Following is an example of a Configurable Calculation (insertion rule) for a non-DSO Application. Each line is denoted by #, which is the line number.

#1 FIX ("FCCS_Periodic", "FCCS_Journal Input", "FCCS_Managed Data", "IFRS_IN ", "Entity Currency", "FCCS_Mvmts_NetIncome", @relative("Total Categories", 0), @relative("Total Area", 0), @LEVMBRS("Intercompany", 0) @relative("Total Custom", 0)) 
#2        "FCCS_Entity Input" (
#3           IF (@ISMBR("Actual_Red")) 
#4                IF (@ISUDA("Entity", "B10") AND ("FCCS_Mvmts_NetIncome" <> #Missing)) 
#5                    ENDIF
#6            ENDIF
#7        ) 
#8 ENDFIX

In this script example, the rules validation process will generate the following warning:

Line 2:Use Movement member FCCS_Mvmts_NetIncome as an anchor
				instead of Anchor member FCCS_Entity Input.

Following is an example for a Configurable Calculation (insertion rule) for a DSO Application. Each line is denoted by #, which is the line number.

	#1   FIX("FCCS_Periodic","FCCS_No Intercompany","No operating Expense","Product1    ","CORP_IN","IC_PROFIT_ACC ")
	#2    	"FCCS_Managed Data"(
	#3	@CALCMODE(BOTTOMUP);
	#4          IF (@ISMBR("FY21"))
	#5          	"FCCS_OpeningBalanceAdjustment"=10;
	#6         ENDIF
	#7	 )
	#8     ENDFIX

In this script example, the rules validation process will generate the following warning:

Line 2: Use Account member IC_PROFIT_ACC as an anchor instead of Anchor member FCCS_Managed Data.

Calc Manager Script Validation for @CALCMODE (BottomUp)

@CALCMODE(BOTTOMUP) Vs @CALCMODE(TOPDOWN)

Financial Consolidation and Close recommends using BOTTOMUP, as compared to TOPDOWN for faster calculations using Configurable Calculations (Insertion Points) or On-Demand Rules (ODRs).

During BOTTOMUP, which is the default approach for calculations, Essbase determines which existing data blocks need to be calculated before it calculates the data using the Sparse member blocks. Essbase then calculates only the blocks that need to be calculated during the full database calculation.

Example: A = B + C

A is calculated only if B and C exist in the database. The dependency of A on B and C is known before the actual calculation starts.

During TOPDOWN, Essbase calculates the formula on all potential data blocks using the Sparse members in the member block.

Example: A = B -> D + C -> D

To calculate the formula, Essbase must examine every combination of A to see whether B -> D or C -> D exists.

Essbase uses BOTTOMUP as the default mechanism to calculate a formula assigned on a Sparse member, unless the formula is complex in nature, in which case the calculation will run TOPDOWN.

A complex expression is one that satisfies the following criteria:

Contains cross-dim operators [ -> ]
Uses one or more range functions, for example, @AVGRANGE, @MAXRANGE, @MINRANGE, or @SUMRANGE
Uses relationship or financial functions, for example, @ANCESTVAL, @NEXT, @PARENTVAL, @SHIFT, @ACCUM or @GROWTH

In such situations, Essbase provides the @CALCMODE(BOTTOMUP) function to enforce a sparse formula calculation to run BOTTOMUP.

Following is an example for a Configurable Calculation (insertion rule). Each line is denoted by #, which is the line number.

#1 	FIX ("FCCS_Periodic", "FCCS_Entity Input", "Entity Currency", "FCCS_ClosingBalance_Input","FCCS_Balance Sheet","FCCS_Total Data Source") 
#2   "FCCS_Income_Statement"(
#3   "FCCS_Movements"=@CURRMBR("Entity")->"FCCS_Contribution" -> "FCCS_Movements";
#4   )
#5   ENDFIX

In this script example, the rules validation process will generate the following warning:

Line 2: Use @CalcMode (BOTTOMUP) for member block FCCS_Income_Statement.

For more information, see this topic: https://docs.oracle.com/cd/E57185_01/ESBTR/calcmode_func.html

Calc Manager Script Validation for Hybrid BSO

For hybrid Financial Consolidation and Close applications, it is recommended to SET HYBRIDBSOINCALCSCRIPT NONE/FULL wherever applicable for the best performance results.

By default, Configurable Calculations (insertion rules) have HYBRIDBSOINCALCSCRIPT set to FULL, and for On-Demand rules, HYBRIDBSOINCALCSCRIPT set to NONE.

The rules validation process checks if the script contains CustomTop Dimension members, which have the Dynamic Calc storage type.

If the expression has 0 or 1 CustomTop Members, a warning is displayed to set HYBRIDBSOINCALCSCRIPT to NONE (if it is set to to FULL).
If the expression has two or more CustomTop Members, a warning is displayed to set HYBRIDBSOINCALCSCRIPT to FULL (if it is set to NONE).

Following is an example of an On-Demand rule. Each line is denoted by #, which is the line number.

	#1 FIX ("FCCS_Periodic", "FCCS_No Intercompany", "No Product", "FCCS_Mvmts_NetIncome", @RELATIVE("AllDepts", 0), "No Department") 
	#2    FIX (@RELATIVE("TotalHFM", 0)) 
	#3        "StkCmpRev_CE" = -("555011_CE"->"FCCS_YTD"->"FCCS_Intercompany Top") - ("555012_CE"->"FCCS_YTD"->"FCC_Intercompany Top");
	#4    ENDFIX 
#5 ENDFIX

In this script example, the rules validation process will generate the following warning:

Line 3: Disable Hybrid calculation for this block with SET
				HYBRIDBSOINCALCSCRIPT NONE.

For more information, see this topic: https://docs.oracle.com/en/cloud/paas/analytics-cloud/tress/hybridbsoincalcscript.html.

Calc Manager Script Validation for @CALCMODE (BLOCK)

For this specific use case, Financial Consolidation and Close recommends that you add @CalcMode (BLOCK) wherever applicable to improve performance.

For more information on @CalcMode (BLOCK), see this topic: https://docs.oracle.com/cd/E57185_01/ESBTR/calcmode_func.html.

To identify whether @CALCMODE(BLOCK) is required within an Anchor block for both DSO and Non-DSO applications, the rules validation process checks the following conditions:

If the expressions in the Anchor block are complex
If the expression contains certain functions, for example, @ANCEST, @CURRMBR, @ISMBR on a dense member, @MDANCESTVAL, @MDPARENTVAL, @MDSHIFT, @NEXT, @PARENT, @PARENTVAL, @PRIOR, @SANCESTVAL, @SPARENTVAL, @SHIFT

If these conditions are met, the validation process issues a warning that includes the line number.

Following is an example of a Configurable Calculation (insertion rule). Each line is denoted by #, which is the line number.

#1 	FIX ("FCCS_Periodic", "FCCS_Entity Input", "Entity Currency", "FCCS_ClosingBalance_Input","FCCS_Balance Sheet","FCCS_Total Data Source") 
#2   "FCCS_Income_Statement"(
#3   "FCCS_Movements"=@CURRMBR("Entity")->"FCCS_Contribution" -> "FCCS_Movements";
#4   )
#5   ENDFIX

In this script example, the rules validation process will generate the following warning:

Line 2: Use @CalcMode (BLOCK) for member block FCCS_Income_Statement.