Are you in a funk about slow Oracle Essbase updates? Did the prototype run at lightning speed but when the full data set was loaded to your production app, loading and calculation times started to take longer than expected? Did your Essbase app get great performance at first, then a year later your 10 minute update blossomed to more like 2 hours and 10 minutes?
Advances in computer hardware, such as multi-core processors, have enabled Essbase to more quickly load larger data sets and run more intricate calculations. But just using the latest and fastest hardware alone may not result in performance that meets your objectives.
Fortunately, there’s a range of things you can do to increase the speed of data loads and calculations. These include techniques related to Outline and Calc Script design, data management, and database settings.
Here are a few solution design approaches that could help reduce calculation times. For this article I’m assuming that your cube’s data storage is the traditional Block Storage type (also called BSO):
Dense vs. sparse: Which dimensions are tagged “dense” and which are “sparse” determines the size of the cube’s data blocks, Essbase’s fundamental data storage units. There is an optimal block size range, and generally very large data blocks cause slower calculation performance. There are other considerations too, for example the general calculation patterns that are required in your cube.
Calculation Script optimization: Depending on your requirements you could have fairly complex Calc Scripts. Calc Scripts should work with the dense and sparse dimension settings and not against them. Design each script to work through all the data blocks only one time, and not have to go back to a previous block to do more calculations.
Don’t calculate more than you have to: Could your Calc Scripts be re-calculating data that doesn’t change? You might consider using “Fix” statements to narrow the calculation down to the relevant slice of the database. A new related command “FixParallel” can separate your calculation into individual threads and calculate them simultaneously (under the right circumstances), for nice performance improvement.
Dynamic Calc members: Tagging Outline members as “Dynamic” means their calculation won’t happen when a Calc Script runs, but rather their consolidation or member formulas will be calculated upon retrieval. Opportunities tend to be found in dense dimension rollups and member formulas, the top level aggregations of sparse dimensions, and other member formulas like ratios and variances that should be calculated after the overall data aggregation. But be careful – while there is less work for your Calc Scripts to do, you could begin to slow down retrievals.
Attribute dimensions: Using Attribute dimensions could offload some calculation effort from their related stored dimensions, while providing additional useful aggregate views to users. Attribute dimensions are entirely dynamic (calculated upon retrieval), so the warning above applies here too.
Aggregate Storage Option (ASO): If your Block Storage cube is mainly just rolling up (aggregating) its data and doesn’t require intricate calculations, converting it to ASO may be an opportunity to significantly reduce calculation time.
Hybrid Aggregation (ASO and Block combined): This is an option recently made available in Essbase that enables you to get the robust calculation capabilities of Block Storage combined with the optimized aggregation of ASO, in the same cube. Whether this is a viable option depends on what you’re doing in your Essbase model.
Not all Essbase cubes will benefit the same way from these techniques. Depending on your model, they might bring significant improvement or very little. As they say, “results may vary.”
Other ideas that could help optimize Essbase, for example data management techniques and database settings, we’ll deal with in later articles.
Don’t scream at your Essbase cube, make Essbase scream!
Dave Stark is founder & president of Analysis Team, Inc. (www.AnalysisTeam.com) and an Oracle Essbase 11 Certified Implementation Specialist.
Read other articles in this series:
Part 2 : Speed Up Your Essbase: Optimizing Data Management
Part 3 : Speed Up Your Essbase: Optimizing Database and Calculation Settings