Is My Essbase Calculation Seeing Deja Vu All Over Again?

, ,

Everybody knows the quickest way from point A to point B is a straight line.  Everybody assumes that the path is traveled only one time – not back and forth, over and over again.  I see a lot of Essbase calculations and business rules, from experienced and novice developers, that go from point A to point B taking a straight line.  But, the calculation travels that line multiple times and is terribly inefficient.

Here is a simple example of a calculation.  Assume the Account dimension is dense, and the following members are all members in the Account dimension.  We will also assume there is a reason to store these values rather than making them dynamic calc member formulas.  Most of these are embedded in a FIX statement so the calculation only executes on the appropriate blocks.  To minimize confusion, this will not be added to the example.

Average Balance = (Beginning Balance  Ending Balance)  / 2;
Average Headcount = (Beginning Headcount   Ending Headcount) / 2;
Salaries = Average Headcount * Average Salaries;
Taxes = Gross Income * Tax Rate;

One of the staples of writing an effective calculation is to minimize the number of times a single block is opened, updated, and closed.  Think of a block as a spreadsheet, with accounts in the rows, and the periods in the columns.  If 100 spreadsheets had to be updated, the most efficient way to update them would be to open one, update the four accounts above, then save and close the spreadsheet (rather than opening/editing/closing each spreadsheet 4 different times for each account).

I will preface by stating the following can respond differently in different version.  The 11.1.x admin guide specifically states the following is not accurate.  Due to the inconsistencies I have experienced, I always play it safe and assume the following regardless of the version.

You might be surprised to know that the example above passes through every block four times.  First, it will pass through all the blocks and calculate Average Balance.  It will then go back and pass through the same blocks again, calculating Average Headcount.   This will occur two more times for Salaries and Taxes.  This is, theoretically, almost 4 times slower than passing through the blocks once.

The solution is very simple.  Simply place parenthesis around the calculations.

(
Average Balance = (Beginning Balance  Ending Balance)  / 2;
Average Headcount = (Beginning Headcount   Ending Headcount) / 2;
Salaries = Average Headcount * Average Salaries;
Taxes = Gross Income * Tax Rate;
)

This will force all four accounts to be calculated at the same time.  The block will be opened, all four accounts will be calculated and the block will be saved.

If you are new to this concept, you probably have done this without even knowing you were doing it.  When an IF statement is written, what follows the anchor?  An open parenthesis.  And, the ENDIF is followed by a close parenthesis.  There is your block!

"East"
(IF(@ISMBR("East"))
    "East" = "East" * 1.1;
ENDIF)

I have seen this very simple change drastically improve calculations.  Go back to a calculation that can use blocks and test it.  I bet you will be very pleased with the improvement.

 
/0 Comments/by

Unique Essbase Customizations Using Java – Custom Logging

, , , ,

Introduction

Many companies have in depth working knowledge of Hyperion Essbase and are looking to enhance their enterprise reporting capabilities to the next level. Companies typically have specific processes and calculations that set them apart in their industry. However, they are often limited to basic reporting capabilities provided by the standard functions in Essbase. Additionally, complex operations can quickly become arduous using Calculation Scripts and Business Rules. This post will demonstrate the how to easily build Custom Java Routines to extend Essbase and dramatically reduce development time.

Complete details will be provided on how to implement a simple customized logging function for use in Calculation Scripts and Business Rules. Essbase’s streamlined, parallel nature makes it difficult for application developers to trace line by line. By using Java to implement a custom logging routine, one may use personalized log entries within their Essbase scripts. Consequently, developers can add tracing to their scripts and quickly determine how Essbase is approaching each calculation. Accordingly, application developers are able to see exactly how the script is being executed – providing quick debugging and faster development time. One powerful feature is to help determine block creation  within FIX statements.

The first step to integrating a custom Java routine into Essbase is to write some simple Java code. It is very easy – the code does not have to include any special APIs for Essbase.  During development, a few issues were encountered where Essbase was a bit picky about how the code is written.  Here are a few tips to help in getting started. These tips were gathered while doing real development, and it is best to follow at first, though you may revisit the items and find out what will work for you.

  • Do not include the code in a package such as “com.company.product_name” – remove the “package” declarative at the top of the code
  • Do not use the keyword “this” to refer to variables
  • Do not overload methods
  • Set all methods and variables to static

With these provisions in mind, the following code can be written to implement a custom logging routine.

CustomLoggerV2.java

import java.io.FileWriter;
import java.io.Writer;
import java.io.PrintWriter;
import java.io.StringWriter;
import java.util.Calendar;
import java.util.ArrayList;

public class CustomLoggerV2
{

    private static String logFile;
   
    public static int logFilterLevel;
   
    public static void setLogFilterLevel(int logFilterLevel2)
    {
        logFilterLevel = logFilterLevel2;
    }
   
    public static void setLogFilename(String logFilename)
    {
        logFilterLevel = 0;
        logFile = logFilename ;
    }

    public static synchronized void customLog (int logLevel, String message)
    {
        log(logLevel, message);
    }

    private static synchronized void log (int logLevel, String message)
    {
       
       
        // do not log
        if (logLevel < logFilterLevel)
            {
                return ;
            }
       
        try {
       
            Calendar c = Calendar.getInstance();
           
            FileWriter fw = new FileWriter(logFile, true);
               
            fw.write(c.getTime()   ": "   message   "\n");
            fw.close();
             
        } catch (Exception e)
            {
                System.out.println("Error, cannot open , "   logFile);
                e.printStackTrace();
            }
        }

 
}

The code implements three public methods:

  • setLogFilterLevel(int logFilterLevel) – sets the minimum message level to log (think about ERROR=100, WARN=90, INFO=70, DEBUG=0) – so you can easily change the verbosity of the output.
  • setLogFilename(String filename) – The full path to the log file you wish to use
  • customLog(logLevel, String message) – The log message, with its indicated priority

The next step is to package up the code above. It is important to use the same version of Java which is running your Essbase instance. To find the version, look for the JRE being used within the environment, for instance, Hyperion\common\JRE\Sun\1.5.0\bin. To obtain the specific revision, open a cmd prompt, cd to the bin directory, and run “java –version”.

E:\Hyperion\common\JRE\Sun\1.5.0\bin>java -versi
java version "1.5.0_11"

Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_11-b03)

Java HotSpot(TM) Client VM (build 1.5.0_11-b03, mixed mode

To compile the code a JDK is required, which will contain the javac command. Hyperion only packages the JRE, meaning you will have to download the correct JDK in order to compile the code. You can find older versions of Java JDK from Oracle(Sun)’s web site. Once you have obtained the correct version of the JDK, compile and package up the code:

javac CustomLoggerV2.java

jar -cf CustomLoggerV2.jar CustomLoggerV2.class

Next, copy the CustomLoggerV2.jar file into the Essbase file structure:

Copy CustomLoggerV2.jar into the E:\Hyperion\products\Essbase\EssbaseServer\java\udf folder. If the udf folder does not already exist, create it.

Now it is time to start including the Java class within Essbase. Essbase runs within its own JVM and therefore has its own Java security. In the example above, we are writing to a local log file, which will violate the default security policy setup in the udf.policy file. The file is usually found in Hyperion\products\Essbase\EssbaseServer\java . The simplest way to get around the security concerns for development purposes is to remove the comment from the last line in the file, which effectively includes the directive “permission java.security.AllPermission”

…

permission java.util.PropertyPermission “java.vm.version”, “read”;

permission java.util.PropertyPermission “java.vm.vendor”, “read”;

permission java.util.PropertyPermission “java.vm.name”, “read”;

// Uncomment the following line if you want to remove all restrictions

permission java.security.AllPermission;

};

Now that the Essbase security and jar file have been put in, a restart of the Essbase process is required to register the changes. Please restart Essbase now.

The final step is to run some maxl statements to register the public java methods with Essbase.

CustomLoggerV2.mxl

create or replace function '@JCustomLoggerV2_setLogFilename'

as 'CustomLoggerV2.setLogFilename(String)'

spec '@JCustomLoggerV2.setLogFilename(absolute file name)'

comment 'Nicholas King'

with property runtime;


create or replace function '@JCustomLoggerV2_customLog'

as 'CustomLoggerV2.customLog(int, String)'

spec '@JCustomLoggerV2.customLog(log level, log message)'

comment 'Nicholas King'

with property runtime;


create or replace function '@JCustomLoggerV2_setLogFilterLevel'

as 'CustomLoggerV2.setLogFilterLevel(int)'

spec '@JCustomLoggerV2.setLogFilterLevel(filter level)'

comment 'Nicholas King'

with property runtime;


One final thing… In order to run a custom java function, the value of the result has to be stored in an Essbase member. This is true even if there is not any use for the return value, such as this case where there is no value returned from the Java methods. To get around this, create a new Essbase member called “No Measure” somewhere within your Essbase outline. This will act as a dummy member intended only to direct the return value, if any, of the Java methods. An example is shown below.


Sample Calc Script or Business Rule to Invoke the Logger


//ESS_LOCALE English_UnitedStates.Latin1@Binary

/* SETUP The Logger */

/* Fix on something so it runs only once */

FIX (Actual, Texas, "100-10")

"No Measure" = @JCustomLoggerV2_setLogFilename("E:\CustomEssbaseLog.log");

"No Measure" = @JCustomLoggerV2_setLogFilterLevel(50);

ENDFIX;

/* In your script, do some actual logging */

FIX (Actual, Texas, "100-10")

/* Won’t be displayed */

"No Measure" = @JCustomLoggerV2_customLog(0, "This is a debug message");

/* Will be displayed */

"No Measure" = @JCustomLoggerV2_customLog(50, "This is a normal message");

"No Measure" = @JCustomLoggerV2_customLog(100, "This is an important message");

ENDFIX;


The result of running the script is the log entries will be added to the log file E:\CustomEssbaseLog.log,


Mon Feb 21 01:30:25 EST 2011: This is a normal message

Mon Feb 21 01:30:25 EST 2011: This is an important message


Troubleshooting Tips


A very common error you may receive is,


Error: 1200324 Error compiling formula for [No Measure] (line 8): operator expected after [@JCustomLogger_customLog]


This error is a generic error that indicates something in your custom function is not registered properly.  Unfortunately, there is not a lot of detailed log information at this point to help discover the problem. If you receive this message a few things might help:


    

  • Retrace your steps – carefully review all instructions above
    • 

  • Check that the correct version of Java was used to compile the class file and package the jar
    • 

  • Check the jar is in the correct “udf” folder in Essbase
    • 

  • Check the syntax of the MAXL to register the functions is correct
    • 

  • Simplify your script as much as possible to reduce the possibility of syntax errors
    • 



Conclusion


This example shows how to create a custom Java based logger integrated into Essbase. The possibilities are endless – anything that can be done in Java can be added to Essbase. You can create development aids, or even read/modify the values within the cube. For instance, this model has successfully been used to perform complex financial calculations within Hyperion Planning Forms using Business Rules.  It could also be used for integrating Web Services with your cube by reading or writing cube data and interacting with an enterprise Web Service.


 
/0 Comments/by 
Dynamically Referencing Members in Business Rules
, , , 
Often times with a Hyperion Essbase or Planning application, an allocation of data will be required.  Many times, the allocation is simply moving data from one member to another.  When the number of members involved is large, developing the script can be time consuming.  When the members frequently change, the maintenance of the calculation can be a nuisance.


When the members involved in the allocation are similar on both sides (the from and the to), the following method can be employed to speed the development and limit, or eliminate, any maintenance required.


Requirement


The application has 50 members in which the data needs to be moved.  The data originates from an account coming from the general ledger.  The data needs to be moved to a new member that doesn’t exist in the chart of accounts.  The new member will exist in a different part of the hierarchy.


Solution


The first step is to create a corresponding member for each of the 50 accounts that need allocated.  These accounts will be identical to the original 50, except they will be prefixed with a “D” identifying them as a dummy, or made up, account.  Each of these new accounts will have a UDA of “allocation.”  The prefix of the member and the UDA are not critical.  They will likely be something more meaningful to the requirements.






GL Acct
  Dummy Account




500345
  D500345




500578
  D500578




607878
  D607878






Once the hierarchy is ready to handle the allocation, the following function can be used.  In layman’s terms, this only executes on the new members added (identified by the unique UDA) and makes them equal to the corresponding member without the added prefix.  We will assume that this is being executed on a scenario that equals “Actuals.”


FIX(@UDA(“allocation”))

/* Make the new member equal to the old member */

“Actuals” = @MEMBER ( @SUBSTRING ( @NAME(@CURRMBR(“Account”)) , 1));

/* Clear the old member */

@MEMBER ( @SUBSTRING ( @NAME(@CURRMBR(“Account”)) , 1)) = #Missing;

ENDFIX


Let’s assume that the UDA is NOT added to the new, or dummy, member.  If the UDA is on the originating member, the calculation would look like this.


FIX(@UDA(“allocation”))

/* Make the new member equal to the old member */

@MEMBER ( @CONCATENATE(“D”,@NAME(@CURRMBR(“Account”)))) = “Actuals”;

/* Clear the old member */

Actuals = #Missing;

ENDFIX


Now we can break down these functions. Remember, the calculations loop through all members in all dimensions.  In this example, setting the result equal to “Actuals” is simply making the account that the calculation is looking at, at that particular point in the loop, equal to whatever is on the other side of the equation.


@MEMBER ( @SUBSTRING ( @NAME(@CURRMBR(“Account”)) , 1))

There are four functions used in this string.


    

  • @MEMBER will convert a string to a member name
    • 

  • @SUBSTRING requires 2 parameters (3 optional).  The first is the larger string from which you want to take a smaller string.  The second is where to start, with 0 being the first character.  The third is how many characters to include.  If this is left blank, it will take all the characters to the right of the second parameter.
    • 

  • @NAME will convert a member to a string.
    • 

  • @CURRMBR gets the current member of a specified dimension.
    • 



Putting this all together, this calculation (from inside out) is getting the current member of the Accounts dimension (“d345678”).  It converts that member to a string.  It takes all the characters to the right of the first character (“345678”).  Then it converts the string back to a member.  At this point, we can set that member equal to something.


@MEMBER ( @CONCATENATE(“D”,@NAME(@CURRMBR(“Account”))))

The functions here are the same as above, except we are not removing the “d.”  We are adding it.


    

  • @CONCATENATE accepts two parameters and will combine those two in to one string
    • 



Putting this all together, this calculation (from inside out) is concatenating two strings, a “D” and the current member of the Accounts dimension (“d345678”).  It then converts the string to a member. At this point, we can set that member equal to something.


Benefits


By using these functions, the calculations can be much smaller, quicker to develop, and completely maintained by the outline.  This effectively gives the user community ownership on the maintenance.


 
/7 Comments/by 
Sparse, Dense, and Blocks For Dummies
, , , , , 
When I am introduced to business segments that use Hyperion Essbase, I always get asked the same question: “Can you explain what sparse and dense mean?”  Although I agree that users don’t HAVE to understand the concept, I contend that it is extremely valuable if they do.  It will not only help them become more efficient users, it goes a long way in helping them understand why something simple in Excel isn’t always simple in Essbase.  If users understand what a block is, and what it represents, they have a much better experience with Essbase.


If you are a relational database developer or a spreadsheet user, you tend to view data in 2 dimensions.  An X and Y axis is equivalent to the rows and columns in your spreadsheet or database table.  Essbase is a little different in that it stores data in 3 dimensions, like a Rubik’s Cube, so it has a Z axis.  Essbase databases refer to these “Rubik’s Cubes” as blocks.  An Essbase database isn’t one giant Rubik’s Cube; it could be millions of them.  The size and number of possible blocks a database has is determined by the sparse/dense configuration of the database.


An Essbase outline has a number of dimensions.  The number of dimensions can range in quantity and size, but each dimension is identified as a dense or sparse dimension.  The dense dimensions define how large each block will be in size (the number of rows, columns and the depth of the Z axis).  The sparse dimensions define the number of possible blocks the database may hold.  Assume the following scenario:  a database exists with 3 dense dimensions and 2 sparse dimensions.  The dense dimensions are as follows:


Net Income

Income

Expenses


Qtr 1

Jan

Feb

Mar


Version

~ Actual

~ Budget

~ Forecast


Remember, the dense dimensions define the size of blocks.  These dimensions would produce a block that looks like the image below.  Every block in the database would be the same.


For those more knowledgeable with Essbase design, this example assumes that no member is dynamically calculated or is tagged as a label to reduce complexity.


 


The sparse dimensions are below.


Total Product

Shirts

Pants


Total Region

North

South

East

West


The unique combinations of each sparse dimension has its own block.  There will be a block for Pants – North, one for Shirts – North, and so on.  Since there are 3 members in the Total Products dimension and 5 members in the Total Region dimension, there will be a total of 15 (3 x 5) blocks.  If a database has 5 sparse dimensions, all with 10 members, it would have a total possible number of blocks equal to 100,000 (10 x 10 x 10 x 10 x 10).  Below is a representation of the possible blocks for Shirts.




 
/0 Comments/by 
The Good, The Bad, and The Dirty Calc
, , , 




Executing calculations that only run on blocks that have changed is a great feature in Essbase.  It enables administrators to calculate the database in a fraction of the time and is referred to as calculating “dirty” blocks, or an update calc.  This is awesome.  “Why shouldn’t I use it all the time?” you might ask.  Understanding how the Essbase calc engine works is critical to answering this question.


The Essbase calc engine calculates each block in a specific order (see figure 1).  The first block it calculates is the first level 0 block of the first sparse dimension.  It then traverses to higher levels and moves through the dimension from top to bottom until the entire dimension is consolidated.










When a level 0 block is changed, it and all of its parents, are tagged as dirty (it needs to be calculated again). When a calculation is executed on just dirty blocks, the process is the same except that it skips all the “clean” blocks.  Once the block is calculated the dirty tag is changed to clean.  So far, so good!


Revisit figure 1, which is a very simple example.  It shows a very simple hierarchy with the order in which the blocks are calculated, 1 through 10.


Figure 2 shows what happens if New York is updated.  Blocks 5, 6, and 10 are tagged as dirty.  The next calculation, if set to calculate only the dirty blocks, would only calculate blocks 5, 6, and 10, in that order.










Here is where things get a little ugly.  When an application has write access, as a planning or forecasting application would, it is very possible that users are updating data DURING the calculation process.  The timing of these events is critical to understand why calculating only dirty blocks can cause inconsistencies.


When a calculation has started, it identifies which blocks need calculated (5, 6, and 10 in this example).  Immediately after that, it starts calculating block 5.  If Texas is updated while block 5 is being calculated, what happens?


Figure 3 shows the state of the clean/dirty blocks when the calculation is finished with block 5.  It is exactly what you might expect at this point.  Blocks 6 and 10 are still dirty.  The update of Texas caused Blocks 1, 3, and 10 to be tagged dirty.










This is the critical piece.  Keep in mind how the calculation engine works.  It will continue to calculate blocks 6 and 10.  Also note that the calculation running does NOT reevaluate what needs calculated.  It will not calculate blocks 1 and 3.


Figure 4 shows the state of the blocks after the calculation finishes.  Only blocks 1 and 3 are dirty at this point because 10 was included in the calculation.










When the next calculation is executed, the only blocks that are dirty are 1 and 3.  Can you see the problem now?  After blocks 1 and 3 are calculated, is block 10 accurate?  Does U.S. equal the total of South, East, and West?  Unfortunately, it does not.


One could argue that it will get updated the next time data is changed.  In a very simple example with 3 levels, this would probably correct itself rather quickly, if the problem happened at all.  In a more realistic example where a company has 10 or 20 levels in their organization dimension, the problem is likely to be a reoccurring problem and may not be corrected until a full calculation is executed.  In most situations, it is not acceptable to have a database where it consolidates correctly only some of the time without any warning that it is not accurate.  Reporting can be incorrect, and bad management decisions can result.


Using the dirty calc feature is a great tool to have in your arsenal.  It can save hours of processing time.  It can make you look like a genius.  Without understanding its pitfalls, it can be the source of countless wasted hours trying to figure out why a cube isn’t consolidating correctly.  A worst case scenario is when a cost center manager updated their budget, it never gets consolidated correctly, and the problem isn’t identified until it is too late.












 


 
/1 Comment/by