By TopDown Team
August 24th, 2011
At a recent client, the project team had the task of reviewing an Essbase database application that provided analysis on network quality at a wireless switch station. Upon review, the team discovered that we could optimize the Essbase cube to load raw data from base stations at a faster load rate. The new reporting cube allowed for better analysis of the quality of the network, where problem areas typically lie, and it provided a sound basis for future expansions and network reconfigurations.
- Data staging analysis does not play a part.
- Connectivity and network bandwidth from Oracle database source to Essbase are constant.
- ODBC SQL connections are preferred over ftp of files.
- OCI should be adopted after an upgrade to a later release of Essbase.
MAXL Statement Used
The Resource Usage option, which shows up in the MAXL, controls how much cache a data load buffer can use. The setting, as percentage, will be between .1 and 1.
When performing an incremental data load, Essbase uses the aggregate storage cache for sorting data. You can control the amount of the cache a data load buffer can use by specifying the percentage .
The total of all data load buffers created on a database cannot exceed 1. Concurrent loads should not be set to 100%.
For our faster loads, we kept the value the same at 0.5.
The example below shows the resource usage as assigned to the buffer using MAXL:
alter database AsoSamp.Sample initialize load_buffer with buffer_id 1 resource_usage 0.5;
Benefits to Performing Multiple Data Loads in Parallel
You can commit multiple data load buffers in the same commit operation.
NOTE: When using Administration Services Console to load data into an aggregate storage database, only a single data load buffer is used.
Oracle Essbase 188.8.131.52 – Multiple SQL data loads in Parallel to ASO cubes – Using MaxL
Before load hourly, you need to load on a daily basis.
In this case, start with one load from one SQL Essbase load rule that has 145 fields. The model has 8 dimensions. Two fields on the SQL statement kept alias information and were ignored. From 145 fields, 135 were used to load base measures.
Use Test Cases
Data is loaded into an ASO Essbase model but not aggregated or materialized into views. After an implicit commit, the data could be retrieved as an aggregate. Then the load was split into 2, then 4, and then 8 SQL-based load rules. Below are the tabulated results for the various test loads:
|Test Type||Transaction type||Transaction Time||# input cells||input data level size||cache hit ratio||cache size||cache size limit|
|Benchmark||SQL Load||981 secs||141 Million||898 MB||0.84||1GB||1GB|
|export file||File Load||87 Secs||144 Million||917 MB||0.76q||1GB||1GB|
|load 70 of 154 fields||SQL Load||586 Secs||no data loaded||n/a||0.25||1GB||1GB|
|second half||SQL Load||599 Sec||70 million||406MB||1||1GB||1GB|
|load split 4 way||SQL Load||450.26 Sec (averaged)||144 Million||917 MB||0.76||1GB||1GB|
|load split 8 way||SQL Load||285 Sec (averaged)||144 Million||917 MB||0.76||1GB||1GB|
After the initial reloading from an export file, each load test easily determined that we would have more success loading data if we split the load into 8 loads. One find was that with 2 way split, the first load, which was individually (not parallelized), didn’t load any data.Summary
The SQL fields were then split by 2, then 4, then 8 from the original 135 fields. After each split, the load time was cut in half from the previous 4, or 2 or single SQL statement. With the current limitations of Essbase, the parallel could not be split beyond 8 load rules