Developing Custom-Defined Calculation Functions

This chapter explains how to develop custom-defined functions and use them in Hyperion Essbase formulas and calculation scripts. Custom-defined functions are written in the Java^TM programming language and enable you to create calculation functions not otherwise supported by the Hyperion Essbase calculation scripting language.

Hyperion Essbase does not provide tools for creating Java classes and archives. This chapter assumes that you have a compatible version of the Java Development Kit (JDK) and a text editor installed on the computer you will use to develop custom-defined functions. For more information about compatible versions of Java, see the Hyperion Essbase Installation Guide.

For more examples of custom-defined functions, see the online Technical Reference in the DOCS directory.

This chapter includes the following sections:

About Developing Custom-Defined Functions

There are five general steps required to develop and use custom-defined functions in Hyperion Essbase calculation scripts and formulas:

Developing custom-defined functions as Java classes
Compiling Java classes into Java Archive (.jar) files
Copying the .jar files to a Hyperion Essbase server
Registering the custom-defined functions
Writing calculation scripts and formulas to use the new functions

When developing and testing custom-defined functions, make sure to register and test new functions locally within a test application. In general, you should register custom-defined functions globally only after you have thoroughly tested them and are ready to use them as part of a production environment.

Hyperion Essbase requires that you have a security level of database designer or higher to create and manage custom-defined functions.

For more information about registering custom-defined functions, see Registering Custom-Defined Functions.

Naming Custom-Defined Functions

When you register a custom-defined function in Hyperion Essbase, you give the function a name. This name is used in calculation scripts and formulas and is distinct from the Java class and method name used by the function.

The names of custom-defined functions must start with an "@" symbol; for example, @MYFUNCTION. The rest of a function name can contain letters, numbers, and the following symbols: "@", "#", "$", and "_". Function names can not contain spaces. The names of custom-defined functions which are called only by custom-defined macros should start with "@_" to distinguish them from general use functions and macros.

Custom-defined functions must have unique names. Function names must be different from each other, from the names of custom-defined macros, and from the names of existing Hyperion Essbase calculation functions. If a Hyperion Essbase application contains a local function that has the same name as a global function, the local function is used for calculation.

For more information about registering custom-defined functions, see Registering Custom-Defined Functions.

Writing Java for Custom-Defined Functions

The basis of a custom-defined function is a Java class and method developed by a database administrator or Java programmer to perform a particular type of calculation. Creating and testing these Java classes and methods is the first step toward creating a custom-defined function.

You can create more than one method in a class for use as a custom-defined function. In general, it is recommended that you create all the methods you want to use as custom-defined functions in a single class. However, if you want to add new custom-defined functions that are not going to be used across all applications on a Hyperion Essbase server, create them in a new class and add them to the server in a separate .jar file.

When creating multiple Java classes that contain methods for use as custom-defined functions, verify that each class name is unique. Duplicate class names will cause methods in the duplicate class not to be recognized, and you will be unable to register those methods as custom-defined functions.

After creating the Java classes and methods for custom-defined functions, test them using test programs in Java. When you are satisfied with the output of the methods, install them on the server and register them in a single test application. Do not register functions globally for testing, because this makes it more difficult to update them if you find problems.

For more information about registering custom-defined functions, see Registering Custom-Defined Functions. For more information about updating custom-defined functions, see Updating Custom-Defined Functions.

Creating Java Classes for Custom-Defined Functions

To create a custom-defined function, write a public Java class that contains at least one public, static method to be used as a custom-defined function. Methods can have any combination of the following supported data types as input parameters:

boolean
byte
char
short
int
long
float
double
java.lang.String
com.hyperion.essbase.calculator.CalcBoolean
arrays of any of the above types

CalcBoolean is an Essbase-specific data type that includes three values: TRUE, FALSE, and #MISSING. For more information about the other listed data types, see the documentation for the Java Development Kit.

The method return data type can be void or any of the preceding data types. Returned data types are converted to Essbase-specific data types. Strings are mapped to a string type. Boolean values are mapped to the CalcBoolean data type. All other values are mapped to a double type.

Note:

Double variables returned with infinite or Not-a-Number values are not supported in Hyperion Essbase. If these values are returned from a Java program, they may not be recorded or displayed correctly in Hyperion Essbase. Double variables should be checked for infinite or Not-a-Number values and set to finite values before being returned to Hyperion Essbase. For more information, see the entry for the class, Double, in the documentation for the Java Development Kit.

For more examples of Java custom-defined functions, see the MaxL statement create function in the online Technical Reference in the DOCS directory.

To create a Java class for a custom-defined function:

Start a text editor and type a Java class; for example, type the following class:

public class CalcFunc {
  public static double sum (double[] data) {
    int i, n = data.length;
    double sum = 0.0d;
    for (i=0; i<n; i++) {
      double d = data [i];
      sum = sum + d;
    }
    return sum;
  }
}

Save the Java class as a .java file; for example, CalcFunc.java.
Compile the Java class using the JDK javac tool. At the command prompt, move to the directory containing the class you want to compile and use the javac tool; for example, to compile the CalcFunc.java class, type the following command:
```
>javac CalcFunc.java
```
Resolve any compiling errors and repeat steps 2 and 3 until the compiler creates a new .class file; for example, CalcFunc.class.

Installing Java Classes on a Server

When you install Java classes on a Hyperion Essbase server, you must first compile the classes into a Java Archive (.jar) file, and then copy the .jar file to a specific location on the computer running Hyperion Essbase server.

Note:

You must either stop and restart Hyperion Essbase applications or stop and restart the server when you install new .jar files.

To create a .jar file from a .class file and install it on a Hyperion Essbase server:

At the command prompt, move to the directory containing the class you want to add to a .jar file and use the JDK jar tool. To add CalcFunc.class to a .jar file, type the following command:
```
>jar cf CalcFuncs.jar CalcFunc.class
```
Copy the .jar file to one of the following locations on the computer running the Hyperion Essbase server:
- The ARBORPATH\java\udf\ directory. This location is recommended for .jar files containing custom-defined functions. If this directory does not exist, create the directory.
- The ARBORPATH\app\AppName\udf\ directory where AppName is the name of the application where the custom-defined function will be used. If this directory does not exist, create the directory. Use this location if you want the code in the .jar file to only be used with a specific application.
  If the .jar file is placed in another location, you must modify the CLASSPATH variable to include the full path and file name for the .jar file.
If the functions will only be used by specific applications, restart those applications in Hyperion Essbase. Otherwise, restart the Hyperion Essbase server.

Registering Custom-Defined Functions

After you have compiled the Java classes for custom-defined functions into .jar files and installed the .jar files on the Hyperion Essbase server, you must register the custom-defined functions before they can be used in calculation scripts and formulas.

Custom-defined functions are registered in one of two ways: locally or globally. When you register a local custom-defined function, it is available only in the application in which it was registered. When you register a global custom-defined function, it is available to all applications on the Hyperion Essbase server on which it was registered.

When you register a custom-defined function globally, all of your Hyperion Essbase applications can use it. Be sure you test custom-defined functions in a single application (and register them only in that application) before making them global functions.

CAUTION: Do not register functions globally for testing, because this makes it more difficult to update them if you find problems. For more information about updating custom-defined functions, see Updating Custom-Defined Functions.

For more detailed information on the create function statement grammar, see the MaxL section of the online Technical Reference in the DOCS directory. For rules about naming custom-defined functions, see Naming Custom-Defined Functions.

To register a local custom-defined function for a specific application:

Start the Hyperion Essbase server.
Start MaxL Command Shell at the command prompt and log on to the Hyperion Essbase server.
Use the create function statement to register the custom-defined function. To register the example custom-defined function shown earlier in this chapter, type the following statement:
```
MAXL> create function Sample.'@JSUM' 
   2> as 'CalcFunc.sum'
   3> spec '@JSUM(memberRange)
   4> comment 'adds list of input members';
```
Notice the prefix Sample. before the name of the function. This prefix assigns the custom-defined function to an application, so the function will be available only within that application.

Specifying input parameters for the Java method is optional. If you do not specify input parameters, Hyperion Essbase reads them from the method definition in the Java code. However, if you are registering two or more custom-defined functions with the same method name but with different parameter sets, you must register each version of the function separately, specifying the parameters for each version of the function.

To register a global custom-defined function on a Hyperion Essbase server:

Start the Hyperion Essbase server.
Start MaxL Command Shell at the command prompt and log on to the Hyperion Essbase server.
Use the create function statement to register the custom-defined function. To register the example custom-defined function shown earlier in this chapter, type the following statement:
```
MAXL> create function '@JSUM' 
   2> as 'CalcFunc.sum'
   3> spec '@JSUM(memberRange)
   4> comment 'adds list of input members';
```
Note: Notice that the AppName. prefix is not included in the name of the function. The lack of a prefix makes a function global.

To register the global custom-defined function, an application must be running. If no applications are running when you register the global function, Hyperion Essbase starts the application with the earliest creation date that contains a database.

Specifying input parameters for the Java method is optional. If you do not specify input parameters, Hyperion Essbase reads them from the method definition in the Java code. However, if you are registering two or more custom-defined functions with the same method name but with different parameters sets, you must register each version of the function separately, specifying the parameters for each version of the function.

Checking the Registration of Custom-Defined Functions

After registering custom-defined functions, you can determine whether a function has been registered successfully and whether it is registered locally or globally.

To check the registration of a custom-defined function on a Hyperion Essbase server:

Start the Hyperion Essbase server.
Start MaxL Command Shell at the command prompt and log on to the Hyperion Essbase server.
Use the display function statement to check the registration of the custom-defined function; for example, type the following statement:
```
MAXL> display function Sample; 
```
This statement displays a list of all functions registered for the named application (Sample) and any registered global functions. The display function statement lists global functions without an application name to indicate that they are global. If the application contains a function with the same name as a global function, only the local function is listed.

Using Registered Custom-Defined Functions

After registering custom-defined functions, you can use them like native Hyperion Essbase calculation commands. Functions you registered locally--using the AppName. prefix on the function name-- are only available for use in calculation scripts or formulas within the application in which they were registered. If you registered the custom-defined functions globally, then the functions are available to all calculation scripts and formulas on the Hyperion Essbase server where the functions are registered.

For more information about registering custom-defined functions, see Registering Custom-Defined Functions.

To use a registered custom-defined function:

Create a new calculation script or formula, or open an existing calculation script or formula.
- If the custom-defined function was registered locally--within a specific application--then you must use a calculation script or formula within that application.
- If the custom-defined function was registered globally, then you can use any calculation script or formula on the Hyperion Essbase server.
Add the custom-defined function to a new or existing calculation script or formula. To use the custom-defined function shown earlier in this chapter, type the following calculation script:
```
"New York" = @JSUM(@LIST(2.3, 4.5, 6.6, 1000.34));
```
Use this calculation script with the Sample Basic sample database, or replace "New York" with the name of a member in a test database.
Save the calculation script or formula, and then run it as usual.

For more information about creating and running calculation scripts, see Developing Calc Scripts. For more information about creating and running formulas, see Developing Formulas.

Updating Custom-Defined Functions

When updating a custom-defined function, there are two major issues to consider. First, is the function registered locally or globally? Second, have the class name, method name, or input parameters changed in the Java code for the custom-defined function? The answers to these questions determine the procedure for updating the custom-defined function.

For information on determining whether a custom-defined function is local or global, see Checking the Registration of Custom-Defined Functions.

To update a custom-defined function, in most cases, you must replace the .jar file that contains the code for the function, and then re-register the function. However, if the signature of the custom-defined function--the Java class name, method name and input parameters--have not changed and the function has only one set of input parameters (it is not an overloaded method), you can simply replace the .jar file that contains the function. In either situation, you must stop and restart the Hyperion Essbase application or server where the custom-defined function is registered, depending on whether it is a local or global function.

Note:

The following procedure is effective only if the signature of the custom-defined function--the Java class name, method name, and input parameters for the custom-defined function--has not changed.

To update a custom-defined function whose signature has not changed:

Make the changes to the Java class for the custom-defined function and use Java test programs to test its output.
Compile the Java classes and encapsulate them in a .jar file, using the same name as the previous .jar file. Make sure to include any other classes and methods for custom-defined functions that were included in the previous .jar file.
Perform one of the following steps:
- If the function is registered locally, shut down the application in which it was registered.
- If the function is registered globally, shut down all running applications.
Copy the new .jar file to the Hyperion Essbase server over the existing .jar file with the same name.

Restart the applications you shut down in step 3.

Updating Local Custom-Defined Functions

Local custom-defined functions are registered with an AppName. prefix on the function name and can be used only within the application where they were registered. To update a local custom-defined function, you must stop and restart the Hyperion Essbase application where the function is registered.

To update a local custom-defined function:

Make the changes to the Java class for the custom-defined function and use Java test programs to test its output.
Compile the Java classes and encapsulate them in a .jar file, using the same name as the previous .jar file. Make sure to include any other classes and methods for custom-defined functions that were included in the previous .jar file.
Perform one of the following steps:
- If the .jar file contains local custom-defined functions only, shut down any Hyperion Essbase applications that use these functions.
- If the .jar file contains any global custom-defined functions, shut down all Hyperion Essbase applications.
- If you are unsure as to which Hyperion Essbase applications use which functions in the .jar file you are replacing, shut down all Hyperion Essbase applications.
Copy the new .jar file to the Hyperion Essbase server over the existing .jar file with the same name.

Start MaxL Command Shell at the command prompt and log on to the Hyperion Essbase server.
Use the create or replace function statement to replace the custom-defined function; for example, type the following statement:
```
MAXL> create or replace function Sample.'@JSUM' 
   2> as 'CalcFunc.sum';
```
Note: After you execute this statement, Hyperion Essbase shuts down and restarts the application where the function resides.

Updating Global Custom-Defined Functions

Global custom-defined functions are registered without an AppName. prefix on the function name and are available in any application running on the Hyperion Essbase server where they are registered. To update a global custom-defined function, you must stop and restart all applications on the Hyperion Essbase server.

Global custom-defined functions can be used in calculation scripts and formulas across a Hyperion Essbase server, so you should verify that no calculation scripts or formulas are using a global custom-defined function before updating it.

CAUTION: Only a database administrator should update global custom-defined functions.

Note: When you update a global custom-defined function, Hyperion Essbase shuts down and restarts any running applications.

To update a global custom-defined function:

Make the changes to the Java class for the custom-defined function and use Java test programs to test its output.
Compile the Java classes and encapsulate them in a .jar file, using the same name as the previous .jar file. Make sure to include any other classes and methods for custom-defined functions that were included in the previous .jar file.
Shut down all running Hyperion Essbase applications.
Copy the new .jar file to the Hyperion Essbase server over the existing .jar file with the same name.

Start MaxL Command Shell at the command prompt and log on to the Hyperion Essbase server.
Use the create or replace function statement to replace the custom-defined function; for example, type the following statement:
```
MAXL> create or replace function '@JSUM' 
   2> as 'CalcFunc.sum';
```
Note: After you execute this statement, Hyperion Essbase shuts down and restarts any running applications.

Removing Custom-Defined Functions

When removing a custom-defined function, you must first determine whether the function is registered locally or globally. The procedure for removing global custom-defined functions is more complex than that for removing local custom-defined functions and should only be performed by a database administrator.

Before removing custom-defined functions, you should verify that no calculation scripts or formulas are using them. Global custom-defined functions can be used in calculation scripts and formulas across a Hyperion Essbase server, so you must verify that no calculation scripts or formulas on the server are using a global custom-defined function before removing it.

For information on determining whether a custom-defined function is local or global, see Checking the Registration of Custom-Defined Functions.

Removing Local Custom-Defined Functions

Local custom-defined functions are registered with an AppName. prefix on the function name and can only be used within the application where they are registered. When you remove a local custom-defined function, the Hyperion Essbase application where the function is registered must be stopped and restarted.

To remove a local custom-defined function:

Start the Hyperion Essbase server.
Start MaxL Command Shell at the command prompt and log on to the Hyperion Essbase server.
Use the drop function statement to remove the custom-defined function; for example, type the following statement:
```
MAXL> drop function Sample.'@JSUM';
```
Note: After you execute this statement, Hyperion Essbase shuts down and restarts the application where the custom-defined function is registered.

Removing Global Custom-Defined Functions

Global custom-defined functions are registered without an AppName. prefix on the function name and are available in any application running on the Hyperion Essbase server where they are registered. When you remove a global custom-defined function, all running Hyperion Essbase applications must be stopped and restarted.

CAUTION: Only a database administrator should remove global custom-defined functions. Removal of global custom-defined functions should only be performed when no users are accessing Hyperion Essbase databases and no calculation routines are being performed.

To remove a global custom-defined function:

Start the Hyperion Essbase server.
Start MaxL Command Shell at the command prompt and log on to the Hyperion Essbase server.
Use the drop function statement to remove the custom-defined function; for example, type the following statement:
```
MAXL> drop function '@JSUM';
```
Note: After you execute this statement, Hyperion Essbase shuts down and restarts any running applications.

Performance and Memory Considerations for Custom-Defined Functions

The ability to create and run custom-defined functions is provided as an extension to the Hyperion Essbase calculator framework. When you use custom-defined functions, consider how their use affects memory resources and calculator performance.

Performance Considerations

Because custom-defined functions are implemented as an extension of the Hyperion Essbase calculator framework, you can expect custom-defined functions to operate less efficiently than functions that are native to the Hyperion Essbase calculator framework. In tests using a simple addition function running in the Java Runtime Environment 1.3 on the Windows NT 4.0 platform, the Java function ran 1.8 times (about 80%) slower than a similar addition function performed by native Hyperion Essbase calculation commands.

To optimize performance, limit use of custom-defined functions to calculations that you cannot perform with native Hyperion Essbase calculation commands, particularly in applications where calculation speed is a critical consideration.

Memory Considerations

Use of the Java Virtual Machine (JVM) and Java API for XML Parsing has an initial effect on the memory required to run a Hyperion Essbase server. The memory required to run these additional components is documented in the memory requirements for the Hyperion Essbase server. For more information about memory requirements for Hyperion Essbase, see the Hyperion Essbase Installation Guide.

Beyond these start-up memory requirements, the Java programs you develop for custom-defined functions can require additional memory. When started, the JVM for Win32 operating systems immediately allocates 2 MB of memory for programs. This allocation is increased according to the requirements of the programs that are then run by the JVM. The default upper limit of memory allocation for the JVM on Win32 operating systems is 64 MB. If the execution of a Java program exceeds the default upper limit of memory allocation for the JVM, the JVM generates an error. For more information about JVM memory management and memory allocation details for other operating systems, see the Java Development Kit documentation.

Considering the default memory requirements of the JVM and the limitations of the hardware on which you run Hyperion Essbase servers, carefully monitor your use of memory. In particular, developers of custom-defined functions should be careful not to exceed memory limits of the JVM when creating large objects within custom-defined functions.