Group Sparse Member Combinations

To improve performance of Essbase block storage (BSO) data load, you can structure the data to minimize the amount of necessary disk I/O (reads and writes).

This section does not apply to aggregate storage cubes.

The most effective strategy to improve performance is to minimize the number of disk I/Os that Essbase must perform while reading or writing to the cube. Because Essbase loads data block by block, organizing the source data to correspond to the physical block organization reduces the number of physical disk I/Os that Essbase must perform.

Arrange the source data so that records with the same unique combination of sparse dimensions are grouped together. This arrangement corresponds to blocks in the database.

The examples in this chapter illustrate ways that you can organize the data following this strategy. These examples use a subset of the Sample.Basic cube, as described below:

Table 33-1 Dimensions and Values for Examples

Sparse, Nonattribute Dimensions	Dense Dimensions
Scenario (Budget, Actual)	Measures (Sales, Margin, COG, Profit)
Product (Cola, Root Beer)	Year (Jan, Feb)
Market (Florida, Ohio)

Note:

Because you do not load data into attribute dimensions, they are not relevant to this discussion, although they are sparse.

Consider the following source of data. Because it is not grouped by sparse-dimension member combinations, this data has not been sorted optimally. As Essbase reads each record, it must deal with different members of the sparse dimensions.

Jan
Actual    Cola          Ohio      Sales    25
Budget    "Root Beer"   Florida   Sales    28
Actual    "Root Beer"   Ohio      Sales    18
Budget    Cola          Florida   Sales    30

The above source of data loads slowly because Essbase accesses four blocks instead of one.

The following, more optimally organized source of data for the same Sample Basic cube has records sorted by a unique combination of sparse-dimension members: Actual -> Cola -> Ohio. Essbase accesses only one block to load these records:

Actual     Cola    Ohio    Jan   Sales     25
Actual     Cola    Ohio    Jan   Margin    18
Actual     Cola    Ohio    Jan   COGS      20
Actual     Cola    Ohio    Jan   Profit     5

To optimize your source data enabling import of many cells per record,

Ensure that records are grouped together by unique sparse-dimension member combinations.
Order the records so that the dimension in the record for which you provide multiple values is a dense dimension.

The next example uses a header record to identify the members of the Measures dimension, which is dense. The data is sorted first by members of the dense dimension Year, then grouped hierarchically by members of the other dimensions. Multiple values for the Measures dimension are provided on each record.

                                 Sales  Margin   COG  Profit
Jan Actual  Cola         Ohio       25      18    20       5
Jan Actual  Cola         Florida    30      19    20      10
Jan Actual  "Root Beer"  Ohio       18      12    10       8
Jan Actual  "Root Beer"  Florida    28      18    20       8

Notice that the heading and first data line that requires two lines in this example; the previous example needs four lines for the same data.