Calculation Scripts:- Session 1

I have read many time people wondering from where to learn calculation scripts or Business

Rules; even more important Where to Start!. The First day I saw this question I thought to write

a blog for the same. My inspiration for this is none other then John Goodwin whom I admire a

lot.

I assume that the readers who are going to read this one are familiar with basics of

Essbase/Planning. We will be starting from writing easy calculation scripts and there will hell

lots of things which we will learn gradually. Trust me if know how to write calculation scripts then

you can write Business Rules as well, Business Rules provides some additional functionality,

we will talk about this later.

Calculations in Essbase:

Well, there are basically two ways by which we can perform calculation on the data.

1A. Outline Calculations,

1B. Member Formulas,

2. Calculation Scripts.

We try to design our outline in such a way that (Most of the times, exceptions are everywhere)

first we try to implement our logic using the outline calculations, if its not possible by outline then

Member formulas and if it’s not even possible to implement our logic by member formulas then

calculations scripts comes into picture (This is the basic stuff I am talking about, we will learn

lots of usage of the same as we go further because if we go in details now it won’t make

sense).

Outline Calculations:- These are the simplest way of calculation, it calculates the value on the

basis of the hierarchy. Ex:

Here if we calculate Acc1 then it will be sum of Acc1A, Acc1B and Acc1C.

Member Formula:- It allows us to do a bit of complicated calculations, ex if we want to

implement something like Acc1 = Acc1A + (20% of Acc1B) - (15% of Acc1C), then its easier to

implement using a member formula.

Calculation Scripts:- If you want to override the outline and member formula calculations then

we need to write a calculation script. A calculation script can be a function, a command, a

formula or a series of these that can be run on database or on a portion of database.

Enough of theory, now let’s talk something practical.

We all know a simple equation:

“Margin” = “Sales” – “Cost of Goods Sold”;

Now how to implement this little equation in Essbase, we will be using all of our three ways to

implement the same. Lets start with creating an Essbase Application, a database:

In EAS console, Right Click on Applications under Essbase Server, Select Create Application-

>Using Block Storage

Or you can Create a application from File->New->Block Storage Option. Enter The Application Name and hit OK,

A Non Unicode Mode Application's name cant be more then 8 characters (What is unicode and non unicode later), Once the application is created you will get the instance and app files at below location:

Now Right Click on the Application name and Select Create Database.

After saving you will get an DB created under Application and .db, .dbb, .esm, .otl, .tct files created under MyDb folder:

Now double click on Outline, then in right pane right click and use Add child to Create a

member Account, now right click on account and use available options to Create the following

outline:

1. Here we have implemented our First scenario and as of now just keep two dimensions

Account and Period

To test the results Lets use our Excel Addin. Open Excel from Hyperion option select Connect:

Once Connected, Perform a Hyperion->Essbase-> Retrieve, you will get a intersection of

Account in row and period in column, to drill down the hierarchy Double click on Account and

then on Period (My drill down zoom in property is set to 'Bottom Level', Essbase->Options-

>Zoom), for Simplicity I have taken Jan, Feb, Mar as period (Deleted rest of them manually)

and 3 of our account members in rows, By default you will find #Missing for all the cell, Enter the

value for Sales and COGS, once values are entered, perform a Hyperion->Essbase->Lock (to

have a read lock on database) then Hyperion->Essbase->Send (to send the excel sheet's data

to database). We are keeping Margin as #Missing because the intention is to calculate this

value automatically:

After Lock and Send above values are passed to Essbase, now its the time to run of default

calc. Go to Hyperion->Essbase-> Calculation.

You will get an option to run Default Calc, Default Calculation calculates the complete database, more on this later:

Hit Calculate and wait for the below message to pop up:

Once you get this, Select Hyperion->Essbase-> Retrieve to retrieve the calculated values from Database.

here we are we got the calculated values for Margin.

Second Way to Implement the Same: Create three members with consolidation

property as ~ and write a member formula for Profit as per equation, To change

the member properties in Essbase right click the member and select edit member

properties in the default first tab, change the consolidation property to

ignore and in Formula tab enter this formula.

Once you will save this you will get:

Now If you will retrieve the data in the same sheet you will be getting the values which are

already present in Essbase. One of the ways to overwrite values is to pass #Missing, and lock

and send the data again, for now lets do the same for the combination shown above type

#Missing and lock and send it back to Essbase.

Now enter values for Sales and COGS, Lock and Send data to Essbase.

Perform the default calculation on database, Hyperion->Essbase->Calculation, Run Default Calc and Retrieve the result:

Here comes the 3rd way, our Calculation Script one: To write a calc script:

Right Click on the database, Create, Calculation Script. Type in the same formula which we have used as the member formula, save the calculation script with any name

(Essbase stores calc Scripts in .csc)