Skip to main content

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→FY11v1actualP11Gross SalesJan
        Ananthapur→FY11v1actualP11Gross SalesFeb
  Ananthapur→FY11v1actualP11Gross SalesMar
        
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→FY11v1actualP11Gross SalesJan
  Ananthapur→FY11v1actualP11Gross SalesFeb
  Ananthapur→FY11v1actualP11Gross SalesMar
  Ananthapur→FY11v1BudgetP11Gross SalesJan
  Ananthapur→FY11v1BudgetP11Gross SalesFeb
  Ananthapur→FY11v1BudgetP11Gross SalesMar
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 1st 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.


Comments

  1. Good work... Exemplary efforts... Impeccable information... Continue this run...!

    ReplyDelete
  2. Superb work. Its a very informative blog.

    ReplyDelete

Post a Comment

Popular posts from this blog

Adding formulas to the Dataforms - Hyperion Planning

We all know, how to create a data form using rows, columns, page and POV. In this I want to explain how to add Formula rows/columns in Hyperion Web form. Here in this blog I have used application called VIZAGPLN. All the screenshots show this application only. Step-1: Go to Planning Application > Navigate > Applications > Planning > VIGAZPLN as shown below. Step-2: Go to Administration >  Manage >  Forms and Ad Hoc Grids to open the forms shown as below. Step-3: Select the form and click on edit, shown as below. Step-4: Go to Layout and click on edit select member selector icon to insert require Member/members in rows and columns as shown below. I will explore further how to add formula row/column with below three requirements for better understanding.             Req1) Displaying Half year sales using Sum function:                       Sum function returns the sum of rows, columns or cell.                       Example:

Password encryption in MAXL script

Password encryption in MAXL script An objective of this blog is to provide information about how to encrypt the Username and Password in Maxl script. To login into Essbase Server, in traditional method we manually enter the User credentials and do the process. We also have another process to login into Essbase server and do the process i.e. through Maxl scripts in which we can automate Maxl scripts through batch by scheduling and also through command prompt we can run the process by calling the Maxl. Through manually login User credentials are remembered by the user whereas through Maxl script we give the User credentials in text file. So, there might be a chance of stealing the User credentials. To provide the more security we do User credential encryption. There will be two basic keys are used Encrypt: Public key Decrypt: Private key The Public and Private Key pair comprises of two uniquely related Cryptographic keys (It is a string of data used to lock and unlock cry