Incrementally Loading Data Using a Data Load Buffer

Using the import database data MaxL statement to load data values from a single data source does not involve the aggregate storage data load buffer.

If you use multiple import database data MaxL statements to load data values to aggregate storage databases, you can significantly improve performance by loading values to a temporary data load buffer first, with a final write to storage after all data sources have been read.

In the aggregate storage data load buffer, Essbase sorts and commits the values after all data sources have been read. If multiple (or duplicate) records are encountered for any specific data cell, the values are accumulated (see Resolving Cell Conflicts). Essbase then stores the accumulated values—replacing, adding to, or subtracting from existing data values in the database. Using the aggregate storage data load buffer can significantly improve overall data load performance.

Note:

When using the aggregate storage data load buffer, the choice for replacing, adding, or subtracting values is specified for the entire set of data sources when loading the data buffer contents to the database.

While the data load buffer exists in memory, you cannot build aggregations or merge slices, because these operations are resource-intensive. You can, however, load data to other data load buffers, and perform queries and other operations on the database. There might be a brief wait for queries, until the full data set is committed to the database and aggregations are created.

The data load buffer exists in memory until the buffer contents are committed to the database or the application is restarted, at which time the buffer is destroyed. Even if the commit operation fails, the buffer is destroyed and the data is not loaded into the database. (You can manually destroy a data load buffer by using the alter database MaxL statement. See the Oracle Essbase Technical Reference.)

Note:

Stopping the application before committing the buffer contents destroys the buffer. In this situation, after restarting the application, you must initialize a new buffer and load the data to it.

  To use the data load buffer for aggregate storage databases:

  1. Prepare the data load buffer, where data values are sorted and accumulated by using the alter database MaxL statement to initialize an aggregate storage data load buffer. For example:

    alter database AsoSamp.Sample 
       initialize load_buffer with buffer_id 1;
  2. Load data from your data sources into the data load buffer using the import database MaxL statement. Use multiple statements to load data from multiple data sources. You can include any combination of data sources, such as .xls files, text files, and SQL relational sources. Specify a rules file if the data source requires one.

    The following example loads three data sources, one of which uses a rules file, into the same data load buffer:

    import database AsoSamp.Sample data 
       from server data_file 'file_1.txt' 
       to load_buffer with buffer_id 1
       on error abort; 
    import database AsoSamp.Sample data
       from server data_file 'file_2' 
       using server rules_file ‘rule’ 
       to load_buffer with buffer_id 1;
       on error abort;
    import database AsoSamp.Sample data 
       from server excel data_file 'file_3.xls' 
       to load_buffer with buffer_id 1
       on error abort;

    To load data into multiple load buffers simultaneously, see Performing Multiple Data Loads in Parallel.

  3. Use the import database MaxL statement to commit the data load buffer contents to the database. For example:

    import database AsoSamp.Sample data 
       from load_buffer with buffer_id 1;

    To commit the contents of multiple data load buffers into the database with one MaxL statement, see Performing Multiple Data Loads in Parallel.

The following incremental data load example provides optimal performance when new data values do not intersect with existing values:

  1. Create a single data load buffer using the ignore_missing_values and ignore_zero_values properties. For example:

    alter database AsoSamp.Sample 
       initialize load_buffer with buffer_id 1
       property ignore_missing_values, ignore_zero_values;

    If the database must be available for send data requests while the database is being updated, initialize the data load buffer with the resource_usage grammar set for 80%. For example:

    alter database AsoSamp.Sample 
       initialize load_buffer with buffer_id 1
       resource_usage 0.8 property
       ignore_missing_values, ignore_zero_values;
  2. Load the data into the buffer. For example:

    import database AsoSamp.Sample data 
       from server data_file 'file_1.txt' 
       to load_buffer with buffer_id 1
       on error abort; 
    import database AsoSamp.Sample data
       from server data_file 'file_2'
       to load_buffer with buffer_id 1;
       on error abort;
    
  3. Commit the contents of the data load buffer to the database by creating a slice and adding values. For example:

    import database AsoSamp.Sample data 
       from load_buffer with buffer_id 1
       add values create slice;