1 / 31

Extraction and Downstream use of Essbase Data: The Good, The Bad and the Ugly

Extraction and Downstream use of Essbase Data: The Good, The Bad and the Ugly. Quinlan Eddy – Founder & CTO. Disclaimer. These slides represent the work and opinions of the presenter and do not constitute official positions of Oracle or any other organization.

sawyer
Download Presentation

Extraction and Downstream use of Essbase Data: The Good, The Bad and the Ugly

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Extraction and Downstream use of Essbase Data: The Good, The Bad and the Ugly Quinlan Eddy – Founder & CTO

  2. Disclaimer These slides represent the work and opinions of the presenter and do not constitute official positions of Oracle or any other organization. This material has not been peer reviewed and is presented here with the permission of the presenter.

  3. Star Analytics and Presenter Profile Star Analytics • Software Company Founded in 2004 • Released Star Integration Server in April 2005 • Over 25 Fortune 500 referenceable customers Quinlan Eddy • 14 Year Arbor / Hyperion Experience • Employed at Arbor / Hyperion (1995 – 2001) • Former consultant with core focus on integration and automation consulting in the Hyperion space

  4. Agenda • Exporting data from Essbase – What are the options? • Explore each option from various perspectives: • Description • Performance • Technical Requirements • Architecture Considerations • Task Sequence • Other pertinent information & Sample code / screen shot • Summary • Demonstration of Select Options • Q&A

  5. What are the Options to Export Data From Essbase? • Classic: • Essbase Native Exports • Scripting: • Essbase Report Scripts • Multi-dimensional Access (MDX) Language Scripts • “@JExport” Custom Defined Function (CDF) & Calculation Scripts • DATAEXPORT Calculation Scripts • Programming: • GRID API • Excel VBA Macro w/ Excel Add-in • Packaged Applications: • Hyperion Application Link • Informatica Essbase PowerConnect (DIM) • Oracle Data Integration (ODI) • Star Integration Server (SIS) • OLAP Underground Outline Extractor (Honorable Mention)

  6. Option #1 – Essbase Native ExportsDescription • Description: • Essbase kernel based export to a ASCII file • Export data at Level 0, Input and ALL • Data can be exported in standard or column formats • No dynamically calculated or attribute calculation data is exported • Performance: • Excellent • Technical Requirements: • Built-in function of Essbase; limited technology knowledge required. • Architecture Considerations: • Can only be run on the Essbase server. Output paths must be valid to the Essbase server.

  7. Option #1 – Essbase Native ExportsSample Step #2 – Use the Export file as a back up file or column format can be used for imports Step #1 – Write a MaxL or Esscmd script to invoke the Export

  8. Option #2 – Essbase Report ScriptsDescription • Description: • Intersections of data and formatting options are defined via the Essbase Report Scripting language. • Typically used with low volume batch reporting or lightweight data integration • Support for BSO and ASO • Performance: • Fine for small quantities of data with very limited scoped intersections (< 10,000) • Typically poor for large data volumes • Technical Requirements for Data Export: • Need to be able to write scripts and know how to run via ESSCMD or MaxL • Architecture Considerations: • Can run on the server or client. Simply requires access to an Essbase Client (Administration Services, ESSCMD, MaxL)

  9. Option #2 – Essbase Report Scripts Sample Step #1 – Write a Script Step #2 – Execute in a client

  10. Option #3 – Multi-dimensional Access (MDX) ScriptsDescription • Description: • Intersections of data and formatting options are defined via the MDX Report Scripting language. • Typically used for low volume and lightweight data integration between MDX aware applications that dynamically generate MDX queries. • Utilizes the platform-independent XML for Analysis specification • Support for BSO and ASO • Performance: • Fine for small quantities of data with very limited scope of intersections (< 10,000) • Typically poor for large data volumes • Technical Requirements for Data Export: • Need to be able to write MDX and know how to run via MaxL • Architecture Considerations: • Can run on the server or client. Simply requires access to an Essbase Client (Administration Services or MaxL)

  11. Option #3 – Multi-dimensional Access (MDX) ScriptsSample Write script Execute in a client (MaxL & MDX) (Essmsh)

  12. Option #4 – @JExport Custom Defined Function (CDF)Description • Description: • 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 • Only supports BSO cubes • Performance: • Excellent assuming proper calculation script writing procedures • Technical Requirements: • Need to be adept at writing calculation scripts and execute with any Essbase client. • Architecture Considerations: • Can only be run on the Essbase server. Output paths or relational targets must be valid to the Essbase server.

  13. Option #4 – @JExport Custom Defined Function (CDF)Sample Write Script Execute in a Client (Calculation Script) (Output)

  14. Option #5 – DATAEXPORT Calculation ScriptsDescription • Description: • 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 • Only supports BSO cubes and no support for attribute dimensions • Lacks some of the rich export flexibility better suited for Report Scripts or @JExport • Performance: • FAST! (Typically faster than @JExport or any other option) for stored data • Poor performance with Dynamic calculations • Technical Requirements: • Essbase 9.3.1 or greater and knowledge of writing calculation scripts • Architecture Considerations: • Can only be run on the Essbase server. Output paths or relational targets must be valid to the Essbase server.

  15. Option #5 – DATAEXPORT Calculation ScriptsSample Write Script Execute in a Client (Calculation Script) (Output)

  16. Option #6 – GRID APIDescription • Description: • A programmatic solution that uses the Essbase Grid API written in C++ • Grid API is used for the classic Essbase Excel Add-in • Support for BSO and ASO • Performance: • Fast with good code • Technical Requirements: • Be a C++ Programmer • Architecture Considerations: • Export data on both the client and the server assuming proper C libraries are available.

  17. Option #7 – Excel VBA Macro w/ Excel Add-inDescription • 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 • Technical Requirements: • Essbase Excel Add-in and knowledge of programming in Excel Visual Basic for Applications (VBA) • Architecture Considerations: • Client side only and requires Excel be running. • Difficult to use in a batch process

  18. Option #8 – Hyperion Application LinkDescription • Description: • 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 more designed for a technical audience. • Performance: • Generally poor because of reliance of Essbase Report Scripts. • Technical Requirements: • Requires HAL infrastructure to be available • Architecture Considerations: • Deployable in both a server and client environment • Supports multiplatform environments – Windows, Unix, Linux

  19. Option #8 – Hyperion Application LinkSample Design a HAL Process move Essbase Data and Metadata from Source Cube

  20. Option #9 – Informatica Essbase PowerConnect (DIM)Description • Description: • Infrastructure ETL software to move application content between disparate systems • DIM’s support for Essbase includes the export of data and metadata • Supports the automatic generation, running and management of integrated Report Scripts (only) • Export results from Report Scripts are then loaded into Informatica PowerCenter or can be used as independent files • Designed for IT; little or no finance orientation. • Performance: • Generally poor because of reliance of Essbase Report Scripts. • Technical Requirements: • Requires DIM / Informatica infrastructure to be available • Architecture Considerations: • Deployable in both a server and client environment • Supports multiplatform environments – Windows, Unix, Linux

  21. Option #9 – Informatica Essbase PowerConnect (DIM)Sample Step #2 – Setup ‘DSN’ and execute to view results Step #1 - Design Extraction via Graphical UI and Report Script Designer

  22. Option #10 – Oracle Data Integration (ODI)Description • Description: • Infrastructure ETL software to move application content between disparate systems • 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 • Designed for IT; little or no finance orientation. • Performance: • Poor to Excellent depending on export method used (Report Scripts, MDX or DATAEXPORT. • Technical Requirements: • Requires ODI infrastructure to be available • Architecture Considerations: • Deployable in both a server and client environment • Supports multiplatform environments – Windows, Unix, Linux

  23. Option #10 – Oracle Data Integration (ODI)Sample • Step #2 - Execute in a client or • via ODI automation framework Step #1 - Write a Report / MDX / DATEXPORT script & create a "Knowledge Module"

  24. Option #11 – Star Integration Server (SIS)Description • Description: • Turnkey software specifically designed for the export of data, metadata and security from Essbase, Planning, HFM and Enterprise into a variety of formats • Auto generates target relational star schemas based upon the design of source cube • Designed to be used by a typical business user to integrate Hyperion data into a data warehouse or relational reporting environment • Flexible design of exports including dynamic calcs, attribute calcs and on-the-fly transformation • Demo available at: http://www.staranalytics.com/demos/sis_demo • Performance: • Excellent! As good if not better than DATAEXPORT. • Technical Requirements: • A business user who understands their source Hyperion application • Architecture Considerations: • Deployable in both a server and client environment • Supports multiplatform environments – Windows, Unix, Linux

  25. Option #11 – Star Integration Server (SIS)Sample Setup Extractions in a Friendly UI Quickly view Data, Metadata To a relational or text target & Security from source cube

  26. Option #12 – OLAP Underground Outline Extractor Description • Description: • Utility to easily extract the Essbase outline from BSO and ASO applications • Does not extract data • Performance: • N/A • Technical Requirements: • Windows Client • Architecture Considerations: • Needs access to an Essbase Client and Uses Essbase VB API

  27. Option #12 – OLAP Underground Outline Extractor Sample Set up Outline Extract Parameters Creates Metadata Output

  28. Extracting Data from Essbase – Technology Matrix

  29. Extracting Data from Essbase – Business Considerations

  30. Extracting Data from Essbase – Technical Considerations

  31. Contact Information Quinlan Eddy Founder and CTO Star Analytics, Inc 650-539-4608 qeddy@staranalytics.com

More Related