Data extraction from Hyperion Essbase 9.3.1

My company is running Hyperion Essbase 9.3.1, and my infrastructure team is looking for potential solutions for querying data from an Essbase cube in a fashion similar to the Excel Add-in, but leveraging a method that can be maintained systematically. API, ODBC, OleDB connection, etc...
Who is Participating?
garycrisConnect With a Mentor Commented:
Essbase has a full API in a few different languages including C, VB, and Java.  Take a look at
it really depends on your budget and your requirement for performance. The easiest way to programatically extract data from Essbase is to use the Essbase Report Writer tool that is built-in to EAS Console. You create the Report (which really should not be called a report because there is next to no style formatting capabilities) and save the report script to a text file similar to the way you would write and save an Essbase calc script. Once written you can invoke this report script via a MAXL script or ESSCMD script and further embed a call to these scripts in your shell script, etc. The only problem with this is that if you are trying to query a very large portion of your data for purposes of synchronisation with other databases such as an EDW or a staging area, then performance may be an issue because ethe report writer is notoriously slow. (although it has improved a lot since v9) Other higher performance options would be to use the C API (which require a lot of expensive consulting unless you code C and have a coding env setup) or the JAVA API. The JAVA API is definitely easier and there is a little known feature in Essbase called CDFs (Custom Defined Functions) These little guys are not difficult to write and you dont have to have an overall program to invoke them from. You simply script a JAva function that takes data parameters in a documented way from Essbase, performs some manipulation on them and outputs the data to a flat file or streams to a JDBC target. You compile the function to a JAR file and register the JAR file with Essbase when the server starts. Then you can invoke the CDA just like any other function within a calc script. This is one of the most powerful ways to quickly customise your own data extraction. But it does require knowledge of Java and a proper LAVA dev env to work within.

You can read more about all this by reading the Essbase Administrators Guide that gets installed when you install EAS Server.

Finally if budget is not a constraint and you expect to be doing this extraction for many Essbase sources over time and you want to support flexible changes, then this tool is optimsed to help you.
Star Analytics is a really good tool.  Also, along the lines of the CDF, the version of Essbase you are on has a built in data export feature that levarages the calc script engine to export data.  The nice thing about this is you can use FIX statements to focus on the slice of data you want.  You can export to flat file, or even to an ODBC connection and dump the export in a relational table.  All can be scripted via MaxL as nkaposhilin mentioned.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.