Best Practices for Calculations in Extended Dimensionality Applications

Extended Dimensionality Applications with Account as the Dense Dimension

If you are using an Extended Dimensionality application with Account as the Dense dimension, you can use these scripting techniques. Note the following points pertaining to these applications:

Dimension order: The Movement dimension is the first Sparse dimension in the dimension order, known as a bitmap dimension.
Intercompany dimension’s parent members are Dynamic Calc.
All Custom dimensions must have their parent members set to Dynamic Calc.
The idea is to have minimal upper level block count, and achieve dynamic aggregations during spreadsheet operations.
Points 2 and 3 above play a significant role while writing customized calculations, apart from the need to rewrite existing calculations, if Top/Parent members are used in existing calculations.

Extended Dimensions Account and Movement

SET HYBRIDBSOINCALCSCRIPT FULL / NONE

HYBRIDBSOINCALCSCRIPT is required when the right-hand side of the expression has top members, for example:

"CA3_010" = "FCCS_Mvmts_Subtotal" -> "Total Custom1" -> "No Custom2";

In this case, you need to disable HYBRIDBSOINCALCSCRIPT for the offending line and then enable it again after the execution of the offending line, for example:

SET HYBRIDBSOINCALCSCRIPT NONE
"CA3_010" = "FCCS_Mvmts_Subtotal" -> "Total Custom1" -> "No Custom2"; SET HYBRIDBSOINCALCSCRIPT FULL;

Remember, if required, this is to be done only for configurable calculation (insertion point) rules.

SET HYBRIDBSOINCALCSCRIPT FULL / NONE

Sometimes, when there is only one top member at the right-hand side, HYBRIDBSOINCALCSCRIPT slows down the expression, for example:

"CA3_010" = "FCCS_Mvmts_Subtotal" -> "Total Custom1" -> "No Custom2";

In this case, you need to disable HYBRIDBSOINCALCSCRIPT for the offending line and then enable it again after the execution of the offending line, for example:

SET HYBRIDBSOINCALCSCRIPT NONE
"CA3_010" = "FCCS_Mvmts_Subtotal" -> "Total Custom1" -> "No Custom2"; SET HYBRIDBSOINCALCSCRIPT FULL;

Remember, if required, this is to be done only for configurable calculation (insertion point) rules.

Successive Addition Technique

"Target_Account_Stored" -> "Mvmt_None" = "FCCS_Mvmts_Subtotal" -> "Source_Account_DynamicCalc" -> "Total Custom1" -> "Total Custom2";

In this example, the intention is to store the result of the dynamic calculation on the right side, into a stored Dense member. This calculation is not performed if executed with SET HYBRIDBSOINCALCSCRIPT FULL. In these situations, based on the consolidation operators of the level zero members of the Custom dimension set as Addition, you can use a technique known as successive addition. Since the Account dimension is Sparse, you FIX on level zero members under the Dynamic Calc source account. Additionally, remember to turn off Hybrid mode.

1.	SET HYBRIDBSOINCALCSCRIPT NONE; /* Turn OFF Hybrid mode */
2.	FIX( @RELATIVE( "Total Custom1", 0 ), @RELATIVE( "Total Custom2", 0 ), <other dimension members of FIX> )
3.	"FCCS_Mvmts_Subtotal" ( @CALCMODE( BOTTOMUP );
4.	"Target_Account_Stored" -> "Mvmt_None" = "Target_Account_Stored" -> "Mvmt_None" + "Source_Account_DynamicCalc";
5.	)
6.	ENDFIX

Consider the following case in which a few level zero Custom1 members have the consolidation operator set as follows: Custom1_A and Custom1_B members have the consolidation operator set as Subtract.

An important consideration when using this technique: When the business rule is executed multiple times, it is quite possible that the target account will have an accumulated value. This results in incorrect numbers. Therefore, consider initializing the target value to #Missing, in a BOTTOMUP process, because the script would have been executed at least once.

Avoid the following constructs:

CALC DIM, CALC ALL, AGG, and any other assignment-free expressions that calculate a sub-tree. A best practice is to limit use of CALC DIM and AGG to dimensions where no stored members are dependent on dynamic members.
Do not use CREATENONMISSINGBLOCK or CREATEBLOCKONEQ in calculation scripts.
Avoid using these functions inside insertion points:
- @ALLOCATE
- @CREATEBLOCK
- @IRREX
- @MDALLOCATE
- @MDSHIFT
- @MOVSUMX
- @PTD
- @SANCESTVAL
- @STDEV
- @STDEVP
- @STDEVRANGE
- @SYD
- @TREND

Using a Sparse Member Block and BOTTOMUP

Non-Extended Dimension Application Scenario

1.FIX ("FCCS_EntityInputFCCS_EntityInputFCCS_EntityInputFCCS_EntityInput FCCS_EntityInput FCCS_EntityInputFCCS_EntityInputFCCS_EntityInputFCCS_EntityInput", "Entity "Entity Currency" Currency" Currency" Currency" Currency" Currency" Currency" )
2.FIX ("Inventory_StockInventory_StockInventory_StockInventory_Stock Inventory_StockInventory_Stock Inventory_Stock Inventory_Stock ", "FCCS_Mvmts_NetIncomeFCCS_Mvmts_NetIncomeFCCS_Mvmts_NetIncomeFCCS_Mvmts_NetIncome FCCS_Mvmts_NetIncome FCCS_Mvmts_NetIncome FCCS_Mvmts_NetIncomeFCCS_Mvmts_NetIncomeFCCS_Mvmts_NetIncome ", "FCCS_NoFCCS_NoFCCS_NoFCCS_No FCCS_NoIntercompany", Intercompany", Intercompany", Intercompany", Intercompany", Intercompany", Intercompany", Intercompany", Intercompany", Intercompany", "No "No Product", Product", Product", Product", Product", Product", Product", "FCCS_LocalFCCS_LocalFCCS_LocalFCCS_Local FCCS_Local GAAP" GAAP" GAAP" )
3."FCCS_ManagedFCCS_ManagedFCCS_ManagedFCCS_Managed FCCS_Managed FCCS_Managed Data" Data" Data" (
4."FCCS_OtherFCCS_OtherFCCS_OtherFCCS_Other FCCS_Other FCCS_Other Data" Data" Data" ="Warehouse_StockWarehouse_Stock Warehouse_Stock Warehouse_Stock Warehouse_Stock Warehouse_Stock " +"Showroom_Stock Showroom_StockShowroom_Stock Showroom_StockShowroom_Stock Showroom_Stock ";
5.)
6.ENDFIX
7.ENDFIX ENDFIX

Extended Dimension Application Scenario

8.FIX ("FCCS_EntityFCCS_EntityFCCS_EntityFCCS_Entity FCCS_Entity Input", Input", Input", Input", Input", Input", Input", "Entity "Entity Currency" Currency" Currency" Currency" Currency" Currency" Currency" )
9.FIX ("Inventory_StockInventory_StockInventory_StockInventory_Stock Inventory_StockInventory_Stock Inventory_Stock Inventory_Stock ", "FCCS_ManagedFCCS_Managed FCCS_Managed FCCS_ManagedFCCS_ManagedData" Data" Data" ,"FCCS_NoFCCS_NoFCCS_NoFCCS_No FCCS_NoIntercompany", Intercompany", Intercompany", Intercompany", Intercompany", Intercompany", Intercompany", Intercompany", Intercompany", Intercompany", "No "No Product", Product", Product", Product", Product", Product", Product", "FCCS_LocalFCCS_LocalFCCS_LocalFCCS_Local FCCS_Local GAAP" GAAP" GAAP" )
10 ."FCCS_Mvmts_NetIncomeFCCS_Mvmts_NetIncome FCCS_Mvmts_NetIncome FCCS_Mvmts_NetIncomeFCCS_Mvmts_NetIncomeFCCS_Mvmts_NetIncome FCCS_Mvmts_NetIncomeFCCS_Mvmts_NetIncome FCCS_Mvmts_NetIncome " (
11 ."FCCS_OtherFCCS_OtherFCCS_OtherFCCS_Other FCCS_Other FCCS_Other Data" Data" Data" ="Warehouse_StockWarehouse_Stock Warehouse_Stock Warehouse_Stock Warehouse_Stock Warehouse_Stock " +"Showroom_Stock Showroom_StockShowroom_Stock Showroom_StockShowroom_Stock Showroom_StockShowroom_Stock ";
12 .)
13 .ENDFIX ENDFIX
14 .ENDFIX

The Movement member "FCCS_Mvmts_NetIncome" is used as an anchor. The Movement dimension is considered a bitmap dimension. It is the first sparse dimension after the Account dense dimension. As much as possible, calculations involving a single Movement dimension member should use the Movement member as member block (known as an anchor).

Calculation Involving Top/Parent Members as Source

Non-Extended Dimension Application Source

1.	FIX ("Entity Currency", "FCCS_Entity Input")
2.	FIX("FCCS_Managed Data", "FCCS_Intercompany Top",
"Total Custom1", "Total Custom2")
3.	"FCCS_Mvmts_NetIncome"(
4.	IF(@ISLEV("Entity", 0))
5.	"Account 3" = "Account 1" + "Account 2";
6.	)
7.	ENDFIX
8.	ENDFIX

Extended Dimension Application Scenario

1.	FIX ("Entity Currency", "FCCS_Entity Input")
2.	FIX("Account 3" ,"FCCS_Managed Data", "FCCS_No Intercompany", "No Custom1", "No Custom2")

3.	"FCCS_Mvmts_NetIncome"(
4.	IF(@ISLEV("Entity", 0))
5.	"FCCS_Intercompany Top"->"Total Custom1"-
>"Total Custom2"->"Account 1" + "FCCS_Intercompany Top"-
>"Total Custom1"->"Total Custom2"->"Account 2"; 6.	)
7.	ENDFIX
8.	ENDFIX

Top members are Dynamic Calc, and cannot be used in FIX expressions, so use them on the right hand side of calculation and redirect result to "No <member>".
A single dense dimension member that stores the result of calculation, is to be placed in FIX.

Best Practices for Calculations in Extended Dimensionality Applications

Use BottomUp processing only when the right hand side calculation does not involve Top/Parent member(s).
Use @Remove to remove the dense member instead of using @ISMBR check on 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 should be performed only on one target Custom dimension member.
Use @LIKE to make the script generic.
Check for edge cases.
Check for common cases first
When calculations write to a single Movement dimension member, use the Movement member as a member block, known as an anchor.
When calculations write to a single Account dimension member where Account is the Dense dimension, move Account member to FIX. In an application where Period and Movement are the Dense members, move Account member to the left-hand side of the calculation.

Extended Dimension applications use a Hybrid aggregation mode. The SET HYBRIDBSOINCALCSCRIPT construct is available as part of Calculation Manager and controls whether cubes in the application use hybrid aggregation mode in calculation scripts when stored members depend on dynamic members.

For a list of Essbase Functions supported in Extended Dimensionality applications, see "Functions Supported in Hybrid Aggregation Mode" in Oracle Essbase Technical Reference.

Customer A Use Case

In this use case, the original calculations included these issues:

Manual Cash Flow calculations
Performance was slow when Cash Flow calculations were inserted in insertion points versus without the calculations. One entity took two minues to consolidate, versus 40 seconds without the calculation.
The calculation could not use the seeded Cash Flow due to their statutory practices.

Original Calculations

1.	FIX("Entity Currency", "FCCS_Entity Input")
2.	/* Account CA3_010 - CET1 Capital ratio */
3.	/* Account CA3_020 - Surplus(+)/Deficit(-) of CET1 capital */
4.	FIX ("FCCS_No Movement", "No Custom2", "No Custom3", "No Custom4", "FCCS_Data Input", "FCCS_No Intercompany")
5.	"Submitted" (
6.	"CA3_010" = ("FCCS_Mvmts_Subtotal"->"Total Custom1"->"Total Custom2"->"Total Custom3"-> "FCCS_Intercompany Top"->"FCCS_Total Data Source"->"FCCS_YTD"->"CA1_020" / "FCCS_Intercompany	Top"->"FCCS_Total Data Source"->"FCCS_YTD"-> "CA2_010") - @Prior("CA3_010"->"FCCS_YTD");

7.	"CA3_020" = ("FCCS_Mvmts_Subtotal"->"Total Custom1"->"Total Custom2"->"Total Custom3"-> "FCCS_Intercompany Top"->"FCCS_Total Data Source"->"FCCS_YTD"->"CA1_020" - ("FCCS_Intercompany Top"->"FCCS_Total Data Source"->"FCCS_YTD"->
"CA2_010" * 0.045)) - @Prior("CA3_020"->"FCCS_YTD");
8.	ENDFIX
9.	ENDFIX

The calculation does not perform well due to the following reasons:

The right-hand side calculations are basically queries to Essbase, because most parent members of the respective dimensions are Dynamic Calc.
In the above case, two simultaneous queries are being launched, and only when results are fetched, will the actual calculation initiate., which leads to a slow formula cache.
The above script executes for every entity, during a consolidation, irrespective of entity level.

Revised Calculation

The following example shows a revised calculation.

1.	SET HYBRIDBSOINCALCSCRIPT NONE; /*Turn OFF HYBRID BSO Mode */
2.	FIX("Entity Currency", "FCCS_Entity Input")
3.	/* Account CA3_010 - CET1 Capital ratio */
4.	/* Account CA3_020 - Surplus(+)/Deficit(-) of CET1 capital */
5.	/* First perform the natural aggregation in BOTTOMUP mode */
6.	FIX (@RELATIVE("Total Custom1", 0), @RELATIVE("Total Custom2", 0), @RELATIVE("Total Custom3", 0), "No Custom4",
@RELATIVE("FCCS_Total Data Source", 0), @RELATIVE("FCCS_Intercompany Top", 0))
7.	"FCCS_Mvmts_Subtotal"(@CALCMODE(BOTTOMUP);
8.	IF (@ISLEV("ENTITY", 0))
9.	"FCCS_No Movement"->"FCCS_No Intercompany"->"FCCS_Data Input"->"Submitted"->"CA3_010" = "FCCS_No Movement"->
"FCCS_No Intercompany"->"FCCS_Data Input"->"Submitted"->"CA3_010" + ("FCCS_YTD"->"CA1_020"/"FCCS_YTD"->"CA2_010");

10.	"FCCS_No Movement"->"FCCS_No Intercompany"->"FCCS_Data Input"->"Submitted"->"CA3_020" = "FCCS_No Movement"->
"FCCS_No Intercompany"->"FCCS_Data Input"->"Submitted"->"CA3_020" +
("FCCS_YTD"->"CA1_020" - ("FCCS_YTD"->"CA2_010" * 0.045));
11.	ENDIF;
12.	)
13.	ENDFIX
14.	/* Now leverage usage of @SHIFMINUS instead of subtraction and @PRIOR */
15.	FIX ("No Custom2", "No Custom3", "No Custom4", "FCCS_Data Input", "FCCS_No Intercompany", "Submitted", "CA3_010")
16.	"FCCS_No Movement"(@CALCMODE(BOTTOMUP);
17.	IF (@ISLEV("ENTITY", 0))
18.	@SHIFTMINUS("CA3_010", "FCCS_YTD"->"CA3_010", -1);
19.	ENDIF;
20.	)
21.	ENDFIX
22.	FIX ("No Custom2", "No Custom3", "No Custom4", "FCCS_Data Input", "FCCS_No Intercompany", "Submitted", "CA3_020")
23.	"FCCS_No Movement"(@CALCMODE(BOTTOMUP);
24.	IF (@ISLEV("ENTITY", 0))
25.	@SHIFTMINUS("CA3_020", "FCCS_YTD"->"CA3_020", -1);
26.	ENDIF;
27.	)
28.	ENDFIX
29.	ENDFIX

Script Improvements

Each query calculation is segregated as a separate FIX, and destination dense member is moved to FIX
Script executes only on level zero entity, during a consolidation
Movement member is used as anchor
Performance improved from two minutes to 30 seconds per entity

Extended Dimensionality Applications with Account as a Sparse Dimension and Period and Movement as Dense Dimensions

This section outlines scripting techniques for Extended Dimensionality applications that use Account as a Sparse dimension. When you select to create an application or convert an existing one to one with Period and Movement as Dense dimensions and Account as a Sparse dimension, keep these points in mind:

Period and Movement are Dense dimensions.
Account dimension is Sparse.
Seeded Parent Movement dimension members are Dynamic Calc.
Intercompany dimension's Parent members are Dynamic Calc.
All Custom dimension Parent members must be Dynamic Calc.
The idea is to have minimal upper-level block count, and achieve dynamic aggregations during Spreadsheet operations.
Points 2 and 3 play a significant role while writing customized calculations, apart from the need to rewrite existing calculations, if Parent members are used in existing calculations.

Customer A Use Case

Calculation of Data Source member IC_Inventory_Alloc, based on source data at FCCS_Managed Data

Before optimization

Customer A - Original Calculation Script

a. Runs TOPDOWN
b. Uses Dynamic Calc Top members on the right side, thus causing slowness in retrieval of data
c. Calculation unnecessarily multiplies and divides using same intersection

Customer A Use Case - Modified Calculation Script

The modified calculation script uses the successive addition technique and BOTTOMUP processing.

a. Calculation runs in BSO mode - SET HYBRIDBSOINCALCSCRIPT NONE
b. @CREATEBLOCK is used to create target IC_Inventory_Alloc blocks base on source FCCS_Managed Data blocks, BOTTOMUP
c. Instead of Dynamic Calc Top members, stored members are used on the right side

Customer B Use Case

This Surplus account calculation had to be rewritten after migration to Account as Sparse model. The Data Storage property of the seeded parent members in the Movement dimension is Dynamic Calc. This led to some challenges as consolidation timings were compromised. The calculation had to be halted after migration.

Full year timing after adopting best practices:

Customer B Use Case - Original Calculation

In the original FCCS_20 calculation:

a. Movement dimension members were used as anchor. The Movement member "FCCS_Mvmts_Total" is Dynamic Calc, and could not be used as anchor.
b. Account dimension is Sparse, therefore the top members "FCCS_Total Liabilities and Equity" and "FCCS_Total Assets" on the right side resulted in slower calculation times.

Customer B Use Case - Modified Calculation

This example shows the modified FCCS_20 calculation.

a. Consolidation member is used as anchor.
b. Successive addition technique is used with FIX on level zero members of "FCCS_Total Liabilities and Equity" and "FCCS_Total Assets", first adding all liabilities and equities and then subtracting all assets

Customer B Use Case - Modified calculation