How to best do YTD calculations in Essbase
As we all know, Essbase is unmatched when it comes to performing complex calculations. I sometimes think the problem when doing member formulas or calculation scripts is not so much whether Essbase can actually do some horrendously difficult calculation, but rather choosing one of the various ways Essbase can do the task.
One example of this is something conceptually extremely simple, namely doing YTD calculations in Essbase. Before I go any further, I have to state that the built-in dynamic time series functionality in Essbase has never really done it for me. I prefer to create specific YTD members in Essbase, as I find that makes the usage in reports and analysis much easier.
So if we focus on YTD members, the question now becomes where do we put them? As these are related to the time aspect of your cube, it seems logical to plonk them into the time dimension. This is the route some of my colleagues prefer, I however do not like this, as I prefer to minimize maintenance of formulas. Assuming you have Month as your most granular time dimension level, then if you put YTD members in your time dimension, you have to create 12 new members with a member formula for each of them. And assuming you don’t make use of a separate Year dimension, but have all years in one time dimension, then you have to create a duplicate of all your months, each with their own YTD formula.
This approach obviously does not lessen maintenance. What it does offer though are easier formulas, because you specifically write a formula for each month, so you know exactly the logic for that month. In essence you hard-code the YTD logic for each. This is something else I don’t like, anything leaning towards hard-coding in Essbase probably means you are missing a more elegant, simpler solution.
The approach I like to take with YTD is to create a new dimension, let’s call it Periodicity for arguments sake. Assume for this example that years and months are in 2 separate dimensions, as you will see this approach will work even if they are not. Also assume the input data is monthly, and Essbase needs to calculate the YTD values. Then the Periodicity dimension will have only 2 members, Monthly and YTD. Monthly will be the default input member and will be stored, while YTD will be a dynamically calculated member (it could be stored as well for query performance sake, but to prove this approach we will make it YTD for now). The Periodicity member itself will be Label Only. This means the new dimension has only one stored member, and therefore will not increase the size of the cube. In addition you probably want to make it a dense dimension, especially if Time is a dense dimension.
So the issue now becomes what should the formula for YTD be? Do we have to test for each month in an IF statement and hard-code the formula per month? Definitely not, remember hard-coding is preferably to be avoided. If possible the same formula should be used across all months, irrespective of where in the year.
Luckily Essbase has some fancy built-in formulas which will make this easy, in this case specifically @CURRMBRRANGE provides the magic key to do this easily, in conjunction with @SUMRANGE. Assuming the Time dimension is called Periods, the months are at level 0, and the year dimension is called Years, the formula is:
@SUMRANGE(“Monthly”,@CURRMBRRANGE(“Periods”,LEV,0,,0));
This will always calculate the sum of months starting with the first month in your time dimension, and ending with the current month being calculated. This works because of the startoffset and endoffset settings for @CURRMBRRANGE. The ” (null value) for startoffset means start the range at the first member in the list, and the ’0′ for endoffset means stop at the current member being calculated.
The main benefit of doing YTD in a separate dimension, apart from the easy maintenance of just one formula, is that it makes it extremely easy for an end user to switch between YTD and Monthly, or do a YTD vs Monthly comparison. The user does not need to select specific YTD month members, they only need to select YTD or Monthly from the Periodicity dimension.
Managing your financial challenges with Enterprise Performance Management (EPM)
Remember when you were a kid and you were scared of ghosts and monsters or the boogeyman hiding in the closet? It’s funny … As we grow up, most of us lose our childhood fears, but all of us will certainly acquire new ones. Our mature fears tend to fall well within the bounds of realism and deviate from the realm of the impossible (although some grown men and women might still be afraid of Casper for all we know).
What scares us most often is that which CAN happen, that which IS possible – for instance, the loss of a loved one, loneliness, disappointment, inadequateness or the more tangible phobias such as heights or small spaces or having to live with the huge tarantula that shows up every few days for but an instant, only to disappear just as quickly, thus causing you to watch your every step.
As much as the above fears are apparent, there is one that is consistent with most of us, if not all of us – the fear of losing or not having money! According to British scientists, losing money or experiencing an economic loss (i.e. financial pain) has the same neurological roots to physical pain.
From a business or corporate or enterprise perspective, this “pain” is aggregated through the encompassment of fears of its employees. As an employer, how do you curb these fears? Well, the answer is fairly simple – you give them the assurance of a safe and secure future at your enterprise just as a parent would assure their child a pleasant night’s sleep by tucking them in or leaving the light on until they’ve fallen asleep.
In order to be in a position to offer these assurances however, your enterprise needs to make a profit. This is the bottom line to every business. The biggest profits are realized when every process, every individual, every team, and every department are working together toward that uniform goal. Managing this synergy can be a cumbersome task for any employer … but not anymore! Enterprise Performance Management (EPM) systems are here to help keep those business demons hidden in the closet for good!
EPM systems are a specific tier of the Business Intelligence (BI) toolset. Managers can now assess the performance of every component of their enterprise by understanding and analyzing where they’ve come from and where they currently stand (i.e. using the historical and current data of the business). Once this is done, a link can be formed between these operational activities and a business strategy that increases future profits. This is the goal of Performance Management – it gives you insight into the future so that you can cure your employees’ and consequently your enterprise’s fears of the present. Go ahead and turn your business’ lights on by investing in an EPM tool – knowing is always better than not knowing!
“I am not afraid of tomorrow, for I have seen yesterday and I love today” – William Allen White
For more information on Oracle Hyperion Enterprise Performance Management products, see http://www.intellient.co.za/live/content.php?Item_ID=1142
So what does Essbase stand for?
Introduction
Before I answer the titular question, maybe a brief introduction as to why I feel myself qualified to blog on Essbase and everything related to this product. I am an IT professional working in the BI field since 1992, and with Essbase specifically since 1995. In this time I have done it all, from being a lowly BI developer on Commander EIS, to running consulting teams and trying to sell BI software. My main focus area is still Essbase (can we still use the Hyperion name?), and all related technologies.
A brief history of Essbase
Essbase is the granddaddy of OLAP (On-Line Analytical Processing) databases. As was stated in a ComputerWorld article entitled “The Top 10 IT innovations of the last decade” written a couple of years ago, Essbase was recognized as the OLAP technology which put OLAP databases on the BI map. It was introduced to the market around 1993, and has been responsible for thousands of cubes and eons of man-hours in development since then.
So what is this OLAP thing?
Essbase is an OLAP technology. The term “OLAP” was first coined by E.F. Codd in 1993 who defined the 12 laws of OLAP . This defined an entire industry. The core principle of OLAP technologies is multidimensionality, and how to quickly answer multidimensional queries. Essbase is a multidimensional database, which creates a physical cube of data, unlike relational OLAP technologies which depend on a relational database.
Essbase stands for…
To come back to the original question, Essbase stands for Extended Spreadsheet Database. The idea was to simulate thousands of spreadsheets linked by multidimensional relationships in a easy-to-access database.
This blog is the first of ,hopefully, many more on the topic of Essbase, Hyperion, Enterprise Performance Management, and general BI subjects. Let me know if you want me to cover specific topics – what I cannot answer I am sure my Intellient colleagues will be able to handle.
