Calculation script Part 1

Calculation script Part 1

This is my first post about calculation scripts and am glad to help for beginners who are struggling to understand these concepts. Today we are going to learn the basics about calculation scripts. Many people have confusion to use calculation commands & functions When & Where? This blog is correct place for beginners to learn about calculation scripts. I will update the notes of few important calculation commands & functions on daily or weekly basis. Kindly keep in touch with the blog for coming posts. Let's start with the basics.

1.      What is Calculation script?

Calculation script is an object of application/database. calculate the portion of data in a cube.

Purpose of calculation scripts is to calculate the portion of data in a cube, copying, clearing block, clearing data…etc

Note: Exporting and importing will not be done using Calculation script.

In general calculation can be done in two ways.

a.      Member formula.

b.      Calculation script.

Member formula:

This is also called outline calculation which means that, member value (Any parent member) will update based on member formula or consolidation operator that we have given via outline.

Example of consolidation operator and Dynamic calculation:

In Sample.basic ouline you can see the consolidation operators. For example, Total Expenses member is a parent of Lease,Marketing,Payroll,Misc.

Total Expenses = Lease+Marketing+Payroll+Misc.

                                                           Fig : 1:1 Sample.Basic outline

"Total Expenses" member value will be calculated based on the consolidation operator.

Dynamic calculation will be done while we retrieve the data from Essbase. In Fig 1:1 Under Scenario Dimension “Variance” member contains “dynamic calc” as data storage type and formula is “@VAR(Actual, Budget)”. Now we will take a look how variance will be calculated.

Variance = Actual – Budget

In the above example we have given the formula for a member in Outline.

Calculation script:

Calculation script mainly used for calculating the portion of a cube. For example ,We have load the budget data into cube for level zero members  and now requirement is to consolidate “Budget” values.

This example calculates Budget values and then recalculates the Variance and Variance % members:

/* Calculate all Budget values */

FIX(Budget)

   CALC DIM(Year, Product, Market, Measures);

ENDFIX

Essbase performs the following actions:

1. Fixes on the Budget values.
2. Calculates all Budget values.

The CALC DIM command is used to calculate all the dimensions except for the Scenario dimension, which contains Budget.

3. Calculates the formula applied to Variance in the database outline.
4. Calculates the formula applied to Variance % in the database outline.

 Please write your feed back...Thanks.