Year End Processes in Hyperion Essbase

This blog is aimed at business users of Essbase focusing on Year End processes in Essbase.

To start with remember it is important to document what you are doing in detail for the process to be repeated exactly and easily in future years.

Generally things that you should think about are:-

• When to do the process?  Speak to all interested parties and get a time to do this when users will be least impacted, after the initial set up the process may only take a few hours, but the time depends greatly on the particular databases.  Where possible take backup copies before any changes are applied.
• When you add the new year to the time dimension, consider size implications especially if it is a dense dimension.  Check server space, you should always have at the very least enough free space for a copy of the database, but usually much more than this, as the database will grow through the year with the monthly (weekly/daily/hourly) loads.  The exact process and any copy calculations etc. will depend on the particular databases.
• Alias names may need to be updated.  Alias names are generally the names Business Users will use.  So for example in the database a member may be called CY to stand for Current Year and the alias is set to 2013 so Business Users will see that.  Variance member aliases can easily be overlooked and depending on the set up you may need to change the calculations on these members.
• What data do you want going forward? Often only this year and last year are regularly used so consider only keeping these in your live database and creating an archive version for historical data.  Also having a version containing just the 2 years, and another version with all data in is usually more efficient.  You can run through the regular load processes quickly on the 2 year datasheet and when the data is all loaded and correct, you can update the larger one.
• Year end is a good time to consider tidying up structures.  Removing unused members, especially in dense dimensions can have a positive effect on performance, but the main benefit is it makes the system easier for Business Users to browse and understand.  Do keep in mind that removing the members may affect historic data.  Just moving the members to an area called “Old” or “unused” will not release space, but can still help Business Users and also highlight incorrect future entries.
• Substitution Variables usually need to be updated during the year end process as they are often set to years.  It can be a good time to consider using these if they are not being used, or to add more.

o   What are Substitution Variables and how they are used?  According to the Essbase Database Administrators Guide “Substitution variables are global placeholders for regularly changing information”.  Using examples is easiest to see how they are used: We could create a Variable called ThisYear and set it to the value 2013.  Then when we want to use it, usually in a calc script we type &this year (Starting with an & tells Essbase you are using the Variable). When the script is run it treats &ThisYear as if we had typed 2013.  There will probably be dozens of places in the calc script where  &ThisYear is used, and this same Variable may also be used in other calc scripts, load rules, report scripts, member formulas etc.  When the year changes it is easy to change the value of &ThisYear.  Making this one change effectively updates all the calc scripts etc.  Any time you manually change scripts on a regular basis consider using a Substitution Variable.  Substitution Variables are often used in the regular load and calc processes.

 At year end also consider reports that will be produced in the new year and check that the year will be correct everywhere it is shown (remember headers and footers) as it can be easy to send out a report with the correct figures but the wrong year shown on it.  When correcting any instances of this try and do so in a way that means it is not required to be done in future years, so use member names that will change alias etc.

There are many other things you may need to consider during year end, such as Load Rules, SQL queries etc.  This blog is not intended to cover everything but to introduce some ideas to guide you.