Configurable calculations best practices for Financial Consolidation and Close (FCC) for Enterprise Performance Management (EPM)

Calculation Concepts

These are the essential concepts for creating calculations:

Data Blocks
Basic Script Format
BottomUp versus TopDown Calculations
Block versus Cell Mode

Data Blocks

The following figure shows a Data Block from a sample application.

Stored members of dense dimensions constitute a Data Block. Block size for the sample application above is 2 (Sales and Cash) x 8 bytes = 16 bytes.
Unique combinations of sparse dimensions members constitute INDEXes and point to Data Blocks. In the sample application above, there are total of 2 (Actual, Budget) x 2 (FY17, FY18) x 2 (Jan, Feb)= 8 indexes.

In Essbase Block Storage Option (BSO) databases, a block constitutes stored members of a Dense dimension. In Financial Consolidation and Close, by default, Account is the only Dense dimension.

In this example, the Account dimension is Dense, and has 1977 stored members. This indicates a single BSO Database Consol’s block has 1977 cells - each represents an Account member.

The Block size, in bytes will be:

Block Size (Bytes) = Number of Stored members in Account * 8
Block Size (Bytes) = 1977 * 8 = 15,816 bytes

Note: To view the database properties, from Calculation Manager, select Action, and then Database Properties.

Best Practices for Creating Data Blocks

When a configurable calculation that writes to a data cell is executed, a data block must exist for data to be written to the database.

Data blocks are combinations of stored Sparse and Dense dimension members.

Separate data blocks are created for each combination of stored Sparse dimensions. The members in a Dense dimension equal one block.

When you create configurable calculations, you may need to create additional blocks to store the calculated results and resolve issues with missing data.

You can enable the Auto-Create Blocks option to have the system automatically create missing blocks. See Enabling Auto Create Blocks for Configurable Calculations.

If you use Bottom-Up processing in your configurable calculations, you should manually create data blocks or ensure that data blocks already exist.

You can manually create data blocks using one of these methods:

Assigning data during the data load process. For example, write a "Zero" to a single Dense member intersection, and then write "#missing" to clear the "Zero" after block creation.
Using the Essbase DATACOPY command, in which all blocks from the source are copied to the destination, including the missing cells. However, this method can potentially create unnecessary blocks and slow the consolidation process.

When to use Auto-Create Blocks

Auto-Create Blocks is a provided setting to create missing blocks during a Configurable Calculation.

This setting has a big performance impact, because it uses the potential block algorithm to search the entire database for the presence of blocks and accordingly creates a missing block, if one does not exist.

Use this setting only when you are completely sure that none of the other block creation techniques are appropriate.

The @CALCMODE(BOTTOMUP) function (if used in an insertion point) and Auto-Create Blocks are mutually exclusive.

Creating Target Data Blocks for @SHIFT and @PRIOR Functions

When you use the @SHIFT or @PRIOR functions in calculation scripts, the target data blocks must exist before you can run the calculation. The target data blocks must exist as either part of another calculation or data load, or must be created using the @CREATEBLOCK function.

Example Use Case:

Data exists in Actual, FY16, P12, ML_HFM. The data is being pulled from Oracle Hyperion Financial Management, and has not been loaded in Actual, FY16, P1, ML_HFM. The data must be retrieved from the prior year's P12 period, and a reversal entry should be reflected at Actual, FY17, P1, ML_HFM_Calc.

The calculation script is as follows:

No journals have been posted ("FCCS_Journal Input" in P13. The code is expected to take the following path, with "ML_HFM_Calc" as the sparse member anchor:

@SHIFT("P12"->"ML_HFM", -1, @CHILDREN("Years"));

However, this returns #MISSING.

Workaround 1:

Workaround 2:

ClearEmptyBlocks Rule Guidelines

The ClearEmptyBlocks business rule helps to scavenge the Consol cube for any empty data blocks. Empty data blocks could be generated as part of:

OnDemand rule execution that generates empty blocks. For example, using the @CREATEBLOCK function, and then possibly a generated empty data block never gets utilized.
An insertion point code (for example, FCCS_20) that possibly has block leaks due to TOPDOWN calculations, perhaps from assigning #MISSING; using sparse anchors, instead of using @CALCMODE(BOTTOM UP)
Financial Consolidation and Close System Calculations

Recommended Practice to Run ClearEmptyBlocks Rule

A best practice is to run the rule after finishing any OnDemand Rule/insertion point tests, when the script is in development phase. The ClearEmptyBlocks rule helps to measure block statistics before and after execution of the calculation under development.
In production phase, execute the rule after finishing a full year consolidation for a given year.

An EPM Automate script could be scheduled to run after office hours, every weekend:

call epmautomate runbusinessrule ClearEmptyBlocks Scenario ="<Scenario>" Year = "<Particular Year>"
Period = "ILv10Descendants(YearTotal)"
call epmautomate restructurecube Consol

Note: The schedule for this activity must maintain at least a gap of three to four hours with the Daily Maintenance Cycle.

Basic Script Format

The following figure shows sample Calc Script format.

Writing Configurable Calculations

The following figure shows the Configurable Calculation rules on the Consolidation Process Local Currency tab.

The following figure shows the correspnding Configurable Calculation rules from the Consolidation Process Local Currency tab.

A configurable calculation helps you perform customized calculations that involve three categories of data:

Untranslated Data: Entity Currency + (FCCS_Entity Input or FCCS_Entity Consolidation)
Translated Data: Parent Currency + (FCCS_Entity Input or FCCS_Entity Consolidation)
Eliminated Data: Parent Currency + FCCS_Elimination

It is important to understand the Currency and Consolidation combination, to write a configurable calculation within the correct Configurable Calculation Rule template, also known as an Insertion Point.

As an example, FCCS_30_After Opening Balance Carry Forward_Translated is supposed to be used, if and only if, FCCS default translation and FX calculation have already processed the data that needs special attention in FCCS_30.

Writing Configurable Calculation Examples

Consider an example of a Block Creation issue and the different approaches to solve the same calculation.

Use Case:

Add values in two accounts: Warehouse_Stock and Showroom_Stock loaded to FCCS_Managed Data, FCCS_Mvmts_NetIncome, FCCS_Local GAAP and No Product
Store the result of the calculation to Account Inventory_Stock at FCCS_Other Data, FCCS_Mvmts_NetIncome, FCCS_Local GAAP and No Product
Use the FCCS_10 configurable calculation

Approach 1: Using No Member Block (Anchor)

1.	FIX ("FCCS_Entity Input", "Entity Currency")
2.	FIX ("FCCS_Other Data", "FCCS_Mvmts_NetIncome", "FCCS_No Intercompany", "No Product",
"FCCS_Local GAAP")
3.	" Inventory_Stock " = "FCCS_Managed Data"->" Warehouse_Stock " + "FCCS_Managed Data"->
"Showroom_Stock ";

4.	ENDFIX
5.	ENDFIX

Disadvantages of this approach:

This is a dense calculation, considering that Inventory_Stock is an account on the left-hand side. Although the calculation is written correctly, the result of the calculation will not be seen, if no prior block exists at FCCS_Other Data and associated other FIX members to hold the result.
Cannot enforce conditional calculation restrictions, for example, IF..ELSE..ENDIF.
Workaround is required to manually introduce zero data blocks to above intersection.

Approach 2: Using Dense Member Block (Anchor)

1.	FIX ("FCCS_Entity Input", "Entity Currency")
2.	FIX ("FCCS_Other Data", "FCCS_Mvmts_NetIncome", "FCCS_No Intercompany", "FCCS_No Intercompany",
"FCCS_Local GAAP")

3.	" Inventory_Stock "(
4.	"FCCS_Managed Data"->" Warehouse_Stock " + "FCCS_Managed Data"->" Showroom_Stock ";
5.	)
6.	ENDFIX
7.	ENDFIX

Disadvantages of this approach:

This is a dense calculation, because member block Inventory_Stock is an account. Although calculation is written correctly, the result of the calculation will not be seen, if no prior block exists at FCCS_Other Data and associated other FIXmembers.
Workaround is required to manually introduce zero data blocks to above intersection.

Approach 3: Using Sparse Member Block (Anchor)

1.	FIX ("FCCS_Entity Input", "Entity Currency")
2.	FIX ("FCCS_Mvmts_NetIncome", "FCCS_No Intercompany", "No Product", "FCCS_Local GAAP")
3.	"FCCS_Other Data" (
4.	" Inventory_Stock " = "FCCS_Managed Data"->" Warehouse_Stock " + "FCCS_Managed Data"->
"Showroom_Stock ";

5.	)
6.	ENDFIX
7.	ENDFIX

Advantage of this approach:

This is a sparse calculation, because member block FCCS_Other Data is Data Source which is a sparse dimension. Calculation results in a block.

Disadvantage of this approach:

Member block calculation runs top-down, as cross-dimension operator is being used.

Approach 4: Using Sparse Member Block and BottomUp Calculation

1.	FIX ("FCCS_Entity Input", "Entity Currency")
2.	FIX ( "FCCS_Mvmts_NetIncome", "FCCS_No Intercompany", "No Product", "FCCS_Local GAAP")
3.	"FCCS_Managed Data"(@CALCMODE(BOTTOMUP);
4.	"FCCS_Other Data"-> "Inventory_Stock " = " Warehouse_Stock " + " Showroom_Stock "; 5.	)
6.	ENDFIX
7.	ENDFIX

Advantages of this approach:

This is a sparse calculation, because member block FCCS_Managed Data is Data Source, a sparse dimension.
Member block calculation runs BottomUp.
FCCS_Managed Data is the source of this calculation. The resulting block is created at FCCS_Other Data, if and only if the data block exists at source.
No cross-dim operator is required on the right-hand side of the calculation.
There is a need to explicitly specify the calculation as BottomUp, because there exists a cross-dim operator at the left-hand side of this assignment.

Block Versus Cell Mode Calculations

BLOCK mode: (default mode) In this calculation mode, Essbase groups the cells within a block and simultaneously calculates the cells in each group.
Block calculation mode is fast, but you need to carefully consider data dependencies within the block to ensure that the resulting data is accurate.
CELL mode: In this calculation mode, Essbase calculates each cell sequentially, following the calculation order, which is based on the outline.
Cell calculation mode is slower for obvious reasons. However, it ensures accurate results where data dependencies are concerned.
When Essbase compiles a formula, it prints a message in the application log file explaining the mode of execution for the formula similar to the following message:

Formula on member Profit % will be executed in CELL and TOPDOWN mode.

Essbase uses Block mode while calculating a formula, unless it uses functions such as:

@ANCEST
@CURRMBR
@ISMBR on a dense member
@MDANCESTVAL
@MDPARENTVAL
@MDSHIFT
@NEXT
@PARENT
@PARENTVAL
@PRIOR
@SANCESTVAL
@SPARENTVAL
@SHIFT
@XWRITE

To manually induce Block mode, use @CALCMODE(BLOCK). Ensure there are no data dependencies within your dense block.

Example of Block Mode

Perform the following calculation, based on month:

January - Sales Synergies is the sum of the children of Returns and Allowances
Februrary - Sales Synergies is the sum of children of Returns and Allowances - multiply by 20%
Remaining months - Sales Synergies is the sum of the children of Returns and Allowances - multiply by 10%

Block Mode

1.	FIX ("FCCS_Entity Input", "Entity Currency")
2.	FIX ("Sales Synergies", "FCCS_No Intercompany", "FCCS_Managed Data", "No Product", "FCCS_Local GAAP")
3.	"FCCS_Mvmts_NetIncome" (
4.	IF (@ISMBR("Jan"))
5.	@SUM(@Children("Returns and Allowances"));
6.	ELSEIF (@ISMBR("Feb"))
7.	@SUM(@Children("Returns and Allowances")) * 0.2;
8.	ELSE
9.	@SUM(@Children("Returns and Allowances")) * 0.1;
10.	ENDIF
11.	)
12.	ENDFIX
13.	ENDFIX

Cell Mode versus Induced Block Mode

Perform the following calculation, based on the month:

January - Sales Synergies is the sum of the children of Returns and Allowances

February - Sales Synergies is the sum of the children of Returns and Allowances - multiply by 20%

Remaining months - Sales Synergies is the sum of the children of Returns and Allowances plus the prior period's Sales Synergies. Multiply the entire result by 10%.

CELL Mode

1.	FIX ("FCCS_Entity Input", "Entity Currency")
2.	FIX ("Sales Synergies", "FCCS_No Intercompany", "FCCS_Managed Data", "No Product", "FCCS_Local GAAP")
3.	"FCCS_Mvmts_NetIncome" (
4.	IF (@ISMBR("Jan"))
5.	@SUM(@Children("Returns and Allowances"));
6.	ELSEIF (@ISMBR("Feb"))
7.	@SUM(@Children("Returns and Allowances")) * 0.2;
8.	ELSE
9.	(@SUM(@Children("Returns and Allowances")) + @PRIOR("Sales Synergies")) * 0.1;
10.	ENDIF
11.	)
12.	ENDFIX
13.	ENDFIX

Block Mode

1.	FIX ("FCCS_Entity Input", "Entity Currency")
2.	FIX ("Sales Synergies", "FCCS_No Intercompany", "FCCS_Managed Data", "No Product", "FCCS_Local GAAP")
3.	"FCCS_Mvmts_NetIncome" (@CALCMODE(BLOCK);
4.	IF (@ISMBR("Jan"))
5.	@SUM(@Children("Returns and Allowances"));
6.	ELSEIF (@ISMBR("Feb"))
7.	@SUM(@Children("Returns and Allowances")) * 0.2;
8.	ELSE
9.	(@SUM(@Children("Returns and Allowances")) + @PRIOR("Sales Synergies")) * 0.1;
10.	ENDIF
11.	)
12.	ENDFIX
13.	ENDFIX

Customer A Use Case

Reclassify Managed Data loaded from FDMEE for Income Statement accounts, to a different calculated Data Source member, based on journal adjustments
Performance was slow: 180 minutes for an entire year

Customer A - Script Example

Customer A - Script Improvements

Use @REMOVE to remove Account instead of using @ISMBR check on Account Dense dimension
BottomUp processing
Use Boolean @ISLEV instead of @LEV and @CURRMEMBER
Performance improved by 90%

Customer B Use Case

Objective - move the data from some Source entities to a Target entity
Data was not being calculated
Performance was slow - 3.5 hours

Customer B - Script Example

Customer B - Script Improvements

Use Copy to create the target block
Calculation remains TopDown
Calculation performed only on one target Custom dimension member
Use @LIKE to make the script generic
Timing reduced from 3.5 hours to a few minutes

Customer C Use Case

Reclassify movements based on FCCS_Closing_Balance_Input entered through the user interface
Performance was slow - 15 minutes

Customer C - Script Example continued

Customer C - Script Improvements

Remove restricted members from the FIX
BottomUp processing
Check for edge cases
Check for common cases first
Performance improved by 40%

Customer D Use Case

Reclassify data pulled from Hyperion Financial Management, data source ML_HFM, and store it at ML_HFM_Calc data source member
Performance was slow –24 hours for a single period
Data was not tying out, as blocks were not being created as expected

Customer D - Script Example

Customer D - Script Improvements

Use @REMOVE to remove Account instead of using @ISMBR check on Account Dense dimension
BottomUp processing
Use Boolean @ISLEV instead of @LEV and @CURRMEMBER
Performance improved by 90%

Customer E Use Case

The Consolidation method changed in the current period, wanted to remove all the cumulative CTA and elimination treatments of the earlier periods
Performance was slow - 90 minutes

Customer E - Script Improvements

Use Data_Input in the target to avoid the validation error about writing to FCCS_Intercompany_Eliminations
Use BottomUp on traverse ICP members which had Closing Balance Input
Timing reduced from 90 minutes to 11 minutes

Summary of Best Practices

BottomUp processing
Use @REMOVE to remove account instead of using @ISMBR check on Account dense dimension
Use Boolean @ISLEV instead of @LEV and @CURRMBR
Remove restricted members from the FIX
Use Copy to create the target block if anchor approach does not work
Calculation performed only on one target Custom dimension member
Use @LIKE to make the script generic
Avoid Auto Block creation
Check for edge cases
Check for common cases first

Best Practices for Performance

Multiple Passes to Essbase

Every time the FIX statement is used in a rule, each FIX will trigger a separate pass to the database. It would be best for performance reasons to avoid multiple passes to Essbase by not including too many separate FIX statements.

Example - Multiple Passes to Essbase

FIX("Entity Currency", "FCCS_Entity Input", …..)
	FIX("FCCS_Data Input", … )
		//Calculations;
	ENDFIX
	
	FIX("FCCS_Other Data", … )
		//Calculations;
	ENDFIX

ENDFIX

Example: Suggested changes to avoid multiple passes using IF...ENDIF

FIX("Entity Currency", ...)
	FIX( @List("FCCS_Data Input", "FCCS_Other Data"), … )
		"FCCS_Entity Input"( @CALCMODE(BOTTOMUP);
			IF(@ISMBR("FCCS_Data Input")
				//Calculations for "FCCS_Data Input";
			ELSE			
				//Calculations "FCCS_Other Data";
			ENDIF	
		)
	ENDFIX
ENDFIX

Example: Suggested changes to avoid multiple passes using Member Blocks

FIX("Entity Currency", ...)
	FIX( @List("FCCS_Data Input", "FCCS_Other Data"), … )
		"FCCS_Entity Input"( @CALCMODE(BOTTOMUP);
			IF(@ISMBR("FCCS_Data Input")
				//Calculations for "FCCS_Data Input";
			ELSE			
				//Calculations "FCCS_Other Data";
			ENDIF	
		)
	ENDFIX
ENDFIX

Example: Multiple separate nested FIX statements which cause multiple passes to Essbase

FIX("FCCS_Elimination")
 	FIX("No Movement")
		Fix(@Relative("ICP_Category",0))
		"Custom_Elimination" (
			"InterSales"="Other_InterAcct"->"FCCS_Intercompany Eliminations";
		)
		ENDFIX  /*Intercompany*/
	ENDFIX  /*Movement*/
 ENDFIX  /*Consolidation*/

Example: Rewrite to avoid multiple passes

FIX ("FCCS_Elimination",@Relative("ICP_Category A",0), "No Movement")
	"Custom_Elimination" ( @CALCMODE(BOTTOMUP);
		"640102" = "WA_Intercompany Account"->"FCCS_Intercompany Eliminations";
	)
ENDFIX

Writing to Restricted Members

In this example, suppose you want to reclassify "FCCS_Intercompany Eliminations" > "FCCS_Eliminations" > "Mvmts_NewBusiness" to "Data Input" > "FCCS_Eliminations" > "Mvmts Reclass."

However, since "FCCS_Intercompany Eliminations" is a restricted member for the Data Source dimension, if you try to use a FIX on this member, the system returns an error.

You can try to write the following statements, which force the system to use Top Down processing.

Example: Working with restricted members using Top Down processing

FIX("Data Input", … ) 
	"FCCS_Elimination" (
		 "Mvmts_Reclass" = -1 * "FCCS_Intercompany Eliminations"->"Mvmts_NewBusiness" ; 
	)		
ENDFIX

Example: Rewrite the statements using BottomUp processing

FIX("FCCS_IntercomanyEliminations", "Mvmts_NewBusiness", … )
	"FCCS_Elimination" ( @CALCMODE(BOTTOMUP);
		 "Mvmts_Reclass"->"Data Input" = -1 * "Mvmts_NewBusiness" ; 
	)		
ENDFIX

Note that in this example, you have a FIX on "FCCS_Intercompany Eliminations", but override it with "Data Input" in the member block, and the system will not return an error during validation.

Entering Data in Closing Balance Input and Calculating Movement Based on UDAs

In this example, suppose you want to move Closing Balance Input into a specific Movement member. You could write a custom calculation with these requirements:

Fix on sparse dimension member combinations together for Bottom-Up processing. Bottom-Up processing is related to blocks, and sparse dimensions define a block.
User-defined attributes (UDAs) are best processed together with a FIX on the UDA accounts, to perform the same calculation.
The example below assumes that all specified UDAs are defined on ASSET / LIABILITY / EQUITY Account Types.
FIX on level zero Account dimension members relative to FCCS_Net Income
Use a Boolean function rather than calculate member's level using @LEV for performance improvement
Use Boolean function @ISDESC to check if the member is a descendant. It will always be a leaf member.

Example: Entering Data in Closing Balance Input and Calculating Movement Based on UDAs

Configurable Calculation example for Closing Balance Input using UDAs

Best Way to Use IF Condition

Below is a common example when you write the conditional statements using IF. In this example, you want to do a specific process in January, but do something different for the other months. If the calculation is written as below, the system will go through the check 12 times for all periods other than January, since it always checks for January first, and then branch off to the ELSE clause.

Example: IF statement

FIX ("Entity Currency", "FCCS_Entity Input" … )
	"Mvmt_Increase01" ( @CALCMODE(BOTTOMUP);
	 	IF(@ISMBR("Jan"))
        			"FCCS_ClosingBalance_Input" - @PRIOR("FCCS_ClosingBalance_Input"-> 			"Dec", 1, @RELATIVE("Years", 0));
		ELSE
	        		"FCCS_ClosingBalance_Input" - "FCCS_TotalOpeningBalance";
		ENDIF
	)
ENDFIX

Example: Rewrite using NOT IF

You can rewrite the IF statement so that 11 out of the 12 periods will be executed with the IF clause and then drop out of the conditional branch. Only January will be executed in the ELSE clause once.

FIX ("Entity Currency", "FCCS_Entity Input", …) 	
        "FCCS_ClosingBalance_Input"(@CALCMODE(BOTTOMUP);
 	IF (NOT @ISMBR("Jan"))
		"Mvmt_Increase01" = "FCCS_ClosingBalance_Input" - "FCCS_TotalOpeningBalance";
	ELSE
		IF(NOT @ISMBR(@MEMBERAT(@CHILDREN("Years"),1)))
	      "Mvmt_Increase01" = "FCCS_ClosingBalance_Input" - "FCCS_ClosingBalance_Input"->"Dec"->	      @MEMBER(@PREVSIBLING(@CURRMBR("Years")));
		  ENDIF;
	 ENDIF;
	)
ENDFIX

Using Top Custom Member System Calculation Option with Extended Dimensionality

For user-defined Custom dimensions, Service Administrators can select to process System Calculations using the Top Member of the Custom dimension, instead of all level 0 members, for faster performance. You can select specific Custom dimensions for which the option would apply. See System Calculations.

If you are using an Extended Dimensionality environment, to make sure using the Custom Top Member does not slow down performance, you can create an empty block at "NoCustomX" at the beginning of the consolidation based on the Entity Input and Entity Currency data, and use that block to perform all the calculations. For example, if you have 1000 Custom members in the Product Custom dimension, you can create one block @"No Product", FIX on "No Product", and use Bottom-Up processing. The system then does not need to loop through all 1000 members of the Product dimension, and you can use "Total Product" for the total value to improve overall performance.

The following example shows a sample Calc Script:

Configurable Calculation example for Custom Top Member

Calculating FCCS_10 Member Blocks using Bottom-Up Processing

Use @CALCMODE(BOTTOMUP) and combine member block calculations.
Combine calculations in multiple FIX...ENDFIX into a single FIX...ENDFIX, if FIX members are the same across calculations.

Avoid FIX within FIX, if it is just a single calculation.

The following examples show running the calculation using Top-Down processing, and then a modified example using Bottom-Up processing to improve query processing on the right-hand side.

Example: Running FCCS_20 C1_Validation Using Top-Down Processing
Configurable Calculation example C1 Top-Down

Example: Running FCCS_20 C1_Validation Using Bottom-Up Processing

Configurable Calculation example C1 Bottom-Up

Calculation Dependency

You should avoid dependencies between entities when calculations are done in Configurable Calculations (insertion points) and On-Demand Rules. If you try to reference Entity A's value in the calculation, and if Entity A has not yet been calculated, Entity A would have no value.

For example, if you try to reclassify data from "Entity A" > "ICP_B" > "Entity Currency" (source) to "Entity B" > "ICP_A" > "Entity Currency" (destination), data in Entity A (source) may not be available, since it may not have been calculated, if both entities Entity A and Entity B are being calculated in parallel.

In such cases, the reclassification should be attempted by first calculating Entity A and then dependent Entity B.