Consultants Corner Aug '09

31st Aug 2009

In the Consultant's Corner this month is Alan Davey. Alan has been with M-Power for 3 ½ years.  Based in Perth, Alan is currently working with the Australian Finance Group, Tiwest and Argyle Diamonds, where he has recently spent time tuning Essbase models & calculations.

“Our research tells us that extended calculation times are the No.1 challenge with Essbase models.  This is partially due to natural growth in data volumes over time, but also because most users demand more functionality once they recognise the powerful analytical capability that Essbase provides.  Continual use, development and improvement of models will almost certainly result in calculation time increases.”

“Tuning therefore plays an important role in maintaining and improving performance. It is surprising how big an impact just a few tuning tweaks can make – 300% plus improvements are not uncommon, even on newer models.”

"Unfortunately, there is no single answer when it comes to Essbase tuning, it's essentially a balancing act - for example techniques that reduce calculation times can increase data retrieval times. Each database is unique and has different user requirements, the art is in understanding these requirements and achieving the right balance for each database.”

“There are a huge number of factors that impact calculation performance. So where do you start? To simplify the process, I have found it useful to assess each database under 4 separate categories;

• ‘Administrative control’ means maintaining central control over what is calculated. An example is the years that are calculated… many companies perform a long range forecast / budget on an annual basis, and then supplement with interim forecast updates for the current year only.  If the calculated years are not managed effectively, then the user is forced to wait for all future years to be recalculated when they are only interested in the current year. This type of administrative control is often eroded over time through staff turnover, often the source of a big win to the performance tuner.
• When investigating ‘database size’, look at areas such as legacy data (Do you really still need all 5 drafts of your 2005 budget?) and whether best use has been made of the dynamic calculation capability.
• For ‘complexity of functionality’, bear in mind it is not necessarily the longest formulae that take the longest to calculate. Break down the key calculation scripts into small steps and run them individually to help identify which parts of the model take time to calculate, then assess whether those steps could be simplified.  In older databases, it is common for original functionality to become obsolete, in which case it can be removed altogether.
• ‘Design efficiency’ covers several aspects including dimension order, dense / sparse configuration, optimising cache settings, etc. An important point here is ensuring that data is calculated once only, and minimising the use of resource intensive functionality such as @SUMRANGE or @XREF.”

“Performance tuning is amongst the most challenging aspects of my role at M-Power but also one of the most rewarding given the significant improvements we’ve been able to deliver.”

Thanks for that Alan.

Please call us for more information on how our performance tuning services can benefit you.