By TopDown Team
January 26th, 2012
I was reading the biography of Albert Einstein—the most fascinating pieces are on how he developed some of his greatest theories by observing his natural surroundings. In one experiment, performed in his head rather than in a lab, he challenged Isaac Newton’s concept of space and time, in what eventually would become the Special theory of Relativity (STR). Einstein would picture in his mind such things as what would be like to ride alongside a light beam or a path the bullet takes being fired from a moving train. Anyone who is new to Essbase, might feel like he or she is reading the theory of relativity looking at the code or trying to de-mystify the Block Storage or Dense/Sparse settings, but Essbase calculations are thankfully not nearly as mysterious as the laws of nature. Many people have figured out how to translate spreadsheet like concepts to the multi-dimensionality of Essbase.
One of these is expressing Time and Periods in ways other than Months, Quarters or Years. Deriving fiscal YTD or MTD numbers out of Essbase is not always straightforward. Examples include adding a Rolling 12 or 18 months to your reports or simply to take a trend or moving average of actual data and plug it to the forecast—this is often more complicated when Period and Years are separate dimensions. Another is calculating social security taxes in a tax year when the fiscal years do not coincide with calendar years. Here is one example of using range functions to calculate Rolling 12 months:
I like to sometimes add a ‘Data’ dimension to my cubes when different iterations of data is needed such as Rolling months or scaled numbers (in thousands). I make this a Dense dimension with only one stored member, thus, the number of blocks is unchanged (but a bigger block size) and I then put member formulas in dynamically calculated siblings:
The member Rolling12 dynamically sums last 12 months using range function @SUMRANGE which could be written as
@SUMRANGE(“Input”, <StartMonth>:<EndMonth>) but I would have to add a bunch of IF/ENDIF statements for each month and Year combination as I roll through the year. So then I take the @SUMRANGE function and replace one of the variables with another very useful function, namely @CURRMBRRANGE which provides a dynamic list of months. This function can pick any number of members going forward and also backward from the level o members of the outline. So then I make a SUMRANGE for current year (going back 11 months, note the negative 11) as well as one (going forward to Dec note positive 11) for the prior year. This is nesting 2 functions, much like nesting 2 or more Excel formulas:
@SUMRANGE(“Input”->FY12”, @CURRMBRRANGE(“Period”, LEV, 0,-11,0)) +
@SUMRANGE(“Input”->”FY11″, @CURRMBRRANGE(“Period”, LEV, 0,+1,11));
I could off course replace @SUMRANGE with any other Financial or Statistical range function such as @AVGRANGE or @MOVAVG. The beauty of the function @CURRMBRRANGE is that it provides a moving range relative to the current member being calculated, much like the bullet Einstein imagined firing from a moving train, OK, maybe that is not the perfect analogy! Again note that function @CURRMBRRANGE on itself cannot calculate anything, but rather must be nested inside a function that does, such as @SUMRANGE. The Essbase Admin Guide provides a full listing of all functions, and lists all the related variables for each.
Another very useful range function is @XRANGE which returns a list of members from 2 specified single or cross-dimensional set of members, say period and years. I found this useful in situations where one might want to calculate a moving average over a specified time period, such as last three months:
Again @XRANGE is a member list so it must be nested within a calculator like @MOVAVG. There are a few considerations with regard to cross dimensional member s, the dimensions must be ordered the same and follow one another in the outline. The order of the variables matters, so for example, one cannot write
Now, there are a lot more fun Essbase functions that reconstruct mathematical or statistical models such as Linear Regression to fit past data into a line or a curve in order to project future. But trust me it is not as complex as the theory of relativity!