Calculation Scripts:- Session 10


Data Blocks:

In Block Storage databases, Essbase uses two types of structure to store data:

1. Data Blocks

2. Index Blocks

Lets go in flashback. We all know that the dimensions can be categorized in sparse and dense 

type and data is not stored for a member but its stored at a member combination. In our 

application we have 5 dimensions, which are of type:





"The number of stored members in Dense Dimensions will contribute in defining blocksize"

Stored Members in Accounts * Stored Members in Period * 8 will be the block size in Bytes

13*19*8 = 1976 Bytes will be our block size

"The number of stored members in sparse Dimensions will help us in finding potential number of blocks"

Potential Number of Blocks = Stored Members in Entity * Stored Members in Year * Stored Members in Scenario; 

13*6*4= 312;



Lets clear all data of our cube and check the statistics, to check the statistics: Right click on database->Edit->Properties





If you will notice, the block size here is 1976 bytes, Potential number of blocks is 312 as we 


have calculated above and because we have cleared all data from this cube existing number of 

blocks are 0. Lets lock and send below data to the Essbase cube and check the statistics:






































Now why the existing number of block and level 0 blocks increased to 1 ?



To answer this let check at which combination this data is stored in Essbase:

Budget->Fy10->APAC1->Jan->GrossSales = 12300 

Budget->Fy10->APAC1->Feb->GrossSales = 12400

Budget->Fy10->APAC1->Mar->GrossSales = 21300



Budget,Fy10, APAC1 these there members are from sparse dimensions, they will make an 

entry in index file and every entry in an index file has a data file associated with it. Though 

Essbase has it own format for index and data file but for understanding lets assume that below 

is the format:





What if we have following data in the Essbase:




Budget->Fy10->APAC1->Jan->GrossSales = 12300 

Budget->Fy10->APAC1->Feb->GrossSales = 12400

.

.

.

Budget->Fy10->APAC1->Dec->GrossSales = 14100

Budget->Fy10->APAC1->Jan->ThirdParty = 5000

Budget->Fy10->APAC1->Feb->ThirdParty = 5025

.

.

.

Budget->Fy10->APAC1->Dec->ThirdParty = 5275

.

.

.

Budget->Fy10->APAC1->Dec->OverHead = 475





Still for all the combination the sparse member combination is same so there is no need to 

have an extra entry in the index file just the data associated with this combination needs to be 

modified.



Now lets assume if you lock and send these values to Essbase:




Now the combination at which data will be stored:

Budget->Fy10->APAC1->Jan->GrossSales = 12300 

Budget->Fy10->APAC1->Feb->GrossSales = 12400

Budget->Fy10->APAC1->Mar->GrossSales = 21300

Budget->Fy10->APAC1->Apr->GrossSales = 14000

Budget->Fy10->APAC2->Jan->GrossSales = 12300 

Budget->Fy10->APAC2->Feb->GrossSales = 12400

Budget->Fy10->APAC2->Mar->GrossSales = 21300

Budget->Fy10->APAC2->Apr->GrossSales = 17000



Now for first four data entries we have the same set of sparse dim combination (Budget,Fy10, APAC1) and this combination is already a part of index file but the next four data entries are for Budget,Fy10, APAC2 so there will be one more entry in the index file and this will point to a new data block in the data file.




If you look at the statistics



Its a bit complicated for beginners, if you have any question on this please feel free to shoot.

Cheers..!!! 
Rahul S.

Comments

Post a Comment

Popular posts from this blog

Multiple Navigation Flows are Active

Image
We have witnessed an issue where application migration or sometimes service restart in PBCS/EPBCS activates multiple navigation flows.  If multiple navigation flows are active you wont be able make changes in any of them, because in order to make change in navigation flow you have to make it inactive. Typically you may end up in following situation:  Solution is pretty simple: 1. Create a copy of any of the existing navigation flow:  2. Give it any desired name:  3. Now, you will see one additional navigation flow, with inactive status: 4.  Make it active: 5.  At this point of time you will have one active navigation flow and two inactive  6. Select the additional navigation flow you have created and delete it  7. Now you are back in business: Rahul S.
Read more

The Member Does Not Exists For The Specified Cube

Image
The member "" does not exist for the specified cube or you do not have access to it. Issue:  If you are running into a issue, where for a RTP member selection is throwing an error for a missing member, this regardless of the member you have selected in RTP or regardless of rule. You may be able to execute the rule from Calc Manager and not from Planning. Reason: You may run into this issue if: 1. You have added members in Planning and have not refreshed the cube 2. For the default value of a RTP in calc member you have a member which is not valid any more for planning application 3. For the default value of a RTP in calc member you have a member which is not valid any more for a plan type Solution: 1. Refresh cube 2. Update Default value of RTP which is causing issue to member which exists 3. Deploy the rule Rahul S.
Read more

"Smart View handled an unknown exception thrown by Microsoft Office" Error on Vista, Windows 7, Windows 2008

My MS-Office Components are Crashing After Installing Smart View on Vista, Windows 7, Windows 2008! This is one more one pretty common issue,   "Smart View handled an unknown exception thrown by Microsoft Office."   This is actually a known issue in version 11.1.2.1. If you look at the read me of Smart View you will find bug# 11696583 "On Vista, Windows 2008, and Windows 7 operating systems, Excel may terminate abruptly upon launching after you install Smart View. Workaround: Turn off “User Access Control” in User Accounts, available from the Control Panel." Here is the link to read me: http://download.oracle.com/docs/cd/E17236_01/epm.1112/readme/sv_1112100_readme.html When the User Access Controls are on Smart View faces problem in registering, when you set it off Smart View register itself like a charm. To have more information on UAC: http://technet.microsoft.com/en-us/library/cc709691(WS.10).aspx Now, when y...
Read more