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.
Answers before you ask.
Into blocks defined by the dense dimension member combinations, with sparse dimensions indexing which blocks exist. This block structure is why dense and sparse configuration matters so much on BSO: it determines block size and how many blocks are created, which in turn drives calculation speed and memory use.
Write-back, stored member formulas, full Calc Script calculation, and complex interdimensional logic. These let a BSO cube accept planning input, compute and store derived values, and run allocations and conversions — exactly what an input or calculation-intensive application needs, and precisely what ASO does not provide.
BSO stores data in dense or sparse blocks and supports write-back, stored formulas, and Calc Script; ASO is optimised for fast aggregation over very large member counts but restricts write-back and calculation. BSO suits input and heavy calculation; ASO suits large-scale reporting. They are complementary engines, not interchangeable ones.
Because the block is built from dense dimensions, the dense and sparse split directly sets block size and the number of blocks. A poor split can create huge, mostly empty blocks or an explosion of tiny ones, wrecking performance. Correct dense and sparse designation is one of the most important tuning decisions on a BSO cube.