[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Data extraction from Hyperion Essbase 9.3.1

Posted on 2009-02-18
4
Medium Priority
?
3,060 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...
0
Comment
Question by:fatblackcat
  • 2
3 Comments
 
LVL 5

Accepted Solution

by:
garycris earned 2000 total points
ID: 23685259
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
0
 

Expert Comment

by:nkaposhilin
ID: 23688103
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
 
0
 
LVL 5

Expert Comment

by:garycris
ID: 23688219
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.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

872 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