CDF Series Part 2: Prebuilt CDFs

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 11.1.2.3.502 and up, many prebuilt Custom Defined Functions are automatically deployed to the Essbase server and ready for use. Version 11.1.2.4 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:

  • @CalcMgrGetCurrentDate
    • Change getCurrentDate(int) to getCurrentDate()
  • @CalcMgrIndexOf
    • Change indexOf(String,int) to indexOf(String,String,int)
    • Change @CalcMgrIndexOf(text, beginIndex) to @CalcMgrIndexOf(text, searchString, beginIndex)
  • @CalcMgrLastIndexOf
    • Change lastIndexOf(String,int) to indexOf(String,String,int)
    • Change @CalcMgrLastIndexOf(text, beginIndex) to @CalcMgrLastIndexOf(text, searchString, beginIndex)
  • @CalcMgrSubstring
    • Change substring2(String,int,int) to substring(String,int,int)
    • Change @CalcMgrSubstring2(text, startIndex, endIndex) to @CalcMgrSubstring(text, startIndex, endIndex)
  • @CalcMgrMDXExport
    • 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 11.1.2.4, 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:

CalcMgr Functions

To get a better feel for the parameters for each @Hsp* function, view their definitions in Essbase Administration services as shown below:

EAS Function

Next time we will cover creating our own CDFs from scratch.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s