Jason McKittrick's Blog

Ways to get data out of Essbase for Microsoft BI

Microsoft BI is easy to use and is the largest growing BI platform according to Gartner.  In order to get data out of Essbase it requires some magic.  Below is a list of options to get data out of Essbase into another stack like Microsoft BI.   One other option that I didn’t include on the list is Star Analytics.  I left them off since they are more of a complete solution even though they have an option for getting data out of Essbase you would have to purchase everything.

A quick disclaimer:  the performance and considerations sections are my opinions of the solutions based on my knowledge.  Please make sure all due diligence is completed prior to purchase or enablement of the options.

  • Essbase Native Exports
    • Essbase kernel based export to a ASCII file
    • Export data at Level 0, Input and ALL
    • Data can be exported in column formats
    • Performance
      • Very fast for daily use
    • Considerations
      • No dynamically calculated or attribute calculation data is exported
      • Can only be run on the Essbase server.
      • Output paths must be valid to the Essbase server. See the link here form the Oracle website.
  • JExport Custom Defined Function
    • Leverages Custom Defined Function (CDF) via the Java hook into the Essbase kernel
    • Using standard Essbase calc scripts, data can be exported directly to text files and relational targets
    • Very flexible exports can be designed providing the ability to extract dynamic calcs, attribute calcs, lite transformations along with base data
    • Performance
      • Very good assuming proper scripting
    • Considerations
      • Can only be run on the Essbase server.
      • Relational targets must be valid to the Essbase server.
  • DataExport Calc Scripts
    • Powerful new Essbase calculation script function introduced in Essbase 9.3.1
    • Similar to @JExport, leverages calc script functions and much simpler to use
    • Provides ability to export data directly via Essbase kernel to text, relational targets as well as a binary format for import into downstream Essbase cubes
    • Lacks some of the rich export flexibility better suited for Report Scripts or @JExport
    • Performance
      • Fast for data stored in Essbase
      • Poor performance with Dynamic calculations
    • Considerations
      • Only supports BSO cubes
      • No support for attribute dimensions
      • Relational targets must be valid to the Essbase server.
      • Need to have Essbase 9.3.1 or greater
  • Essbase GRID API
    • Programmatic solution that uses the Essbase Grid API written in C++
    • Grid API is what is used for Essbase Excel Add-in
    • Support for BSO and ASO
    • Performance
      • Very Fast with good C code
    • Considerations
      • Can run on both the Client and the Server with C Libraries being available.
  • Excel VBA Macro w/ Excel Add-in Description
    • Use the Essbase Excel Add-in and Visual Basic for Application (VBA) to extract data.
    • Create a basic programmatic loop in Excel VBA:
      • Pull and intersection of data via Essbase Excel Add-in and VBA
      • Write returned results in the Excel worksheet to a text file
      • Pull a new intersection of data and repeat process
    • Performance
      • Good
    • Considerations
      • Client side only and requires Excel be running.
      • Difficult to use in a batch process
  • Hyperion Application Link
    • Infrastructure software designed for Hyperion to move application content between disparate systems
    • HAL’s support for Essbase includes the export of data and metadata
    • Supports the automatic generation, running and management of integrated Report scripts (only)
    • Graphical in nature but designed for a technical audience.
    • Performance
      • Slow due to reliance on the Essbase Reports Scripts
    • Considerations
      • Requires HAL infrastructure to be available
      • Deployable in both a server and client environment
      • Supports multiplatform environments –Windows, Unix, Linux
  • OlapUnderground Outline Extractor
    • Written by Essbase developers to simplify tasks
    • Utility to easily extract the Essbase outline from BSO and ASO applications
    • Works great on smaller and large data sets
    • No product support
    • Performance
      • Average to other tools, can be really fast implemented correctly
    • Considerations
      • It is an Open Source tool and license
      • No product support
      • Windows Client
      • Needs access to an Essbase Client and Uses Essbase VB API
  • Oracle Data Integration
    • ODI’s support for Essbase includes the export of data and metadata
    • Supports running and management of integrated Report Scripts, MDX scripts and DATAEXPORT calc scripts
    • Export results from Report Scripts, MDX and DATAEXPORT are staged to an ASCII file and subsequently loaded into a Relational DB
    • Designed for IT
    • Performance
      • Poor to Excellent depending on export method used (Report Scripts, MDX or DATAEXPORT.
    • Considerations
      • Deployable in both a server and client environment

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *