Dense and sparse dimensions are storage designations applied to dimensions in an Essbase BSO (Block Storage Option) cube that determine the physical structure of the Essbase database. The terms do not refer to how much data a dimension contains — they refer to how Essbase uses that dimension to organise its physical storage blocks.
In Essbase BSO, the dense dimensions define the block structure: every unique combination of members from all dense dimensions is represented as a potential data block. The sparse dimensions determine which of those potential blocks are physically allocated: a block is created only when at least one data value exists for that block’s dense member combination at any sparse dimension intersection. The dense dimensions’ member counts multiply together to determine the block size, while the sparse dimensions’ populated member combinations determine how many blocks exist in the database.
Dense vs Sparse — Practical Impact
| Designation | Effect on Block Size | Effect on Block Count | Standard EPM Assignment |
|---|---|---|---|
| Dense | Multiplies block size by member count | No direct effect | Account, Period |
| Sparse | No effect on block size | Each populated combination creates one block | Entity, Scenario, Version, custom dimensions |
The practical consequence: adding a dense dimension with 500 members to an existing application multiplies the block size by 500. Adding a sparse dimension with 500 members adds at most 500 blocks. Dense dimension member counts must be kept small; sparse dimensions can accommodate large member counts. The standard designation — Account and Period dense, all other dimensions sparse — reflects decades of Essbase performance engineering and should be deviated from only with documented analytical justification and a calculated block size impact.
Design Considerations
A common source of confusion: “sparse data” in the business sense (most account-entity combinations have no value) does not require making Account a sparse dimension. The correct approach for a sparse data set is to allow BSO to simply not create blocks where no data exists — which is the default behaviour when the dimensions with the most varied data populations are designated as sparse. The sparseness of the data and the sparseness of the dimension are related but distinct concepts.
What Goes Wrong in Practice
The specific design failure that produces the largest immediate performance impact is designating a high-cardinality custom dimension — such as a Project dimension with 3,000 members — as dense rather than sparse, in the belief that “Project should be dense because we have lots of project data.” Designating Project as dense multiplies every existing block’s size by 3,000 — turning a 5KB block into a 15MB block, making the database orders of magnitude larger than it needs to be and making calculations impractically slow. The designation must be reversed and the application rebuilt before any meaningful performance restoration is possible — there is no runtime workaround for an incorrect dense/sparse designation.
How Loop Wise Solutions Handles This
We calculate block size estimates for every proposed BSO cube during dimension design — before any application is provisioned — using the formula: block size = (product of all dense member counts) multiplied by 8 bytes. We include a dense/sparse decision rationale for every dimension in the design document, and we validate that the calculated block size falls within our design guidelines before the application is provisioned. Any deviation from the standard Account/Period dense designation triggers a formal design review before the application is built.
