Essbase® Analytic Services Database Administrator's Guide | | Update Contents | Previous | Next | Print | ? | |
Information Map | |
This chapter explains how to develop custom-defined functions and use them in Analytic Services formulas and calculation scripts. Custom-defined functions are written in the JavaTM programming language and enable you to create calculation functions not otherwise supported by the Analytic Services calculation scripting language.
Analytic Services 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 use to develop custom-defined functions. For information on compatible versions of Java, see the Essbase Analytic Services Installation Guide.
For additional examples of custom-defined functions, see the Technical Reference.
Use these sections to create and use custom-defined functions:
Note: The information in this chapter is not relevant to aggregate storage databases.
You can view custom-defined functions to determine whether a function has been registered successfully and whether it is registered locally or globally. No custom-defined functions are displayed until they have been created and registered. Analytic Services does not supply sample custom-defined functions.
To view a custom-defined function, use either of the following methods:
Tool |
Topic |
Location |
---|---|---|
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.
There are several steps required to create a custom-defined function:
For instructions, see Creating and Compiling the Java Class.
For instructions, see Installing Java Classes on Analytic Server.
For instructions, see Registering Custom-Defined Functions.
The basis of a custom-defined function is a Java class and method created 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 the Analytic Server, create them in a new class and add them to the Analytic 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.
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 Analytic Server and register them in a single test application. Do not register functions globally for testing, because registering functions globally makes it more difficult to update them if you find problems.
Methods in custom-defined functions can have any combination of the following supported data types as input parameters:
Type |
Type |
---|---|
CalcBoolean is an Analytic Services-specific data type that can include 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 Analytic Services-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 by Analytic Services. If these values are returned from a Java program, they may not be recorded or displayed correctly in Analytic Services. Double variables should be checked for infinite or Not-a-Number values and set to finite values before being returned to Analytic Services. For more information, see the entry for the class, Double, in the documentation for the Java Development Kit.
For additional examples of Java custom-defined functions, see the MaxL statement create function in the Technical Reference.
Analytic Services requires that you have these security permissions:
Custom-defined functions are registered as either local (application-wide) or global (Analytic Server-wide) in scope. 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 Analytic Server on which it was registered.
When developing and testing custom-defined functions, make sure to register and test new functions locally within a test application. You should never register custom-defined functions globally until you have thoroughly tested them and are ready to use them as part of a production environment.
When you register a custom-defined function in Analytic Services, 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.
Remember these requirements when you create function names:
For information about and instructions for registering custom-defined functions, see Registering Custom-Defined Functions.
To create a Java class for a custom-defined function, use this procedure:
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; } }
CalcFunc.java
.CalcFunc.java
class, type this command:>javac CalcFunc.java
.class
file; for example, CalcFunc.class
.
When you install Java classes on Analytic 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 Analytic Server.
Note: You must either stop and restart Analytic Services applications or stop and restart Analytic Server when you install new .jar
files.
To create a
.jar
file from a .class
file and install it on an Analytic Server:
.jar
file and use the JDK jar tool. To add CalcFunc.class
to a .jar
file, type this command:>jar cf CalcFunc.jar CalcFunc.class
.jar
file to one of the following locations on the computer running Analytic Server: ARBORPATH
\java\udf\
directory. This location is recommended for .jar
files containing global custom-defined functions. If this directory does not exist, create the directory.ARBORPATH
\app\
AppName
\udf\
directory where AppName is the name of the application where the local 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.
After you have compiled the Java classes for custom-defined functions into .jar
files and installed the .jar
files on Analytic Server, you must register the custom-defined functions before you can use them in calculation scripts and formulas. For rules about naming custom-defined functions, see Naming Custom-Defined Functions.
When you register a global custom-defined function, all Analytic Services applications on the Analytic Server 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.
Use the same process for updating the catalog of functions as you do for updating the catalog of macros. After you register a custom-defined function, see Refreshing the Catalog of Custom-Defined Macros.
Caution: Do not register global functions for testing, because registering them globally makes it more difficult to change them if you find problems.
To register a custom-defined function, use either of the following methods:
Tool |
Topic |
Location |
---|---|---|
To register a custom-defined function with local scope, include the application name as a prefix. For example, use the following MaxL statement to register the local custom-defined function in the class CalcFunc from Creating and Compiling the Java Class to be used 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, use the following MaxL statement to register as a global function the custom-defined function in the class CalcFunc from Creating and Compiling the Java Class:
create function '@JSUM' as 'CalcFunc.sum'; spec '@JSUM(memberRange)' comment 'adds list of input members';
The AppName. prefix is not included in the name of the function. The lack of a prefix makes a function global.
Note: Specifying input parameters for the Java method is optional. If you do not specify input parameters, Analytic Services 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.
After registering custom-defined functions, you can use them like native Analytic Services 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 Analytic Server where the functions are registered.
For information and instructions for registering custom-defined functions, see Registering Custom-Defined Functions.
To use a registered custom-defined function:
"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.
For a comprehensive discussion of creating and running calculation scripts, see Developing Calculation Scripts. For a comprehensive discussion of creating and running formulas, see Developing Formulas.
When you update a custom-defined function, there are two major issues to consider:
The answers to these questions determine the procedure for updating the custom-defined function.
For a review of methods to determine whether a custom-defined function is local or global, see Viewing 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 Analytic Services application or Analytic 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:
.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..jar
file to Analytic Server over the existing .jar
file with the same name.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 Analytic Services application where the function is registered.
To update a local custom-defined function:
.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..jar
file contains local custom-defined functions only, shut down any Analytic Services applications that use these functions..jar
file contains any global custom-defined functions, shut down all Analytic Services applications..jar
file you are replacing, shut down all Analytic Services applications. .jar
file to Analytic Server over the existing .jar
file with the same name.create or replace function sample.'@JSUM' as 'CalcFunc.sum';
Global custom-defined functions are registered without an AppName. prefix on the function name and are available in any application running on the Analytic Server where they are registered. To update a global custom-defined function, you must stop and restart all applications on Analytic Server.
Global custom-defined functions can be used in calculation scripts and formulas across Analytic 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.
To update a global custom-defined function:
.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..jar
file to Analytic Server over the existing .jar
file with the same name.create or replace function '@JSUM' as 'CalcFunc.sum';
When removing a custom-defined function, you must first determine whether the function is registered locally or globally to identify the security permissions required:
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 Analytic Server, so you must verify that no calculation scripts or formulas on Analytic Server are using a global custom-defined function before removing it.
For a review of methods to determine whether a custom-defined function is local or global, see Viewing 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, be sure to stop and restart the Analytic Services application where the function is registered to update the catalog.
To remove a custom-defined function, use either of the following methods:
Tool |
Topic |
Location |
---|---|---|
For example, use the following MaxL statement to remove the @JSUM function from the Sample application:
drop function Sample.'@JSUM';
Global custom-defined functions are registered without an AppName. prefix on the function name and are available in any application running on Analytic Server where they are registered.
Be sure to stop and restart all running Analytic Services applications when you remove a global custom-defined function.
Global custom-defined functions can be used in calculation scripts and formulas across Analytic Server, so you should verify that no calculation scripts or formulas are using a global custom-defined function before removing it.
Caution: Only a database administrator with supervisor permission can remove global custom-defined functions. Removal of global custom-defined functions should only be performed when no users are accessing Analytic Services databases and no calculation routines are being performed.
To remove a global custom-defined function, use either of the following methods:
Tool |
Topic |
Location |
---|---|---|
For example, use the following MaxL statement to remove the global @JSUM function:
drop function '@JSUM';
You can copy custom-defined functions to any Analytic Server and application to which you have appropriate access.
To copy a custom-defined macro, use either of the following methods:
Tool |
Topic |
Location |
---|---|---|
The ability to create and run custom-defined functions is provided as an extension to the Analytic Services calculator framework. When you use custom-defined functions, consider how their use affects memory resources and calculator performance.
Because custom-defined functions are implemented as an extension of the Analytic Services calculator framework, you can expect custom-defined functions to operate less efficiently than functions that are native to the Analytic Services 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 Analytic Services calculation commands.
To optimize performance, limit use of custom-defined functions to calculations that you cannot perform with native Analytic Services calculation commands, particularly in applications where calculation speed is a critical consideration.
Use of the Java Virtual Machine (JVM) and Java API for XML Parsing has an initial effect on the memory required to run Analytic Services. The memory required to run these additional components is documented in the memory requirements for Analytic Services. For more information about memory requirements, see the Essbase Analytic Services Installation Guide.
Beyond these start-up 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 the Java Development Kit 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 objects within custom-defined functions.
![]() |