Friday, June 11, 2010

Essbase Outline Performance Testing - Exciting Update

I had a number of comments and emails following my post the other day about the performance of opening an outline.  Many of them shared their belief that ASO databases did open more slowly, and why, and I had a couple of suggestions:
  • Try converting the Big1.Big1 outline to ASO using the wizard, then compare performance; and
  • Try compacting the outlines using the free Oracle ESSCMDQ utility that Oracle QA uses for testing.
I did that and here is the result.

Updated Analysis (click image to view in a larger window)
As you can see, converting Big1 to ASO caused the outline to grow by over 300% and the time to open to grow over 600%.  Subsequent compacting of BigASO did not cause that much of a decrease in size or time to open.

There was, however, a big impact on the zzz outline that was generated by Essbase Studio.  The filesize was reduced by 91% and the time to fell by an average of 75% (or nearly 45 seconds).

The customer who provided the zzz outline compacted their outline and have reported it opens much faster in their environment.  As a result, it looks like compacting may have an impact on outline open operation performance with more significant results on Essbase Studio generated outlines.

Thanks to my friends Glenn Schwartzberg and Edward Roske for pointing out the availability of ESSCMDQ which is available at http://www.oracle.com/technology/products/bi/esscmdq_sampleapps.html.  I would heavily recommend you backup your outlines before running this utility as it is not available for every minor version of Essbase and I had it crash on my laptop a couple of times.   By the way, on my 11.1.1.0 laptop, I had to use ESSCMDQ 9.3.1 as ESSCMDQ 11.1.1.1 threw an exception.

Finally, I am thinking of doing a poll on the blog about results you see when compacting your outlines.  Would you like to participate in that poll?   Also, would you be interested in the Java code I used to run the testing?  If so, I will do another blog post to explain how it works and will make it available from our website.

5 comments:

Anonymous said...

Thank you for trying the big1 as ASO. It confirmed what I have been seeing with ASO Outlines.

I wish they would open faster and allow me to do build dimensions on them faster.

Tim Tow said...

My pleasure.. I will also make sure the Essbase product manager sees the results of my testing so they may address it in development.

Tim

Tey Brady said...

Compacting ASO outlines sped up the time to open/edit outlines and to build them using Maxl by around 90%.

jithendra said...

The utility is now available in the following link

http://www.oracle.com/technetwork/middleware/bi-foundation/esscmdq-sampleapps-093105.html

Thanks,
Jithendra

Tim Tow said...

Excellent! Thanks for sharing the link..

Tim