Database Reference
In-Depth Information
I had a conversation with a client concerning the size of a particularly large Planning
application. The application is three years old and the client periodically copies a forecast
to a new scenario to be used later for reporting. The continual growth of the database is
becoming an issue. The administrator asked me to help him quantify the overhead of
each scenario. Looking at the statistics that are available for each database in EAS, we can
see the total number of blocks: level-0 blocks and upper-level blocks. What is not available
is any information about how the data is distributed. It would be great if there was a way
to tell how many blocks were in a specific scenario. Well, actually there is, and here is how.
my example uses Sample/Basic, which has dense dimensions year, Accounts, and
sparse dimensions Product, market, and Scenario. The database has 704 blocks with 354
at level-0 and 350 upper-level blocks. The calculation script uses “Set msg Summary” that
places read/write statistics in the application log. The statistics are for the entire script so
sometimes it is necessary to break the code into multiple scripts. The message looks like:
Total Block Created: [2.4200e+02] Blocks
Sparse Calculations: [2.4200e+02] Writes and [1.2200e+03] Reads
Dense Calculations: [3.5400e+02] Writes and [3.5400e+02] Reads
Sparse Calculations: [2.3232e+04] Cells
Dense Calculations: [4.2480e+03] Cells
The second line that details sparse calculations block writes and reads is the one that
we are interested in. For those not familiar with scientific notation, 2.4200e+02 equals
to 242. 1.2200e+3 equals 1220 block reads.
For this example, I have changed Sample.Basic making Scenario sparse so that
the Actual and Budget scenarios are in different blocks. The entire database has 748
blocks. how many of the blocks are in the budget scenario? to decipher this, we need
a calculation script that fixes on the Budget scenario. Including Jan in the fix is not
required, but the script runs a little faster. If there is no statement between the FIx and
EnDFIx, Essbase is smart enough not to loop through the blocks. The statement “Sales
= Sales;” sets the member value to itself, which does not do anything other than cause
the script to execute. If this were a production database, I would have added a dummy
account to the database and referenced that instead of live data. The script follows:
Set UPDATECALC OFF;
Set Msg Summary;
FIX (Jan, Budget)
Sales(
Sales = Sales;
)
ENDFIX
The application log message is “Sparse Calculations: [0.0000e+00] Writes and
[3.7400e+02] reads” indicating that 374 Budget blocks were read. running the script for
the Actual scenario also returns 374 blocks, which confirms that the total blocks in the
database is 748 blocks. Do not expect every database to reconcile as nicely as Sample.
Basic. Scientific notation becomes less precise as the numbers get larger. Percentage
wise, the results are still meaningful.
variants of the script also can be used to find the level-0 blocks:
"FIX (Jan, Budget, @LEVMBRS (Product,0), @LEVMBRS (Market,0))"
