BSO — Block Storage Option — is Essbase’s original multidimensional storage engine, which organises data into physical storage blocks whose structure is defined by the application’s dense dimensions. Every unique combination of members from all dense dimensions defines a block; all data values at that dense combination — across all sparse dimension members — are stored within a single block. The block structure enables Essbase’s full Calc Script calculation capability, including write-back, member formula storage, interdimensional calculations, and allocation logic — capabilities that ASO does not support in the same way.
BSO Block Structure and Performance
Block size in BSO is calculated as: (number of dense members across all dense dimensions) multiplied by 8 bytes per cell. A block size of 8KB is generally considered optimal; blocks smaller than 2KB are inefficiently small, and blocks larger than 100KB produce database footprints and I/O patterns that degrade calculation performance significantly. Block size is determined at design time by the choice of which dimensions are dense and the member count in those dimensions. Adding members to the Account dimension increases block size; adding a new dense dimension multiplies the existing block size by the new dimension’s member count — a non-linear growth that can produce block size issues unexpectedly.
BSO calculations proceed in a sequence determined by the Calc Script’s FIX statements and the dimension calculation order. For a full database calculation (AGG or CALC ALL), Essbase calculates member formulas across all blocks, then aggregates parent members from children, in the sequence specified by the calculation order. For targeted calculations (FIX-based Calc Scripts), Essbase processes only the blocks that match the FIX specification — which is how well-scoped business rules achieve performance significantly better than full database calculations.
Design Considerations
The most important BSO design parameter — and the one with the most significant performance implications — is the dense/sparse designation. The Account dimension is almost always dense because it contains member formulas that must be computed across every block. The Period dimension is conventionally dense because time-series calculations operate across periods within a single block. All other dimensions should be sparse by default unless a specific, analysed reason requires otherwise. Making a high-cardinality dimension dense — a Product dimension with 5,000 members — will produce enormous blocks and database sizes that make BSO calculations impractical.
What Goes Wrong in Practice
The most damaging BSO design error in practice is using BSO for a large, flat reporting application that has no calculation logic — no member formulas, no allocation rules, no write-back — and thousands of sparse dimension members producing an extremely sparse block population. In this scenario, BSO’s block structure produces a large number of sparsely populated blocks whose combined size far exceeds the equivalent ASO structure for the same data. Query performance for aggregation is also slower than ASO because BSO requires a calculation step to aggregate parent values, while ASO aggregates dynamically. Choosing BSO for this pattern produces a database that is significantly larger and slower than an ASO equivalent for the same workload.
How Loop Wise Solutions Handles This
We calculate estimated block size and block density for every proposed BSO cube during the design phase, using the planned dimension structure and the expected data population. Where calculated block size exceeds our design threshold, we review the dense/sparse designation and the dimension design before the application is provisioned. Block size and database size estimates are included in the application design document, so that the client’s IT team has a realistic expectation of the infrastructure footprint before go-live.
