An Essbase Cube For Increasing Network Quality At A Wireless Switch Station

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.

Technical Assumptions

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 a 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 11.1.1.3 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 typeTransaction typeTransaction time# input cellsInput data level sizeCache hit ratioCache sizeCache size limit
BenchmarkSQL Load981 secs141 Million898 MB0.841GB1GB
export fileFile Load87 Secs144 Million917 MB0.76q1GB1GB
load 70 of 154 fieldsSQL Load586 Secsno data loadedn/a0.251GB1GB
second halfSQL Load599 Sec70 million406MB11GB1GB
load split 4 waySQL Load450.26 Sec (averaged)144 Million917 MB0.761GB1GB
load split 8 waySQL Load285 Sec (averaged)144 Million917 MB0.761GB1GB

Summary

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) didnt load any data.

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.