Calculation Scripts:- Session 3

In this session we will be implementing the same logic ("Commission on Sales" = 

"Commission %" * "Sales" ) but we will add more complication to our outline, lets add Quarters 

in our Period Dimension and an Entity dimension with few members.

We still have the same hierarchy for Account:

Period hierarchy:

Entity hierarchy:

Here are our Outline Properties:

Lets assume that we want to retrieve data in below format:
.

Here the #Missing in red are the input cells and #Missing in black the palces where we expect values:

Lock and send above data to Essbase, now run our old calculation script MySec.csc. Once the calculation is done retrieve the results.
.
We got the calculated values for Sales and Commission on Sales, but why the value is not 

aggregated for Qtr1, YearTotal and APAC? because we havnt done that, in order to calculate 

the values for Qtr1 and YearTotal we need to write a command which performs the outline 

calculation, like the way we wrote for Sales in our last example. Here we can write:

"Qtr";

"YearTotal";

Adding these two will do the outline calculation for "Qtr" and "YearTotal", Now we will add these 

two statements to our Calculation Script.

Lets Save this file as MyThird.csc, Need less to say the statements with in /* and */ are comments which are ignored when a Calc Script is executed.
Come back to our excel sheet and execute MyThird on this data and retrieve data.

Wow!! we finally got the values for Qtr1 and YearTotal as well But still there are no values at 

APAC. Now you know what needs to be done to get values at APAC, You are right! you need 

to write a member calculation for APAC.

"APAC"; 

If Above will be added to Calc Script you will get the values for APAC. Along with that lets 

understand one more concept, above we wrote the calculation for Qtr1 and YearTotal 

Separately, Many of you will wonder dont we have a function which can calculate YearTotal and all 

the quarters in on go!. The answer is 'yes', we can write IDESCENDANTS("YearTotal" ) to 

calculate the hierarchy below YearTotal. Same can be done for calculating Margin(Which is still 

#Missing) we have an outline hierarchy as per that Margin is the difference of Sale and COGS, 

to calculate Sales along with Margin we can use IDESCENDANTS("Margin" ) Lets give the 

final touch to our Calc Script.

Save, Validate the script and lets re run our MyThird Calc scripts on the same data and retrieve results.

We got the values :)

---

As we discussed in the beginning,we try to implement our logic by outline calculations and if 

thats not feasible then comes the Calculation Scipt in picture. Lets try to implement same thing 

in outline, yeah "Without calculation scripts". To do the same we have two options either we can 

write a member formula for Commission on sales or we can create a shared member of sales 

under Commission on Sales and move our Commission % under Commission on Sales. I will 

be going through second way. Then our new hierarchy will be:

Shared Member: A shared member shares values between member. We have 6 types of data 

storage in Essbase and Planning, Shared is one of them(More on this later). For more 

information on the same have a look at below link and look for "Determining How Members 

Store Data Values"

http://download.oracle.com/docs/cd/E17236_01/epm.1112/esb_dbag/dotattrs.html

Here the "Sales" member under "Commission on sales" will share value with the main stored "Sales" and the product of "Commission %" and "Sales" will go to "Commission on sales".

Everything in our Accounts, Period and Entity Dimension is outline calculation, lets create our 

next Calc Script, here we have few options either we can write IDESCENDENTS("Margin " ) 

and IDESCENDENTS("Commission on sales" ) or we can calculate complete dimension in 

one go. The command which will help in achieving the same is CALC. CALC is basically used 

in three ways:

1. CALC (Members);

2. CALC DIM;

3. CALC ALL;

1. CALC Members can be used to calculate members like we used above ("Sales"; for 

calculating sale) but with some additional features like

CALC "Jan; Entity;"; this will calculate the Members January and Entity.

2. CALC DIM, this is what we are going to use now for our outline calculation.We can directly 

Write:

CALC DIM ("Accounts","Period", "Entity" ); This will calculate data in one pass (What is Pass, 

Why one pass, In real detail later ;) )

Save the above Calc Script as MyFourth.csc, Validate it.

Now I request you to start from begnning, Clear Data, lock and send data for input values, run 

MyFouth.csc and Retrieve data. Once done you will get following data, this is the data at upper 

level you can have a complete retrieval in you excel sheet just arrange the  members 

accordingly.

Now the most important and the last thing for the session :)

CALC ALL;

This was the default script which we were running in the session 1. CALC ALL command 

calculates and Aggregates the entire database based on the consolidation operators and 

member formulas. (There is a big difference between CALC and AGG (Aggregates) try finding 

it out, read Dbag for now)

Really important!!! Calculation Order:

When you perform a default calculation (CALC ALL) on your database,

Essbase calculates dimensions in a predetermined order.

If a database includes a dimension tagged as Accounts and a dimension

tagged as Time and if formulas are applied to members on the Accounts

dimension, Essbase calculates in the following order:

• First, the dimension tagged as Accounts, whether dense or sparse

• Second, the dimension tagged as Time, whether dense or sparse

• Third, other dense dimensions (in the order that they are displayed

in the database outline)

• Fourth, other sparse dimensions (in the order that they are displayed

in the database outline)

• Fifth, two-pass calculations (if selected in the database settings)

If you are really new to essbase then there will be few things which will be hard to understand 

but dont worry we will discuss them someday.


Cheers..!!!

Rahul