Essbase Custom-Defined Functions Overview
While the out-of-the-box functions provided by Essbase and Hyperion Planning can be helpful for many calculations, they may not always be sufficient for more complex or dynamic scenarios. However, Essbase CDFs (Custom Defined Functions) can be a powerful solution.
By creating custom functions using CDFs, developers can extend the capabilities of Essbase and Hyperion Planning to perform complex calculations and incorporate data from external sources. CDFs can also be used to create forecasting or predictive analytics models, which can integrate with existing financial data to provide valuable insights into future performance.
One advantage of CDFs is that they can be developed using Java, a widely used programming language. Developers with Java experience can use their existing skills to create custom functions, reducing development time and cost.
Another benefit of CDFs is that we can easily integrate them with existing Essbase and Hyperion Planning applications. This allows organizations to add new functionality to their applications without replacing or re-engineering existing processes.
Overall, CDFs are a powerful tool for extending the capabilities of Essbase and Hyperion Planning and can be a valuable solution for organizations looking to incorporate more advanced analytics and forecasting into their financial applications.
Prerequisites for Creating Essbase CDF
Before creating custom-defined functions in Essbase, we should review the requirements and gather the necessary tools and documents. Here are the main requirements and tools that we will need:
1. Java Development Kit (JDK)
We need a compatible version of the JDK installed on the computer. We can download the JDK from the Oracle website (http://www.oracle.com/technetwork/java/javase/downloads/index.html).
Ensure you download the appropriate 32-bit or 64-bit version of the JDK that matches your installation.
2. Java Integrated Development Environment (IDE)
While not strictly necessary, an IDE can make the development process much easier and more efficient. Eclipse, JDeveloper, and NetBeans are some of the popular Java IDEs that can be used to develop custom-defined Essbase functions. Without an IDE, we can use a text editor to write and edit the Java code. Notepad++ is a popular and free text editor that developers often use.
3. Enable Java for Essbase
Before using custom-defined functions, we must ensure that Java is enabled in the configuration file (Essbase.cfg). Specifically, we need to specify the full path to the JVM.DLL file for the version of the JDK that we are using. We can do this by adding the following line to the Essbase.cfg file:
JvmModuleLocation{path}\jdk{version}\jre\bin\server\jvm.dll
Make sure to replace {path} and {version} with the actual path to your JDK installation and the version number of the JDK that you are using.
By gathering the necessary tools and documents and ensuring that Java is properly configured, we can start creating custom-defined functions to extend Essbase’s functionality and meet the organization’s specific needs.
Steps for Creating an Essbase CDF
Step 1: Develop
- Design and build Java function classes that perform the necessary calculations or algorithms.
- Compile the Java classes using a compatible version of the JDK. The version of the JDK required will depend on the version of Essbase that we are using.
- Package the function classes into a jar library.
- Deploy the function library jar to Essbase, typically by copying it to the appropriate directory on the server.
Step 2: Deploy and Register
- Define the function profile and data interface using the @JCustomFunction annotation. This specifies the input and output parameters for the function and any additional metadata.
- Define the security policy for the function, including which users or groups are authorized to use it.
- Create a registration script that registers the custom-defined function with Essbase. This usually involves specifying the location of the function library jar and providing any necessary metadata.
- Register the custom-defined function with Essbase, either globally or for a specific Essbase application or database.
Step 3: Calculation Script
- Develop calculation scripts or business rules that use the new custom-defined functions.
- Pass data from your intersections to the new @JCustomFunction() using the appropriate syntax and data types.
- Return values from the function can be assigned to Essbase members or used as input to other functions or calculations within the script.
Essbase CDF Example
Let’s look at an example of creating an Essbase CDF to sum a list of values:
- Open the Essbase Calculation Script Editor and select “File” > “New” > “Custom Defined Function”.
- Enter a name for the function, such as “MySum,” and define the number of arguments the function will take. For example, we can define two arguments for the function: “MySum(list, delimiter).”
- Write the Java code for the function using the Essbase API. For example, we can write code that converts the input list into an array of values and sums them using a for loop.
- Compile the Java code and save it as a .jar file.
- Register the .jar file as an Essbase add-in using the Essbase Administration Console.
- Test the function by calling it from a calculation script or the Smart View add-in in Excel.
Using CDF in a Business Rule
Here is an example of using a custom-defined function in a Hyperion Business Rule:
- Open the Hyperion Calculation Manager and create a new Business Rule.
- In the Business Rule editor, navigate to the Calculation Script tab.
- Define the function profile by typing the function name, parameters, and return type. For example:
- @JCustomFunction(params=(“double[]”), returns=”double”)
- Call the custom function in the Calculation Script area using the defined function profile. For example:
- FIX (Jan) Actual = @JCustomFunction(@LIST(100, 200, 300)); ENDFIX
- Save the Business Rule and deploy it to the Essbase server.
The custom-defined function can now be used in Essbase calculations via the Business Rule.
If you have any questions about this blog or need help with Oracle EPM/Hyperion, please contact us.