Oracle has given Essbase administrators several different prebuilt CDFs that can be used anytime they are needed. There are three main locations for prebuilt CDFs: Oracle’s Sample Code site, Calculation Manager, and Hyperion Planning.
Oracle Sample Code
Located at (http://www.oracle.com/technetwork/indexes/samplecode/essbase-sample-522117.html), Oracle’s Sample Code site offers eight different downloads containing several CDFs. These downloads contain precompiled JAR files, the necessary MaxL statements to register the CDFs, and usually a sample calculation to show how to use the CDFs. The eight downloads are:
- Financial Functions: Calculates loan maturity, principal and interest payments, and interest rates
- Export Functions: The main function used in this CDF was replaced by the DATAEXPORT Essbase calculator function
- Email Functions: Sends emails during calculations to alert administrators or users
- Date Functions: Includes various functions to work with dates, some are epoch date format and some are YYYYMMDD format
- Data Functions: Allows retrieval of external data from files or the Internet
- Counter Functions: Counts the number of passes through an outline during a calculation for tuning purposes
- Logging Functions: Logs messages to a specified file, replaced by @CalcMgrLog and @CalcMgrLogText functions
- String Functions: Manipulates text fields, many of these functions are also available in @CalcMgr* text functions
Calculation Manager CDFs
Beginning with Calculation Manager 184.108.40.206.502 and up, many prebuilt Custom Defined Functions are automatically deployed to the Essbase server and ready for use. Version 220.127.116.11 of Calculation Manager includes 67 prebuilt functions. Many of these functions are for text and date manipulation. The date functions are primarily for use in Capital Expenditure and Workforce Planning applications using the YYYYMMDD date format, but can be used in any application that stores dates using that format.
In addition to the text and date functions, there are six miscellaneous functions that can be useful in a variety of situations. The six miscellaneous functions are:
- @CalcMgrExecuteEncryptMaxLFile – allows the execution of an encrypted MaxL file.
- @CalcMgrExecuteMaxLScript – streams MaxL code to the Essbase kernel for immediate execution
- @CalcMgrLog – writes messages to a specified text file, can include data from Essbase for validations
- @CalcMgrLogText – writes text-only messages to a specified text file
- @CalcMgrMDXDataCopy – using MDX queries, this function can select a range of data blocks in one application and copy the data to a target application
- @CalcMgrMDXExport – using MDX queries, this function exports data to a file quickly without the ugly formatting of a traditional MDX export
Some of these functions are not properly registered after applying the patch. The errors can be fixed by editing the essfunc.xml file in the %EPM_ORACLE_HOME%\EssbaseServer\essbaseserver1\java directory. The following edits need to be made to have all of the CDFs registered properly:
- Change getCurrentDate(int) to getCurrentDate()
- Change indexOf(String,int) to indexOf(String,String,int)
- Change @CalcMgrIndexOf(text, beginIndex) to @CalcMgrIndexOf(text, searchString, beginIndex)
- Change lastIndexOf(String,int) to indexOf(String,String,int)
- Change @CalcMgrLastIndexOf(text, beginIndex) to @CalcMgrLastIndexOf(text, searchString, beginIndex)
- Change substring2(String,int,int) to substring(String,int,int)
- Change @CalcMgrSubstring2(text, startIndex, endIndex) to @CalcMgrSubstring(text, startIndex, endIndex)
- Change hyperion.calcmgr.common.cdf to com.hyperion.calcmgr.common.cdf.MDXExport
Hyperion Planning CDFs
Hyperion Planning also comes with several prebuilt CDFs. In version 18.104.22.168, Planning contains 23 CDFs available for use in both Calculation Manager rules and in Essbase calculation scripts. When attempting to use these rules in Calculation Manager, the parameter definitions are not descriptive, as shown in the picture below:
To get a better feel for the parameters for each @Hsp* function, view their definitions in Essbase Administration services as shown below:
Next time we will cover creating our own CDFs from scratch.