Essbase best practices for a good performing solution

Essbase best practices to ensure a fast planning solution

In “design guidelines” by Arthur van den Berg

In this post I will list the Essbase best practices to make a smooth running Hyperion Planning / Oracle PBCS implementation. Enjoy and please use them!

many implementations are just plain wrong

There are many ways of implementing business requirements into Essbase – read Hyperion Planning – but most of them are plain wrong. I can say this with certainty, as I have been asked to rescue and redesign many sluggish Essbase implementations over the years. At each particular malfunctioning implementation, I enforced these Essbase best practices and the result was a smooth-running engine. The result: all performance issues solved, an implementation partner that should be a bit wiser and a happy client. I am happy to share these best practices with you, so you can build and design good planning solutions from the start.

sharing proven Essbase best practices

After fiddling around for a couple of years with Essbase in the beginning of my Essbase career I stumbled upon this very specific Essbase design pattern when I joined a group of seasoned Essbase performance optimizers in The Netherlands. Maybe they invented the pattern, maybe they copied it and perfected it. I really do not know. But what I do know, is that this design pattern has proven to be the “golden standard” for Hyperion Planning solutions tuned for good performance.

This pattern has been copied among lots of consultants in Europe, but I still see and discover that many of them do not really understand the pattern as I see the pattern being implemented incorrectly. The consultants that do understand the value of this pattern keep their mouth shut as it was one of the great secrets of the world … only if they could, which of course is not possible. In my opinion, good design and best practices should not be a secret, they should be shared and taught to improve the overall quality and business success when using Oracle’s EPM solutions.

the Dutch School on Essbase Design (DSED)

In this blog posts and the next to follow, I want to tell you the ‘secrets’ of this design pattern listed as Essbase best practices for Hyperion Planning implementations (and PBCS); which I will from now on call the “Dutch School on Essbase Design”. I do this, because in “Essbase land” there are many expert Essbase consultants with each having their own opinion on how to best implement business requirements in Essbase. Each consultant has its own — most of the time — specific design pattern. But in all Essbase discussions on the internet on what is best in Essbase, the context of the design patterns used is lost; what is good for one pattern could be disastrous when using another design pattern. The result is you being confused with contradicting advise.

So, please keep in mind, I will discuss “The Dutch School on Essbase Design”. With ample proof that it works and brought relief when redesigning many existing sluggish Planning implementations. When you follow this design pattern you will ensure — for almost 95% — that your solution will be (1) fast, (2) enable the implementation of almost any financial requirement and (3) is easy to maintain.

So here we go.

the Essbase best practices revealed

What are the Essbase best practices for Planning, what is the “Dutch School on Essbase Design”? And let’s follow Oracle’s fondness of abbreviations and refer to it as the DSED.

The best-practices of the DSED for Hyperion Planning are:

the right design

  • Fully understand the business requirements before you start to build.
  • This can not be said enough! A good practice to capture the user requirements is to build  a prototype in Planning or PBCS first. I will tell you more about this later.

  • Make use of the available plan types to create specific sub-models and a master model.
  • Do not make one huge master database that covers all the required functionality. Or at least think about this thoroughly before you go this route. In almost all cases you win a lot by creating specific sub-models and transferring only the relevant data using the #XWRITE function.

optimized for performance

  • Set the Account and Time dimension always to “dense”.
  • Please don’t be creative on this one. I’ve seen some ugly mistakes. More on this in Essbase dense and sparse dimensions.

  • Add another dense dimension called “View”, with only one stored member called “PER”.
  • This dimension will act as an anchor dimension which will offer you very nice hooks for efficient business rules and optimal performance and replaces the functionality for the native “time intelligence”.

  • Apply the “hour-glass” or the “hour-glass on a stick” principle
  • The order of the dimensions determine the sequence in calculations and parallelization. These principles are guidelines from which the essence needs to be used. I will detail on that later.

  • Only store relevant data (user input, data load, result from business rules) on level 0.
  • This applies to bottum-up planning. This best practice offers loads of benefits varying from consistency to logic validation to easy backup and restore. I will detail more on this in a future blog post.

  • Set all sub-totals in the dense dimensions to “dynamic calc”.
  • I hope, this is an “open door” and you are already doing this. Please check also the dimension levels!

  • Take care that your cube outline is as such that the block size will be around 50k.
  • Although Oracle advises a broader range (somewhere between 8-100k) we find this size to be perfect.

maintenance friendly

  • Make the dimensions and business rules as such that they keep on working if new members are added.
  • I will add tips and tricks in seperate blog posts alter.

  • Make use of variables in business rules and forms.
  • This is an obvious one I already see present in most implementations.

  • Make use of template rules.
  • A nice “little” feature that allows you to maintain the logic in one place and re-use it in multiple rules (for example for budget and forecast calculations).

Efficient business rules

  • Proper use of FIX and IF’s.
  • A FIX defines the blocks to be parsed, an IF determines what to do with the values on the block. Make FIXes as small as possible and only on level 0 blocks!

  • Do not use SET CREATEONMISSINGBLK.
  • I only know of one reason to use this and that is to set a single switch-board parameter. In all other cases, please do not use this command as it is a potential performance killer. There are more smart ways to create blocks.

  • Avoid multiple passes through the same slices.
  • Similar to the FIX statement. Only set the FIX to the blocks (database slice) you want to calculate or aggregate.

  • Apply parallel calculation as much as possible.
  • Especially when aggregating parts of the database. Parallel is mostly better than sequential. More on this in a future blog.

  • Never use XREF.
  • Speaks for itself.

  • Do not create blocks using a COPY statement.
  • It drives me mad when I review implementations and I see this command to create data blocks and as a next step to clear the data! It is a performance killer. Never do that. There are alternatives.

These Essbase best practices work best when implemented all together. If you leave one best practice out, you might run into a sub-optimal solution (and I have seen a couple of examples of this). I will detail on each single best practice in the coming blog posts.

 

-----
about the author
mm

Arthur van den Berg

LinkedIn

I love the calculation power of Essbase and the financial problems you can solve with it. I have developed, implemented and improved dozens of EPM solutions with Essbase at its core and I love sharing what I learned with professionals that need to build and/or support these solutions.

share this post