Data extraction from Hyperion Essbase 9.3.1

Posted on 2009-02-18
Last Modified: 2013-11-16
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...
Question by:fatblackcat
    LVL 5

    Accepted Solution

    Essbase has a full API in a few different languages including C, VB, and Java.  Take a look at

    Expert Comment

    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.
    LVL 5

    Expert Comment

    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.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Join & Write a Comment

    Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
    This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now