Calculation Scripts:- Session 11

Ways to create data blocks:

1. Data Load,

2. DataCopy Command,

3. Sparse Dimension Consolidation,

4. Member Formulas:

4a. Assigning a constant value to a sparse member,

4b. Turning Create Block on Equation setting on and assigning a non constant value,

4c. Create non missing blocks

Data load: The moment we load data to Essbase, It creates block for those combinations 

automatically. In data copy we copy data from source to a particular destination, it this case there is no need to create the blocks, data copy command takes care of it. Same is the case with Sparse Dimension Consolidation.

Here we will be concentrating on how to create block when it comes to calculation scripts, before we do that, why we need to create blocks using calculation scripts?? This is because (if 

we talk about level 0 blocks) Essbase runs calculation on existing blocks, For ex. if for all your 

entities you want to increase overhead cost by 10% then Essbase will increment overhead only 

for those entities for which we have data (Which is quite practical). To understand it better lets 

take an ex: Assuming we have following data in Essbase cube:

(Clear all data and lock and send above)

If we will look at the statistics we will have only one block as per rule 1.

Now we want to Increase the overhead of all entries by 10%, for this we can write a simple script:

Now If we retrieve data for All Entities we will get results only for Apac1

Now I will be entering data for APAC1, APAC3 and NA5 and will execute the same calculation script:

Number of blocks will increase to 3:

After execution of the same script this time we will get data for 3 blocks, because when we ran the calculation we had 3 blocks created.

We can see that there is no block created for APAC2, now lets create a block for APAC2, 

FY10 and Actual and assign an Overhead as 2500: Prior to running this script we can still see 

that number of blocks are 3:

Now lets execute the below script:

Number of block increased to 4 after executing this:

Here you can see the results

Now why this script created a block for APAC2? this is because we have assigned a value to sparse member(as per rule 4a) This is one more way to create blocks.

Let's use create blocks on equation for creating blocks, to explain this, let clear all the data from our database:

Now execute our below script to create block for APAC1

After executing this script, lets check the statistics again

ohh its still say 0 blocks, so what have we missed here? 

Its SET CREATEBLOCKONEQ ON; Because either we have to assign a constant value like we did in our last example or we need to have CREATEBLOCKONEQ ON.Now Execute the same script by adding SET CREATEBLOCKONEQ ON;

lets check the number of blocks:

See we got a block created

Now lets understand CreateBlockOnEquation using an Example, Cleared the data, now I want to set OverHead for all the level0 Decedents to 2500 for Jan and 2600 for Feb:

If we look at number of blocks its 0:

Data retrieved is #Missing, reason is simple we dont have blocks created for any of the entity so our script resulted in nothing. Even though we have used CREATEBLOCKONEQ ON but for this to create block we need to assign a non constant value to sparse dim member. For creating a block and assigning a non constant value I particularly prefer [Sparse] = [Sparse]  + 0;

Lets create blocks first:

Check the statistics:

If we retrieve data now, we will get the expected results:

Create non missing blocks: Its the simplest but the trickyest way to create block, SET 

CREATENONMISSINGBLK ON enables Essbase to create potential blocks in memory where 

the dense-member formulas are performed. Of these potential blocks, Essbase writes to the 

database only blocks that contain values; blocks resulting in only #MISSING are not written to 

the database.So here we need to write a separate code for block creation:

Cheers..!!! 

Rahul S.