Hyperion Essbase Custom-Defined Functions Documentation

Package: ExportCDF Version 2.0

Disclaimer:
These functions are considered "shareware". Use of the contained functions are at your own risk. Hyperion Solutions Corp. is not liable for any damage, direct or indirect, associated with the use of the supplied functions. The source code is provided so that customer may review, recompile, or modify for their environment.

Introduction

The custom-defined functions (CDFs) contained in this zip file provide the functionality to export data directly from an Essbase database to various target formats. These target formats include a text file export, a Microsoft Access database table, or any JDBC accessible database table. The user of these functions can use the Essbase calculator to focus on the whole database, or any given slice (via the FIX calculator statement). Example calc scripts, based on the Sample Basic database that ships with Hyperion Essbase, are included in this zip file. These calc scripts do not change any data in the Essbase database.

Contents of Zip File

The files contained in this zip file are as follows (you may click on the links to view selected files):

Installation Instructions

The custom-defined function feature is a server based feature. Therefore, files necessary to work with the examples need to be installed on the server, per the below instructions. Any file that is not identified below is not required to be on the server, and can therefore be moved to a location of the user's choosing.

You must also grant access for the ExportCDF.jar to be executed. To grant access, add the following to the end of the udf.policy file contained in the $ARBORPATH/java directory:

grant codeBase "file:${essbase.java.home}/../java/udf/ExportCDF.jar" {
permission java.security.AllPermission;
};

Registering the Function(s)

You can manually register the function via the Essbase Application Manager interface or via the new Essbase Administration Services interface. Included in this package is the RegisterExportCDF.msh MaxL script that will register the function automatically. The registration script can be run from either the Essbase server or a client machine that has installed the Runtime Client. For more information on installing the Runtime Client, please refer to the Hyperion Essbase Installation Guide.

Note: You must have administrator privileges to register new functions on the Essbase server. Prior to executing the script, you must modify the Login statement to reflect the appropriate administrator id and password for your system. For more information on using MaxL, please refer to the Hyperion Essbase Technical Reference in your Essbase installation.

To execute the registration script, type the following at a command prompt:

essmsh RegisterExportCDF.msh

Using the Examples: ToDSN.csc

The ToDSN.csc calc script is used to export data to a DSN configured ODBC connection. This example comes with a Microsoft Access database to be used in the example. To use this example, you must first configure your ODBC System DSN. The System DSN should be created with the name of DemoCDF. The Database path should be set to where you have placed the exportRDB.mdb database file. Running this calc script will write all intersections and data from the Essbase database that has a negative sales variance to the output table contained in the exportRDB Microsoft Access database. Please review the ToDSN.csc calc script source file for the specific parameters, and their meaning.

Note: If you run this calc script multiple times, you will want to delete the data contained in the output table of the Access database prior to running, otherwise you will continue to append to this table.

Using the Examples: ToFile.csc

The ToFile.csc calc script is used to export data to a flat file format. Running this calc script will write all intersections and data from the Essbase database that has a negative sales variance. It also allows for the indication of the delimiter to use between fields. Please review the ToFile.csc calc script source file for the specific parameters, and their meaning.

Using the Examples: ToFile2.csc

The ToFile2.csc calc script is used to export data to a flat file format. Running this calc script will write all intersections and data from the Essbase database that has a negative sales variance. It also allows for the indication of the delimiter to use between fields, selecting a text qualifier such as " and specifying precision and replacing #Missing per exported column. Please review the ToFile2.csc calc script source file for the specific parameters, and their meaning.

Using the Examples: ToJDBC.csc

The ToJDBC.csc calc script is used to export data to any JDBC accessible database. In order to use this calc script, you will need the JDBC driver, and you will need to define the driver in the included rdb.properties file. The rdb.properties file included alread contains entries for IBM DB2, Oracle and Microsoft SQL Server. You will need to modify this file for your selected RDBMS. Running this calc script will write all intersections and data from the Essbase database that has a negative sales variance.  Please review the ToJDBC.csc calc script source file for the specific parameters, and their meaning.

Note: This function does not enforce any particular table structure that must be used, as it is simply using the Insert Into TargetTable Values (....) SQL syntax. Therefore you have flexibility in exporting any information from any Essbase database to various target Relational tables. However, your target table should match the information that you are exporting. For purposes of this example, you need to first create the Output table in your relational database with the following fields:

Note: If you run this calc script multiple times, you will want to delete the data contained in the output table of the Access database prior to running, otherwise you will continue to append to this table.

Additional Notes

These functions have been written to be extremely flexible in working with different Essbase databases and target export formats. Exploiting additional Essbase calculator functionality, such as specific fixes on different levels, can further scope the information to be exported. Additionally, within one calc script, one can specify multiple export formats based on some criteria, such as using the IF statement to test certain conditions. Since the JExportTo and JExtendedExportTo functions are not restricted to a given export format, but rather driven by the parameters passed during execution, different scripts and Essbase Applications can reuse these functions as needed. Also, with the intelligent calculator enabled, one can use these functions in a calc script to export "dirty" blocks before an actual calculation in order to maintain an audit of activity in a read/write application, and to use for recoverability purposes.