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:

ASO - add huge dim members

ASO always have a lot of members. the most polular methods are generation reference method and children reference method.

1. Create some rule files
2. Create some data files, the data files are not for data loading, but for member creation. Check the consistence between the data file and the rule files
3. If there are too much data members, you may have to use a computer language(such as JavaScript) to generate the better formated data files from a more basic original data file
4. Use Maxl Import command to load data to buffer from the data files in parallel
5. Use Maxl import command to load data from buffer to essbase

import database AsoSamp.Sample data connect as TBC identified by 'password' using multiple rules_file 'rule1','rule2' to load_buffer_block starting with buffer_id 100 on error write to "error.txt";
import database AsoSamp.Sample data from load_buffer with buffer_id 1, 2;

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;

Write data from BSO to ASO

BSO can have "write back" functionality to any level, and BSO can perform complex calculations. ASO is for large aggregation focused databases with many dimensions and many members. Sometimes, we want to use the advantage of both ASO and BSO.

In the world where I can now make an ASO database the source of a partition, I can take advantage of the BSO strengths (write back to any level, powerful calculation engine) and then source this information to a consolidated ASO database that maybe has the volumes of detail from other sources.
Note - the new Hyperion Profitability and Cost Management solution uses this model: BSO for allocation calcs and loads to an ASO cube for reporting.

Steps:
. Create the BSO database in a separate application from the one in which the ASO database is located.Typically, the block storage database contains a subset of the dimensions in the aggregate storage database.
. Create a transparent partition based on where you want the data to be stored. Make the block storage database the target and the aggregate storage database the source.

Essbase ASO Perfromance

● Use stored hierarchies (rather than dynamic hierarchies) as much as possible.
● Use alternate hierarchies (shared members) only when necessary.
● Minimize the number of hierarchies. (For example, each additional stored hierarchy slows down view selection and potentially increases the size of the aggregated data).
● If a hierarchy is a small subset of the first hierarchy, consider making the small hierarchy a dynamic hierarchy. Considerations include how often the hierarchy data is queried and the query performance impact when it is dynamically queried at the time of retrieval.
● The performance of attributes is the same as for members on a stored hierarchy.
● The higher the association level of an attribute to the base member, the faster the retrieval query
------------------------------------------------------------------
● Convert BSO to ASO in case, ASO has much faster running performance compared to BSO. ASO cannot include Calculation Script however. Must use migration wizard for this transmision, besides, as soon as it is transferred to ASO, it cannot be transferred back t BSO.

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