Essbase Calc Script

Calculation Script Overview

Calculation Script is group of command lines; those are use for calculation. Calculation scripts are text files that define how to calculate data in the database. In a block storage database, they represent the second method of calculations after the basic outline calculations. Calculation scripts perform calculations different from the consolidations and mathematical operations that are defined in the database outline.

For example, you can calculate part of a database or copy values between members.

Because calculation scripts perform specific mathematical operations on member, they are typically associated with a particular database. You can, however, define a calculation script for use with multiple databases.

Calculation scripts files have a “*.csc” extension.

The calculation processes of a single database can use any number of calculation scripts. Oracle Hyperion Calculation Manager is a new tool which centralizes the development of calculations for HFM, Essbase, and Planning. 

Formula

The Measures dimension uses the following formulas:

• Margin = Sales – COGS
• Total Expenses = Marketing + Payroll + Miscellaneous
• Profit = Margin – Total Expenses
• Profit % = Profit % Sales
• Margin % = Margin % Sales
• Profit per Ounce = Profit / @ATTRIBUTEVAL(@NAME(Ounces))

Essbase uses Essbase - Consolidation Operator to calculate the Margin, Total Expenses, and Profit members:

• The Margin% formula uses a % operator, which means “express Margin as a percentage of Sales”.
• The Profit% formula uses the same % operator.
• The Profit per Ounce formula uses a division operator (/) and a function (@ATTRIBUTEVAL) to calculate profitability by ounce for products sized in ounces.

  

Calculation Script

1.                 Unary Operator

1. The first and most efficient manner of calculating in Essbase in the outline in through the use of unary operators.
2. Unary operator calculations are faster than formulas in the outline or in calculation scripts.
3. Unary calculation construction provider’s visibility for drilling down in the accounts hierarchy, So that you can see where the number originated. Formulas can obscure visibility into the calculations.

                       

Margin = Sales – Expenses

2.                 Member Formulas à Member formula is directly associate formulas with members in the outline. Member formulas execute when you execute complete outline script.

2.              Calculation Script --> Calculation script calculation is the anther method of calculation. Using a calculation script, you can choose exactly how to calculate a database

IF(EMP_Salary>12000)

            Income_Tax=EMP_Salary*0.03;

EndIF;

CALC ALL : Calculates and aggregates the entire database based on the database outline.

Example Calc All

CALC DIM : Calculates formulas and aggregations for each member of the specified dimensions.

Example CALC DIM(Accounts);

CALC TWOPASS;

Housekeeping : A range of commands used to copy and clear data or perform other housekeeping functions on specific  sectors of the database (for example, DATACOPY

Bud Version 1 TO Budget Version 2).

SET AGGMISSG OFF;    SET UPDATECALC OFF;

SET CALCPARALLEL 3;   SET CALCTASKDIMS 5;

Roll-Up A calculation script with the CALC ALL or CALC DIM functions on specific dimensions rolls up input data in the outline hierarchy according to the outline’s consolidation operators and formulas on members in the outline. The default calculation script, unless reset to an alternative script, is equivalent to the CALC ALL command.

Member Formulas Data is created or modified for specific members according to formulas associated with the member either in the outline or in a calculation script.

How to write calculation script?

Create a new calculation script file.

File --> New 

 

Select Calculation script from script tab

 New calculation script can be written in scripting area. 

 

Verify an execute script.