The basic unit of storage in Essbase is the “data block”. Blocks contain the actual data of your database and are stored in page (.pag file type) files located on one or more Server drives.
Essbase loads, calculates and retrieves data by data block.
This is key! Knowing this basic fact will enable you to think through optimization of data loading, calculation scripts and retrievals.
The data block and index explained:
The creators of Arbor Essbase enabled dimensions to be tagged or defined as either dense or sparse. When a dimension is tagged as dense, it becomes part of the storage structure called the data block
Consider an Essbase cube with the Dimensionality
DIM (160, 19, 3, 30, 200, 10000)
Tagging some dimensions dense creates the block
DIM (160d, 19d, 3d, 30s, 200s, 10000s)
Every data block that is created in the database has an identical structure.
In this example, it contains precisely 160 * 19 * 3 = 9,120 cells, or intersection points.
Data block size in bytes would be 9,120 * 8 = 72, 960. This is the fully expanded block size.
Physical block size calculation: it is the product of the stored members of the dense dimensions * 8 bytes.
All data blocks are stored on disk within the ESS*.PAG files.
Addressing, or locating, blocks of data is provided by means sparse member combinations.
These combinations become part of the storage structure called the index and are stored on disk with the ESS*.IND files.
The data block is a fixed format data structure the existence of which is driven by data-relevant sparse member combinations in the index. By data-relevant we mean that only where business data actually exists across sparse member combinations will a data block be generated.
For example, if we do not have any business for the product P00 in January in the country Jordan, we do not reserve any space in our cube for those intersection coordinates.
The small subcomponents of the cube (the data block and its index address) are quite readily moved between disk and working memory. These structures mesh very well with the general user requirement of only being interested in sub-sets of information from the cube at any one point in time.
Block creation explored:
The above Essbase cube contains 6 dimensions with the following configuration:
• dense dimension #1 containing 160 members
• dense dimension #2 containing 19 members
• dense dimension #3 containing 3 members
• sparse dimension #1 containing 30 members
• sparse dimension #2 containing 200 members
• sparse dimension #3 containing 10000 members
Unique combinations of the sparse dimensions form the index.
Data blocks are actually created depends upon unique sparse combinations that contain data
Essbase Cube Explosion:
The three defining characteristics of a Essbase cube storage structure are:
1. The number of dimensions
2. The number of members within each dimension
3. The hierarchical relationship of the members within each dimension
Data explosion can occur across each characteristic individually and concurrently having a combined (that is Cartesian) impact.
If we increase sparse dimension #1 to include 60 more members, the number of potential intersection points will be tripled.
Adding a completely new dimension will explode the number of potential intersection.
We will load the data at the leaf level in essbase and all hierarchical levels will form the consolidation levels. More the consolidation levels, more the block explosion.
The first rule of design in Essbase modeling is to minimize the number of dimensions in a database design.
While minimizing dimensionality is our first guiding principle, there is a dynamic tension between it and the reporting needs of the enterprise. The point is that the possibility of block explosion needs to be bounded in such a way as to produce intersections that are actually used for business analysis.
a) The final criterion for dense/sparse settings is the combination of settings that produces the lowest overall calculation time with acceptable storage configurations.
b) Tips to reduce the block size: Change a dense dimension to a sparse dimension or Make members in a dense dimension dynamic – Label only or – Dynamic calc.
c) The goal of the Essbase designer is two fold: One Create as few blocks as possible and the another is Create blocks that are as densely populated as possible.
a) Data blocks contain Cells: A cell is created for every intersection of stored members of the dimensions that are set as dense in the Data Storage dialog.
b) Stored members are those defined in the outline with storage types: Store Data, Dynamic Calc and Store, and Never Share.
c) All other storage types--i.e. Dynamic Calc, Label Only and Shared Member-- are not stored members and therefore do not take up space-- i.e. cells-- within a data block.
d) Each cell, as defined by the stored dense members, takes up 8 bytes of storage space on disk (before compression) and in memory.
e) The size of a data block can be calculated: it is the product of the stored members of the dense dimensions * 8 bytes.