FCCS – Intro to Writing Configurable Calculations

In Financial Consolidation and Close Cloud Services (FCCS) business rules can be defined in a few places. This post will primarily focus on Configurable Calculations. These are seeded calculation rules inserted at pre-defined points along the consolidation path and uses Calculation Manager to write rules. FCC sits on top of Essbase and uses Essbase Calc Script language for business rules.

Basics of Writing Rules in FCCS

Semi-colon

Each equation that performs an action in the data base must end with “;”
- “Sales” = “Unit Price” * “Units Sold”;

Double Quotes

Best practice is always to use double quotes to keep formatting consistent and reduce number of errors. Double quotes are required for member names with
- Spaces – “Opening Inventory”
- Special Characters – “2200-20”
- Numbers followed by letters – “3MonthMovingAverage”

Cross-dimensional Operator

Used to retrieve or write to a combination of members
Created by hyphen and greater than sign ( -> ) with no space in-between
- “Mgmt Fees” = ( “Sales” -> “FCCS_Intercompany Top”) * 0.08;

Comments

start with /* and end with */
comments can span multiple lines
- one line
  - /* comments */
- multiple line
  - /*comments, comments */

Essbase Functions and Operators

Essbase has a list of functions and operators to assist in writing complex calculation scripts
- Not all Essbase functions are supported – check the FCC admin guide for list of supported functions
Essbase function start with @
- /* Checks if current entity is a member of North America */ @ISMBR(@DESCENDANTS(“North America”)
Essbase has mathematical, conditional and logical operators
- List of mathematical operators:
  - Click here for list of mathematical operators
- List of Conditional and Logical operators:
  - Click here for list of conditional and logical operators

FIX & ENDFIX

All configurable calculations are written within FIX & ENDFIX statement, often called the sandwich command
Helps to focus calculation and reduce the number of blocks a calculation is run against

Limits the number of members pulled into memory
Only blocks that meet the FIX conditions are processed

Can fix on multiple dimensions
i.e.
- FIX (“Entity Currency”)
  - FIX (“Entity Input”)
    - “Acct1” = “Actual”;
  - ENDFIX
- ENDFIX

Member Blocks and Conditional Statements

- To use conditional statements, the conditions need to be in a member block associated with a database member
- The conditional statement needs to be enclosed in brackets ()
- Best practice is to fix on sparse and apply conditional statements to dense (Account) this will restrict multiple passes through the same block
- For member block calculation to run faster its recommended to use BOTTOMUP calculation
  - BOTTOMUP (faster) – Determine which existing member blocks need to be calculated and calculates only the existing blocks that require calculation. For simple calculations this is the default.
  - TOPDOWN (slower) – Essbase execute the calculation on all potential data blocks. For complex calculations this is the default
    - Complex Calculations:
      - Uses cross- dim operators
      - Uses range, relationship or financial functions
  - CELL (slower) – Essbase perform the calculation sequentially by cell. Factors in dependencies.
    - Essbase uses cell mode for certain functions that have dependencies especially time-based functions.
  - BLOCK (faster) – Essbase groups the cells in a block and calculates each group simultaneously. Need to ensure calculation is accurate as all required data dependencies may not have been factored into the calculation.
    - This is the default mode
- The calculation mode can be overwritten by using the function @CALCMODE. Bottom-up and Top-down are mutually exclusive and Cell and Block are mutually exclusive.

- /* Fix applied to Product */
- FIX (“Product1”)
- /*To use conditional statements, the below member block is associated to the Data Source member FCCS_Data_Input */
- “FCCS_Data Input”(
- /* Change calculation mode to bottom up */
  - @CALCMODE(BOTTOMUP);
- /*Conditions applied to dense dimension */
  - - IF (@ISMBR(“Acct_Prior”))
      - “FCCS_Data Input” = “Actual”;
    - ELSEIF (@ISMBR(“Acct_Capital”))
      - “FCCS_Data Input” = “Budget”;
    - ELSE
      - “FCCS_Data Input” = “Forecast”;
    - ENDIF
- /* Close off conditions associated with member FCCS_Data Input” */
  - - )
- ENDFIX

Single vs Double Equal Sign

A single equal sign is to set the left equation to equal the right equation
- “Sales”->”Computers” = 500;
A double equation sign is to test if something is True and return True if right equation equals left equation and FALSE if its not true
- IF (“CopySalesFlag”->“FCCS_No Intercompany”->“FCCS_No Movement”->”FCCS_Data Input” ==1)

Clearing Data Using Rules

To clear data intersection you can use the CLEARDATA command:
- CLEARDATA “Sales”->“Computers”;
- CLEARDATA command cannot be used inside IF statements
You can clear data by setting the data intersection using #Missing
- “Sales”->“Computers” = #Missing;
- Can be used inside IF statements

Restrictions in FCCS

The below dimensions cannot be included in FIX statements or left-hand side of the equation. However, they can be defined in IF statements and there are no restrictions from using them on the right-hand side of the equation.

- Year
- Period
- Scenario
- Entity
- View

FCCS has restricted members that must be excluded from calculations otherwise the rules will fail. These members are restricted because FCC has its own calculations for these members. Full list of restricted members can be found in the FCC admin guide.
For example, If calculations are to be executed at all level 0 members (base) of the Account dimension member “FCCS_Balance Sheet”, the restricted members under “FCCS_Balance Sheet” have to be removed. The below FIX focuses on base of “FCCS_Balace Sheet” and excludes the restricted members, “FCCS_CTA”, “FCCS_CICTA” and “FCCS_Balance”:

FIX (@LIST(@RELATIVE(“FCCS_Balance Sheet”,0 ) AND (@REMOVE (@RELATIVE (“FCCS_Balance Sheet”,0 ), @LIST(“FCCS_CICTA”,“FCCS_CTA”,“FCCS_Balance”)))))
/* Calcs*/
ENDFIX

When no members of a dimension are specified in the rules, FCCS will run the rule on all members of the dimension.

Two Pass Calculations

Members that are calculated on a second pass through the Essbase outline are called two pass calculations
Used when value is derived from a calculated or parent value
Should only be used on the Accounts dimension
Two pass calculation is a property you can define as “Yes” or “No” on a member in the metadata

Missing Blocks

Configurable calculation will not run if a block doesn’t exist for the point of view being calculated and this is a common issue for calculations not to work.