Extracting data from Hyperion Essbase

I recently did a project where we moved dimension and fact data from Hyperion Essbase to SQL Server Analysis Services. In general we cannot use an OLAP cube as a data source for another cube. In this series of blog, I will explain all the steps and challenges faced while migrating data from Hyperion to Analysis Services.

The first challenge was to extract data from Hyperion. I did some research and came up with the following options.

• Use Star Integration Server from Star Analytics to extract dimension and fact data.
• Use Outline Extractor from Applied OLAP to extract dimensions. Fact data extraction is not available in Outline Extractor.
• Create SSRS reports using Hyperion as a data source and generate CSV or Excel files
• Use Essbase built-in export formats to export Fact data OR Use script to generate extract file

Star Integration Server extracts data for both dimension and fact data. Next option, Outline Extractor only extracts dimensions data. SSRS report option will not work as it requires selecting each measure separately to creating output files. In my project, there were around 800 account measures so this approach was not feasible. The fourth option, using Essbase only exports fact data.

Star Integration Server is the best option as it works for both dimension and fact data but it is very expensive. Client was not interested in purchasing Star Integration Server license. We decided to go with a combination of 2nd and 4th option.  The Outline Extractor works fine for dimensions. For fact data we started with built-in export formats but the extract file had data for all granular levels. There was duplication of data and our fact values were not matching with the Hyperion. We then decided to use script in Essbase to generate data only for the lowest granular level (Level 0).

We used the following script to generate extract file for fact data.

Using Outline Extractor for dimensions and Hyperion Essbase script for fact data solved the issue of data extraction. In next blog of this series, I will explain about the data extract structure and how I loaded data to staging and then to the data warehouse.