Optimizing Parallel Data Load Processing

Although Essbase uses parallel processing to optimize processor resources across the data load stages, processor resources are idle at times. To take advantage of these times, Essbase can further divide record processing in the preparation and write stages. To tailor parallel processing to your situation, you can use the DLTHREADSPREPARE and DLTHREADSWRITE essbase.cfg settings to tell Essbase to use additional threads during these stages.

DLTHREADSPREPARE: Specifies how many threads Essbase may use during the data load stage that codifies and organizes the data in preparation to being written to blocks in memory.
DLTHREADSWRITE: Specifies how many threads Essbase may use during the data load stage that writes data to the disk. High values may require allocation of additional cache. See Implications in Sizing the Data Cache.
For ASO, Essbase Server uses one thread with aggregate storage cache. The DLTHREADSWRITE setting is ignored.
DLSINGLETHREADPERSTAGE: Specifies that Essbase use a single thread per stage, ignoring the values in the DLTHREADSPREPARE and DLTHREADSWRITE settings

Load huge outline member for ASO

I am building a ASO cube with Essbase(9.3.1) for shipment industry.
outline involve 3 large munber of dimension members:
1. DIM_SHIPPER (1,465,000), max generation is 5
2. NA_STATE (162,300), max generation is 3
3. DIM_CONSINEE (875,200), max generation is 5
4.... others dimension member maximum no more then 100,000. Top 2 dimensions are 80000 and 60000, others only thounsand level.
outline total including 10 dimension (not involve the account dimension).
Question:
1. After I finished loading all dimensions except DIM_SHIPPER, NA_STATE,DIM_CONSINEE. I found I can't open the outline with edit model, and Essbase report 'out of memory'error. At the same time I can't use ODI to load dimension anymore, ODI always report network error and time out error. (I think time out error is because long time no response from Essbase, am I correct?)

Answer:
1. Since can't use ODI to build dimension, so I change the loading ways and use flat file to build dimension. This ways is work, can build dimension in Essbase, but the performance is very poor. I found need to cost nearly 8 hours to finished DIM_CONSINEE dimension building.
2. Check for Disk Space. We usually get this error when there is insufficient disk space to create .pag files.
3. Increase your agent thread count, so research these essbase.cfg settings. I know I have seen that issue in the past with the agent being too busy, and adjusting threads greatly helped.
AGENTTHREADS
AGTSVRCONNECTIONS
SERVERTHREADS
Also, do not make AgtSvrConnections greater than AgentThreads. Note that using more threads will consume more memory, and is very specific to your OS so you probably should ask Oracle support what they recommend based on your specific environment before you change it.

4. Following are the minimum and max settings:
AGENTDELAY 20 60
AGENTTHREADS 2 500
AGTSVRCONNECTIONS 1 7

-----------
Answers:

Increase Data Load Performance

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.

For ASO database, the data load method is:
1. Create Data Load Buffer
2. Import data to data load buffer, many importing process can happen in the same time.
3. Export data from data load buffer to ASO database.

MaxL Code:
alter database AsoSamp.Sample initialize load_buffer with buffer_id 1;

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.dat' 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;

import database AsoSamp.Sample data from load_buffer with buffer_id 1;
By default, when cells with identical keys are loaded into the same data load buffer, Essbase resolves the cell conflict by adding the values together.

To create a data load buffer that combines duplicate cells by accepting the value of the cell that was loaded last into the load buffer, use the alter database MaxL statement with the aggregate_use_last grammar.
For example:
alter database AsoSamp.Sample initialize load_buffer with buffer_id 1 property aggregate_use_last;

query database appname.dbname list load_buffers;

For example, to create a slice by overriding values (the default), use this statement:
import database AsoSamp.Sample data from load_buffer with buffer_id 1 override values create slice;

alter database AsoSamp.Sample merge all data;

Time Dim - Calculation Performance

By default, the time dimension is set to be dense. But if you use incremental data loading in MaxL srcipt. And the data is loaded in the end of every month. You can set Time dimension as sparse dimension, if you have Intelligent Calculation enabled, only the data blocks marked as dirty are recalculated.This will significantly increase the data loading performance.
--------
Incremental Data Loading
Many companies load data incrementally. For example, a company may load data each month for that month. To optimize calculation performance when you load data incrementally, make the dimension tagged as time a sparse dimension. If the time dimension is sparse, the database contains a data block for each time period. When you load data by time period, Essbase accesses fewer data blocks because fewer blocks contain the relevant time period. Thus, if you have Intelligent Calculation enabled, only the data blocks marked as dirty are recalculated. For example, if you
load data for March, only the data blocks for March and the dependent parents of March are updated.
However, making the time dimension sparse when it is naturally dense may significantly increase the size of the index, creating possibly slower performance due to more physical I/O activity to accommodate the large index.
If the dimension tagged as time is dense, you still receive some benefit from Intelligent Calculation when you do a partial data load for a sparse dimension. For example, if Product is sparse and you load data for one product, Essbase recalculates only the blocks affected by the partial load, although time is dense and Intelligent Calculation is enabled.
Note: This method works only for ASO