This section does not apply to aggregate storage databases.
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 database. 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 data source 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 database, as shown in Table 184:
Because you do not load data into attribute dimensions, they are not relevant to this discussion although they are sparse. |
Consider the following data source. Because it is not grouped by sparse-dimension member combinations, this data has not been sorted for optimization. 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
This data loads slowly because Essbase accesses four blocks instead of one.
An optimally organized data source for the same Sample.Basic database shows different 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
You can use a data source that loads many cells per record. Ensure that records are grouped together by unique sparse-dimension member combinations. Then order the records so that the dimension in the record for which you provide multiple values is a dense dimension.
The next data source 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 and 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.
For information about arranging data in source files before loading, see Data Sources that Do Not Need a Rules File.