INTRODUCTION OF SCRIPTS

Hi all, as a fresher, I started my career with Hyperion technology. I just wanted to share my knowledge through this blog. For a fresher, it is more important to pick up the right technologies and tools and always keep reading, learning from various sources that will help your career upgrade. Along with it, a keen interest and passion for it are essential to succeed in the future. Today, many people are looking to pursue their career in the right way. I think Hyperion is a righteous path for a fresher.

Here am going to explain some important topics.

• Calculation Scripts

 About Essbase Calculation Scripts.

Basically, we have two ways by which calculation can be performed on the data.

                1a) Outline Calculation 

                1b) Member Formula

                 2)  Calculation Scripts.

Outline Calculation: It is performed based on the hierarchy, for e.g. will consider below fig:1 in that  Unit Sales and Price values will aggregate into the Sales.

                                                         fig:1

Member Formula: If we want to perform some complex calculations then member formula will be preferable. 

                                                        fig: 2

                                                       

 Calculation Scripts: It can be run on a database or on a part of the database.

• It can be a function or a command

So coming to practice will take some simple example and perform a calculation in three ways.

• Sales=Unit Sales-Price.

Now open Excel and connect to smart view.

First will see outline based calculation

• Before data is passed it shows #missing fig:3

                                   fig:3

                                     

After entering the data you have to pass the data and launch the default calculator  fig:4 

                               fig:4

 A second way to implement calculation scripts with member formula.(used for complex calculations)

• Change the consolidation property as (~ )  to the specified members on which you are performing member formula for e.g. consider the below fig:5

                                      fig:5  

• Assigning formula fig:6

                                        fig:6      

• Below image shows how member holds the formula                                       

                                         fig:7

• After calculating according to the above formula then results will appear fig:8

                                           fig:8

                                          

 A third way to implement calculation scripts :

• We can write scripts here

1. First, we need to create a calculation script by right-clicking on the database.fig:9
2. Scripts written can save on the Essbase server fig:10
3. Lock and send the data.
4. Execute the scripts fig:11
5. Results will display fig:12

                             fig:9

                                  

                          fig:10

                                           

                                           fig:11

                             

                                                   
                                           fig:12

What type of calculations we do?

The first basic things are

•CALCALL.

•CALCDIM.

•AGG.

CALCALL

Calculates and aggregates based on the database outline.

Syntax

1. CALC ALL [EXCEPT DIM (dimlist) \ MBR (mbrlist)]

CALCDIM

Calculation and aggregation are performed on a specified dimension.

Syntax

CALC DIM [(dimlist)]

•        Calculation order depends upon whether they are sparse or dense

•         First dense dimensions are calculated and then sparse.

AGG

•        It will calculate only sparse dimensions

•       It will do the only aggregation but not formula calculation.

Syntax

CALC AGG (dimlist)

Difference between CALC ALL, CALC DIM and AGG in Essbase?

AGG:

It consolidates the database values ignoring all member formulas

It works on the sparse dimensions

It is faster than CALC for less than 6 consolidation levels.

CALC ALL:

It will calculate an aggregate database based on the outline structure

The order of calculation depends on the characteristics of dimensions

   CALC DIM:

It calculates and aggregate values for the specified dimension members

The order of calculation depends on the sparse and dense types.