Developing Custom-Defined Calculation Functions

In This Section:

The information in this chapter applies only to block storage databases and is not relevant to aggregate storage databases. Also see Comparison of Aggregate and Block Storage.

Process for Creating Custom-Defined Functions

To enhance the calculation functions provided with Essbase, you can create custom-defined functions for use in calculation scripts. Essbase does not provide sample custom-defined functions.

Custom-defined calculation functions are written in Java. Essbase does not provide tools for creating Java classes and archives; you must have a supported version of the JDK. See the Oracle Hyperion Enterprise Performance Management System Certification Matrix (http://www.oracle.com/technology/software/products/ias/files/fusion_certification.html).

For examples of custom-defined functions, see the Oracle Essbase Technical Reference.

To create a custom-defined function:

Review the requirements for custom-defined functions.
See Custom-Defined Function Requirements.
Write a public Java class that contains at least one public, static method to be used as a custom-defined function.
See Creating and Compiling a Java Class.
Install the Java class.
See Installing Java Classes on Essbase Server.
Register the custom-defined function as a local or global function.
See Registering Custom-Defined Functions.

Custom-Defined Function Requirements

You can create multiple methods in a class for use as a custom-defined function. Typically, Oracle recommends that you create the methods that you plan to use across all applications on an Essbase Server as custom-defined functions in a single class. If, however, you plan to add custom-defined functions that will be used in selective applications on the Essbase Server, create these custom-defined functions in a separate class and add them to Essbase 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 cause methods in the duplicate class not to be recognized, and you cannot register those methods as custom-defined functions.

Using test programs in Java, test the Java classes and methods. When you are satisfied with the output of the methods, install them on Essbase Server and register them in a single test application. Do not register functions globally for testing; doing so makes updating them more difficult if you encounter problems.

Methods in custom-defined functions can have any combination of the following supported data types as input parameters:

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

CalcBoolean is an Essbase-specific data type that can include three values—TRUE, FALSE, and #MISSING. For information about the other listed data types, see the JDK documentation.

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:

Essbase does not support double variables returned with infinite or Not-a-Number values. If these values are returned from a Java program, they may not be recorded or displayed correctly in Essbase. Double variables should be checked for infinite or Not-a-Number values and set to finite values before being returned to Essbase. See the entry for the class, Double, in the JDK documentation.

For creating, deleting, and managing custom-defined functions, Essbase requires these security permissions:

Local, application-wide, custom-defined functions: Application Manager or higher
Global, server-wide, custom-defined functions: Administrator

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

Follow these requirements for naming custom-defined functions:

Start the name with the @ symbol. The rest of a function name can contain letters, numbers, and the following symbols: @, #, $, and _. Function names cannot contain spaces.
For example: @MYFUNCTION
Start the names of custom-defined functions that are called only by custom-defined macros with “@_”, to distinguish them from general-use functions and macros.
For example: @_MYFUNCTION
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 calculation functions.
If an Essbase application contains a local function that has the same name as a global function, the local function is used for calculation.

Creating and Compiling a Java Class

To create and compile a Java class, use a text editor and the JDK javac tool.

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

In a text editor, create a Java class.

For example:

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 file with a .java extension.
For example:
```
CalcFunc.java
```
Navigate to the directory where the .java file resides; at a command prompt, enter this command:
```
javac java_filename
```
For example:
```
javac CalcFunc.java
```
Resolve any compiling errors until the compiler creates a new file with a .class extension.
For example:
```
CalcFunc.class
```

Installing Java Classes on Essbase Server

Java classes must be compiled in a JAR file, using the JDK jar tool.

To create a .jar file and install it on an Essbase Server:

Navigate to the directory where the .class file resides; at a command prompt, enter this command:
```
jar cf jar_filename class_filename
```
For example:
```
jar cf CalcFunc.jar CalcFunc.class
```
On the computer running Essbase Server, copy the .jar file to one of the following directories (if the directory does not exist, create it):
- For .jar files containing global custom-defined functions:
```
ARBORPATH/java/udf/
```
- For .jar files to be used only with specific applications:
```
ARBORPATH/app/appName/udf/
```
  where appName is the name of the application where the local custom-defined function will be used.
If the .jar file is subsequently placed in another location, you must modify the CLASSPATH variable to include the full path and filename for the .jar file.
If the functions will be used only by specific applications, restart those applications in Essbase. Otherwise, restart 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 Essbase Server, you must register the custom-defined functions before you can use them in calculation scripts and formulas. See Custom-Defined Function Requirements.

When you register a global custom-defined function, all Essbase applications on the Essbase Server can use it. Test custom-defined functions in a single application (and register them only in that application) before making them global functions.

Use the same process for updating the catalog of functions as for updating the catalog of macros. See Refreshing the Catalog of Custom-Defined Macros.

Caution!

Do not register global functions for testing; doing so makes changing them more difficult if you encounter problems.

To register a custom-defined function, use a tool:

Tool	Topic	Location
Administration Services	Creating Custom-Defined Functions	Oracle Essbase Administration Services Online Help
MaxL	create function	Oracle Essbase Technical Reference

To register a custom-defined function with local scope, include the application name as a prefix. For example, the following MaxL statement registers the custom-defined function, @JSUM, in the CalcFunc class as a local function for use within the Sample application:

create function Sample.'@JSUM'
as 'CalcFunc.sum'
spec '@JSUM(memberRange)'
comment 'adds list of input members';

To register a custom-defined function with global scope, do not include the application name as a prefix. For example, the following MaxL statement registers the custom-defined function, @JSUM, in the CalcFunc class as a global function for use in any application on Essbase Server:

create function '@JSUM'
as 'CalcFunc.sum'
spec '@JSUM(memberRange)'
comment 'adds list of input members';

Note:

Specifying input parameters for the Java method is optional. If you do not specify input parameters, Essbase reads them from the method definition in the Java code. If, however, you are registering multiple 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.

Using Registered Custom-Defined Functions

You can use registered custom-defined functions like native Essbase calculation commands.

To use a registered custom-defined function:

Create or open an existing calculation script or formula.
- If the custom-defined function was registered locally—within a specific application—you must use a calculation script or formula within that application.
- If the custom-defined function was registered globally, you can use any calculation script or formula on Essbase Server.
Add the custom-defined function to the calculation script or formula.
For example, to use JSUM, use this 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.

Updating Custom-Defined Functions

The procedure for updating custom-defined functions depends on these conditions:

Whether the function is registered locally or globally
Whether the signature of the custom-defined function—class name, method name, or input parameters—has been changed in the Java code for the custom-defined function

Typically, to update a custom-defined function, you must replace the .jar file that contains the code for the function, and then re-register the function. If, however, the signature of the custom-defined function has not changed, and the function has only one set of input parameters (it is not an overloaded method), you can replace the .jar file that contains the function.

Note:

Only DBAs should update global custom-defined functions.

To update a custom-defined function:

Determine whether the function is local or global.
See Viewing Custom-Defined Functions.
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 archive them in a new .jar file, using the same name as the previous .jar file.
Include any other classes and methods for custom-defined functions that were included in the previous .jar file.
Perform an action, depending on whether you are updating a local or global custom-defined function:
1. Local: Shut down any Essbase applications that use the functions in the .jar file.
2. Global: Shut down all Essbase applications
If you are unsure which Essbase applications use which functions in the .jar file, shut down all Essbase applications.
Copy the new .jar file to Essbase Server, replacing the existing .jar file with the same name.
If the signature of the custom-defined function has not changed, skip to step 8.
To replace the custom-defined function, use a tool:
- Administration Services: See “Editing Custom-Defined Functions” in the Oracle Essbase Administration Services Online Help.
- MaxL: Use the create or replace function statement. For example:
  - Local:
    create or replace function sample.'@JSUM' as 'CalcFunc.sum';
  - Global:
    create or replace function '@JSUM' as 'CalcFunc.sum';
Restart the applications that you shut down, which updates the catalog.

Viewing Custom-Defined Functions

You can view custom-defined functions to determine whether a function has been registered successfully and whether it is registered locally or globally. Custom-defined functions are not displayed until they have been created and registered.

To view a custom-defined function, use a tool:

Tool	Topic	Location
Administration Services	Viewing Custom-Defined Functions	Oracle Essbase Administration Services Online Help
MaxL	display function	Oracle Essbase Technical Reference

For example, use the following MaxL statement to view the custom-defined functions in the Sample application and any registered global functions:

display function Sample;

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.

Removing Custom-Defined Functions

The following permissions are required to remove a custom-defined function:

Local: Application Manager permission for the application, or any wider permission
Global: Administrator permission

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 Essbase Server, so you must verify that no calculation scripts or formulas on Essbase Server are using a global custom-defined function before removing it.

Caution!

Remove global custom-defined functions only when users are not accessing Essbase databases and calculation routines are not being performed.

To remove a custom-defined function:

Determine whether the function is local or global.
See Viewing Custom-Defined Functions.
Perform an action, depending on whether you are removing a local or global custom-defined function:
1. Local: Shut down any Essbase applications that use the functions in the .jar file.
2. Global: Shut down all Essbase applications.
To remove the custom-defined function, use a tool:
- Administration Services: See “Deleting Custom-Defined Functions” in the Oracle Essbase Administration Services Online Help
- MaxL: Use the drop function MaxL statement. For example:
- Local:
```
drop function Sample.'@JSUM';
```
- Global:
```
drop function '@JSUM';
```
Restart the applications that you shut down, which updates the catalog.

Copying Custom-Defined Functions

You can copy custom-defined functions to any Essbase Server and application to which you have appropriate access.

To copy a custom-defined function, use a tool:

Tool	Topic	Location
Administration Services	Copying Custom-Defined Functions	Oracle Essbase Administration Services Online Help
MaxL	create function	Oracle Essbase Technical Reference

Performance Considerations for Custom-Defined Functions

Because custom-defined functions are implemented as an extension of the Essbase calculator framework, you can expect custom-defined functions to operate less efficiently than functions native to the Essbase calculator framework.

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

Memory Considerations for Custom-Defined Functions

Use of the JVM API and Java API for XML Parsing has an initial effect on the memory required to run Essbase. The memory required to run these additional components is documented in the memory requirements for Essbase. See the Oracle Hyperion Enterprise Performance Management System Installation Start Here.

Beyond these startup memory requirements, the Java programs you develop for custom-defined functions sometimes 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 JDK documentation.

Considering the default memory requirements of the JVM and the limitations of the hardware on which you run 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 artifacts within custom-defined functions.