An Essbase Cube for Increasing Network Quality at a Wireless Switch Station

Avatar

By
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.

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 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 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


Avatar

About TopDown Team

The TopDown Team includes members of TopDown Consulting who want to let the community know about webcasts, conferences, and other events. The team also conducts interviews on various EPM industry topics.

Leave a Reply

Your email address will not be published. Required fields are marked *