Main menu

Hyperion Essbase User Defined Functions (UDF) Java Package

Hyperion Planning 9.3.1 ValidateData.js custom buttons

I. What is all about

Below is a java package (created for j2se 1.4.2+) to complement Oracle-Hyperion Essbase function set with support for Strings, Dates and Process execution. Especially lack of proper strings support I found it very frustrating when creating complex calculation scripts, so I created a simple wrapper package for standard JDK classes that might come in handy for anybody that uses Essbase a lot.

In Oracle Hyperion System 9 (up to version 9.3.1 at least, which is the one I am using) the only "out of the box" string functions are @CONCATENATE, @SUBSTRING and @NAME and they are under "Miscellaneous" functions category (pretty much shows the interest of Hyperion for strings support right ?). Essbase will treat any string in a calculation script as a member name and will try to get its value in the current calculation context (current fix for example), which will result either in an error if there is no member in the outline by that name, or in a calculation with results you are not expecting to happen. There is no way I am aware of to compare (in case you need it) strings in Essbase.

In short, the following test

If(string1 == string2)
  /* do something */
Endif;

Will not compare strings, but will compare the values of the members "string1" and "string2" if they exist in the outline. Essbase supports only numeric comparison, so everything must be denominated to numbers in order to work.

In order to make this comparison behave as in any other scripting language you would need a UDF (user defined function) and do something like this:

If(@udf_compare_strings(string1, string2)==0)
  /* do something */
Endif;

Or

If(@udf_compare_strings(@name(member1), @name(member2))==0)
  /* do something */
Endif;

The last format comes in very handy when member2 for example is a variable of type Member in a business rule and you want to compare it with current member in the same dimension:

If(@udf_compare_strings(@name(@currmbr(Dimension)), @name(member_variable))==0)
  /* do something */
Endif;

II. Downloads

Here it is all you need to install these functions in your Hyperion Essbase deployment:

  1. Java package (click here). If your browser saves this as a .zip file, rename in .jar.
  2. Installation files (click here).
  3. Online documentation in javadoc format. Here is the offline version (click here).
  4. Package source files (click here) you don't actually need these, but if you are curious ...
  5. Essbase sample application (click here).
  6. Sample udf.policy file.

Regarding copyright, all these downloads are released under GNU general public license, which in a nutshell means free for all, no guarantees (this includes that I don't assume any responsability from the use of this code), please give me credit if you use it, but I won't kill you if you don't. Cheers and happy coding!

III. Installation steps

So, here is what you need to do:

  1. Prepare package: Copy dragosmatachescu.jar in your Essbase UDF directory. In a normal Next Next Next installation it should be c:\Hyperion\AnalyticServices\java\udf\, otherwise is %HYPERION_HOME%\AnalyticServices\java\udf\
  2. Prepare security: If you plan to use the process execution functions you need to give more java execution rights to this package. Either you give to all UDF functions by un-commenting the line
    grant {
    ...
    permission java.security.AllPermission;
    };
    
    in the default udf.policy file (found under %HYPERION_HOME%\AnalyticServices\java\ directory)
    or you add specific line for dragosmatachescu.jar at the end of udf.policy file
    grant codeBase "file:${essbase.java.home}/../java/udf/dragosmatachescu.jar" {
    permission java.security.AllPermission;
    };
    
    Need to restart Essbase (Analytic Services) after this modification.
  3. Deploy: Modify the installation files found in install.zip (register_jreFunctions_Calendar.bat, register_jreFunctions_Runtime.bat, register_jreFunctions_String.bat, unregister_jreFunctions_Calendar.bat, unregister_jreFunctions_Runtime.bat, unregister_jreFunctions_String.bat) and replace the generic parameters with values according to your installation (Essbase administrator account, password, server and application). These UDF functions will be registered per application, if you want per server look into Essbase documentation how to do this (you will need to create new installation .mxl files for this). Use the bat files to register/unregister the functions. If you install per application, you need to restart the application after such operation, if you install per server, need to restart Essbase service.
  4. Test installation: If everything was OK, if you open a calculation script in your target application you should see under "Commands and Functions" -> "User Defined Functions" the list of @JCalendar_*, @JString_*, @JRuntime_* functions.

Below is a list of Essbase UDF functions created by this installation:

Calendar / Date functions:

@JCalendar_add_currentDate, @JCalendar_add_customDate, @JCalendar_after_currentDate, @JCalendar_after_customDate, @JCalendar_before_currentDate, @JCalendar_before_customDate, @JCalendar_equals_currentDate, @JCalendar_equals_customDate, @JCalendar_get_currentDate, @JCalendar_get_customDate, @JCalendar_getActualMaximum_currentDate, @JCalendar_getActualMaximum_customDate, @JCalendar_getActualMinimum_currentDate, @JCalendar_getActualMinimum_customDate, @JCalendar_roll_currentDate, @JCalendar_roll_customDate

Process execution functions:

@JRuntime_execCurrentDir, @JRuntime_exec

String functions:

@JString_compareTo, @JString_compareToIgnoreCase, @JString_concat, @JString_endsWith, @JString_equals, @JString_equalsIgnoreCase, @JString_indexOf, @JString_lastIndexOf, @JString_length, @JString_matches, @JString_regionMatches, @JString_replaceAll, @JString_replaceFirst, @JString_split, @JString_startsWith, @JString_substring, @JString_toLowerCase, @JString_toUpperCase, @JString_trim, @JString_BooleanToString, @JString_DoubleToString, @JString_IntToString, @JString_LongToString, @JString_StringToBoolean, @JString_StringToDouble, @JString_StringToInt, @JString_StringToLong

IV. Sample application

In order to install the sample application provided in the downloads section, follow these steps:

  1. Create a block storage Essbase application called "test" and a database "test" inside it.
  2. Copy testJRE.csc and test.otl under %HYPERION_HOME%\AnalyticServices\app\test\test\ and restart the "test" database.
  3. Execute the testJRE.csc script and use the file "createblock.xls" provided to connect to test database and follow execution results

V. Contact

If you really have to ...



Links

Some useful links:

JDK javadocs
Oracle Essbase forum

Copyright © 2008 www.dragosmatachescu.com.