As Oracle Essbase has grown in popularity over the years, customers have pushed the envelope on dimensionality and quantity of data. With cubes containing more data and expected to crunch increasingly complex calculations, the update process (outline refreshes, data loads and calculations) can sometimes get bogged down.
Optimizing Essbase might seem like sorcery if you’re unfamiliar with the inner workings of the software, but it’s not so mysterious if you understand the basics and know where to look for opportunities. In other posts, we discussed some ideas for optimizing Block Storage (BSO) Essbase updates related to model design (Outlines and Calc Script basics) and data management.
In this article we’ll look at database and calculation settings that might improve performance, depending on the circumstances of your cube design and data. There are more options than the few presented here, but when I’m tuning I typically start with these.
Database Caches: There are two types of database cache for BSO cubes – Index and Data. Setting the right amount of memory assigned to them can improve performance for both retrievals and calculations. To the extent the index or data needed resides in memory (that is, “cached”), the time needed for Essbase to read from disk is reduced.
Index Cache: This reserves memory for the Index (.ind files) which holds information about which data blocks are present and where to find them. Optimally, this cache is set large enough to hold the database’s entire Index but your database size and memory constraints may make that impractical.
Data Cache: This holds the data from blocks that were most recently used by a calculation or retrieval. While calculations can benefit from an optimal allocation of Data Cache, retrievals could see quite a significant improvement in speed. Data Cache needs only to accommodate a small percentage of your database at a time, and best if the cache is large enough to hold the data that is most often retrieved by users.
@CALCMODE: BSO cube calculations have four execution modes. Actually, it’s two different types of modes, and each of them has two choices. Essbase reads your Calc Script and Outline and decides which modes to use, without you having to tell it. But Essbase doesn’t always get it right, so you can force optimal performance by setting the modes using the @CALCMODE calculation function.
@CALCMODE – Cell vs. Block: Cell mode makes Essbase perform each calculation serially and the full power of the processor will not be used. Conversely, Block mode allows simultaneous calculations. Certain calculations need to be done sequentially to assure correct results however at times Essbase chooses Cell mode when it may not be necessary. Where calculations can be done simultaneously, Block mode can assure faster performance.
@CALCMODE – BottomUp vs. TopDown: BottomUp is the default mode and the most efficient, however Essbase may determine that certain complex Outline member formulas should be executed in TopDown mode to derive correct results. However TopDown may be less efficient because it considers the potential data blocks rather than just the blocks that exist. If the TopDown method is not required to get the right result, forcing BottomUp could show significantly better performance when the database is aggregated.
Parallel Calculations: Taking advantage of parallel processing can seriously reduce calculation times. Using SET CALCPARALLEL in your script enables the calculation effort to be divided among a specified number of threads. Using FIXPARALLEL … ENDFIXPARALLEL allows for very precise control of parallel calculations and can be advantageous when using certain commands and functions such as DATACOPY, CLEARBLOCK and @XREF, and in other circumstances.
Trying any of these ideas to optimize your cube takes experimentation and validation, and keep in mind that combinations of different settings and techniques can give you varying performance results. Whatever you do, experiment in your Development environment, not in Production! A tip: It’s helpful to use SET MSG DETAIL in your Calc Scripts to capture detailed messages to help you understand what’s going on when you test.
A caveat worth mentioning is that when you’re optimizing a cube’s allocations of memory and processor power, you need to do so in consideration of other cubes running on the same server at the same time. They all have to share the same physical resources, so be sure to balance out everyone’s needs.
Doug Pearce is Principal Consultant at Analysis Team, Inc. (www.AnalysisTeam.com) and an Oracle Essbase Certified Implementation Specialist.
Read other articles in this series: