Financial Data Integration With Hyperion Essbase

Follow us on Twitter & LinkedIn for more tutorials, case studies, and articles.

Publication Date: 05/15/2019
Share This Article

Our Fortune 500 Oil & Gas client was in negotiations with a competitor regarding a potential acquisition and merger. It was important that the client’s financial data be mapped to the acquiring company’s metadata structure prior to the merger.


A Fortune 500 industry leader and ongoing client positioned for acquisition.


Paradigm SES was selected to manage this project based on the quality of work delivered by the Paradigm SES team in prior projects and their familiarity with the complexity of the client’s data.

The Challenges

  • Integrate client’s key financial data with that of acquiring company in anticipation of Merger and Acquisitions-related due diligence and integration
  • Clean data and map it from client’s metadata structure to that of acquiring company
  • Convert client’s quarterly forecast to acquiring company’s 12-month forecast
  • Replicate acquiring company’s profitability cost management allocation process
The unique design of each company’s data structure would make the one-to-one mapping process extremely complex; thus, extensive data cleanup would be required.

The companies’ forecasting models were also different. Thus, another phase of the project would require the development of the a new 12-month forecast to model that of the acquiring company. Lastly, a new profitability cost management allocation process had to be established in just a few months to mimic the acquiring company’s process, a project which had taken 5 years.


Our client considered a number of vendors for this project, including two of the Big Four professional services firms. Based on the quality of work delivered by Paradigm SES in prior projects and the familiarity of the Paradigm SES team with the client’s data, Paradigm was selected to manage the integration. Quickly, Paradigm SES established a 5-member team to work with both companies’ IT, finance, integration, and divestiture organizations. Over the next 4 months, the teams would work together across all project phases.

Software Solutions used in this project include:

  • Hyperion Essbase
  • Hyperion Planning
  • SmartView
  • Financial Reporting Studio

Financial Data Mapping

Understanding both company’s data structure was key to a successful mapping process. Data had to be mapped according to the acquiring company’s metadata structure but using the client’s data. Accounts—such as Company Code, Cost Center, Product Line, Function, and Entity, along with functional accounts—had to be matched. Select accounts had to be redefined, and logic had to be created to accurately map like accounts to one another.

Forecast Development

Paradigm SES had to recreate the logic for a process that mimicked the acquiring company’s 12-month forecast. The client had been using a quarterly forecast. Team meetings were held with the stakeholders of the Financial planning and analysis team to complete the data requirement process. Once the BRD (Business Requirement Document) was prepared and accepted, a proof of concept was created in a Hyperion Planning environment. The team created 7 data forms (Excel and online interface) through which end users could prepare their forecasts. The calculation logic ran automatically when the user hit “Save,” eliminating any additional steps. Maxl scripts were used to extract data from the planning team and load into the reporting Essbase database. Lastly, 18 additional reports were updated for the acquiring company.

Profitability Cost Management Allocation Process

Paradigm SES worked quickly to understand the acquiring company’s cost allocation process. A process that had taken 5 years to create, the allocation model was based on highly complex revenue-based calculations. Within 2.5 months, Paradigm SES was able to replicate the complex allocation process for the client. The acquiring company’s allocation process was built using DRM and an Oracle database; the logic was run within Oracle and eventually loaded into Essbase. The replicated allocation process was created solely within Essbase; the logic was based on calculation scripts written within Essbase and DRM was used to establish the metadata structure.


All phases completed on time and within budget expectations.

Financial Data Mapping

Working in conjunction with the finance team, Paradigm SES was successful in scrubbing the data, mapping it, and validating the 100% accuracy of the data.

Forecast Development

A new 12-month forecast model was developed with enhanced functionality and the elimination of possible user errors. These enhancements were not present in the acquiring company’s original model. User Acceptance Testing (UAT) was successfully completed in one week.

Profitability Cost Management Allocation Process

Paradigm SES delivered this process in 2.5 months for 1 product line. The acquiring company had taken 5 years to develop this process for 8 product lines. By replicating everything within Essbase, Paradigm SES eliminated 4 additional steps from the acquiring company’s original Oracle process. This change cut the end-to-end process time by 1 day and reduced the chances of possible error.


“We have worked with Paradigm SES on a number of projects, and I can say that this one is the most impressive to me in terms of their team’s response to the complexity and on-demand nature of our requirements. We were counting on the success of this integration as the foundation for a seamless financial and operating transition between companies. The Paradigm SES team identified some critical steps in the process that hadn’t been anticipated, and they effectively led teams from both companies through each step to ensure a complete transfer of knowledge.” -Senior Director

John Doe