We help IT Professionals succeed at work.

Data extraction from Hyperion Essbase 9.3.1

Medium Priority
3,094 Views
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...
Comment
Watch Question

Commented:
Essbase has a full API in a few different languages including C, VB, and Java.  Take a look at
http://download.oracle.com/docs/cd/E10530_01/doc/epm.931/html_esb_api/topapilist.htm

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
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.
http:// www.staranalytics.com
 

Commented:
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.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.