Block creation for data storage members

We all have the confusion that, how block creation happens in Essbase. Even I used to have that confusion. So, I wanted to clear that confusion by clearly explaining how block creation happens using data storage members.

In Block storage, I am going to tell about,

1.     Block size.

2.     The potential number of blocks.

3.     The number of existing blocks.

1.  Block size: The size of each cell is 8kb. So the size of a block will be 8* no. of. Cells (Dense * Dense) bytes.

Fig: 1.1

In the above picture, if you see we have two dense members i.e. Product and Year. For block size, we need to take members which are stored only. So no of cells= 9*1bytes. So Block size= 8*(9*1) = 72bytes.

Below Fig shows the Block size,

Fig: 1.2

2.  Potential Number of blocks: Maximum number of blocks that are derived from the product of one sparse member to other sparse members. Consider Fig: 1.1 we have five sparse members i.e. Account. Entity, Version, Period & Scenario. So the potential number of blocks= 3*4*6*1*17=1224.

Below Fig shows the Potential number of blocks,

Fig: 2.1

3. Number of existing blocks: Total number of block that exists means that contains data. Number        of existing blocks will be varied if we change the data, so by using I will explain in detail about  number of existing blocks by taking Data storage members. We have six data storage members i.e.

•         Store data 
•         Dynamic Calc 
•         Dynamic Calc and Store 
•         Never Share 
•         Label Only
•         Shared Member

Will see how existing block changes happen for all the data storage members.

• Store data: By default Essbase dimension are stored members. Now will see how existing block behave by giving data in the smart view excel sheet.

Fig: 3.1

In the above Fig: 3.1 I have given data for the three different combinations i.e.

  Ananthapur→FY11→v1→actual→P11→Gross Sales→Jan

        Ananthapur→FY11→v1→actual→P11→Gross Sales→Feb
  Ananthapur→FY11→v1→actual→P11→Gross Sales→Mar
        

So, it takes 3 blocks for storing the data as we have data with three different combinations. So observe in the below Fig: 3.2

Fig: 3.2

Now let’s try giving data for another 3 different combinations of data.

Fig: 3.3

In the above Fig: 3.3 I have given data for 6 different combinations i.e.

  Ananthapur→FY11→v1→actual→P11→Gross Sales→Jan
  Ananthapur→FY11→v1→actual→P11→Gross Sales→Feb
  Ananthapur→FY11→v1→actual→P11→Gross Sales→Mar
  Ananthapur→FY11→v1→Budget→P11→Gross Sales→Jan
  Ananthapur→FY11→v1→Budget→P11→Gross Sales→Feb
  Ananthapur→FY11→v1→Budget→P11→Gross Sales→Mar

So, totally 6 blocks get created as we have 6 different combinations of data. Observe the below Fig: 3.4.

Fig: 3.4

Note: If we clear the data in smart view excel, number of existing blocks will remain the same as 6. Because blocks are already created for combinations. Only if we clear the data from the database, then the number of existing blocks will become 0.

•     Dynamic Calc: Now I’ll tag the Account member with Dynamic Calc then will see how   existing blocks created.

Fig: 3.5

I have tagged Dynamic Calc to Net Sales, Gross Margin & Account. Above Fig: 3.5 show the data I have given for different combinations in Fig: 3.6(left). While submitting data, data gets aggregated to Net Sales, Gross Margin & Account.

But the number of existing blocks will be 6 only because Dynamic Calc does not store data it’s just for on fly calculation.

Fig: 3.6

So, data stores for only below given combinations.

  Ananthapur → FY11 → v1 → actual → P11 → Gross Sales → Jan

  Ananthapur → FY11 → v1 → actual → P11 → Gross Sales → Feb

  Ananthapur → FY11 → v1 → actual → P11 → Gross Sales → Mar

•      Dynamic Calc and Store: I have tagged the Dynamic Calc and Store to the Account, Gross  Margin & Net sales in Fig: 3.8(left) and given the data for same different combinations given previously in Fig: 3.7.

Fig: 3.7

Fig: 3.8

Even for the Dynamic Calc and Store existing blocks are created for level 0 members-only shown in Fig: 3.8(right) like in the Dynamic Calc. But the only difference with Dynamic Calc and Store is if the user wants the data to be stored, then for the first time data has to be requested using default calculation. Essbase will automatically recalculate the data and update the stored values, on the next request for data if a component value used in the member formula changes.

Fig: 3.9

So for the first time, I have requested the data. Number of existing blocks in the above Fig: 3.9 has been changed and data has been stored.

•      Never Share: For never share there is a variation from other storage members. I have tagged never share member to Gross Margin Fig: 3.11(left).

Fig: 3.10

I have given data to the above shown Fig: 3.10 members and data has been aggregated to their parent levels.

Fig: 3.11

So, existing blocks will remain same and potential blocks will increase because by tagging the never share to the parent having an only child will stop implied sharing, that means separate memory block will be created for the parent having an only child. So potential blocks will be increased in Fig: 3.11.

Fig: 3.12

In the above Fig: 3.12 stored members should be 3 for account dimension but it became 4 as I tagged Gross Margin with never share.

•       Label Only: I have tagged the Label Only member to Net sales in Fig:3.14 and I have given   the data to the same previous different combinations in Fig: 3.13

                 

Fig: 3.13

In the above Fig: 3.13 Net Sales is displaying only its first child as I have tagged Label only to it. But no block will be created for the net sales having 1^st child data. Block will be created for only level 0 members only in Fig: 3.14.

Fig: 3.14

Potential blocks will be decreased because no block will be created for Net Sales so no of stored members for account dimension decreases.

•       Shared Member: I have created a shared member Gross Sales as a child of Net Sales in Fig   3.16(left).

Fig: 3.15

In Fig: 3.15 combination with red color is shared member sharing Gross Sales data. But there won’t be memory and block created for that.

Fig: 3.16

So existing blocks will be created for level 0 members only in Fig: 3.16.