Developing a Reporting Solution with Hyperion Essbase – Part1

Identifying the need of the Essbase

This is the first part in the series - Developing a Reporting Solution with Hyperion Essbase considering an ERP System PSGL as source.
we will discuss the issues and solution design approaches in all the phases of developing an Essbase application.

We could do all our reporting from the ERP system but why should we use Essbase to do it?
The answer is related to the necessity for Speed, Performance and complicated business logic processing. The need for multiple users to generate reports simultaneously and quickly.

How can we expect speed and performance from Hyperion Essbase?
Essbase — an OLAP Server, handling all data storage, caching, calculations, and data security. It supports drill down, drill up, slicing and dicing.
key strengths of Essbase - Fast reporting and analysis, powerful calculation engine for the complicated business logic processing, organized in dimensions of data – a.k.a the business view, write back ability.
Essbase is a blank sheet of paper and Essbase can be designed and tailored for any business, any industry, any application.

To explain OLAP - On-Line Analytical Processing, we will first look into OLTP.
This stands for On-Line Transaction Processing. ERP (Enterprise Resource Planning) systems, such as Oracle E-Business Suite, SAP, Peoplesoft etc, are considered to be OLTP systems. That is, they are designed and optimized primarily for Transaction Processing.

OLAP is a category of applications or technology for collecting, gathering, processing and presenting the multidimensional data for analysis and management purposes.
On-Line Analytical Processing (OLAP) differs from On-Line Transaction Processing (OLTP) in that its primary function is to perform analytical processing for the transactions occurring in the OLTP Enterprise Resource Planning system.
Although ERP systems provide a reporting capability, there are significant advantages of using an OLAP system for analyzing and reporting the transactional data.

In short, the functional requirements for OLAP are as follows:
-> Rich dimensional structuring with hierarchical referencing
-> Efficient specification of dimensions and dimensional calculations
-> Separation of structure and representation
-> Flexibility
-> Sufficient speed to support ad hoc analysis
-> Complex business logic processing
-> Multi-user support
-> Robust security

System Speed and Performance
Consider a typical General Ledger, which contains the raw data for most management and statutory reporting. Usually, ERP systems store the individual transactions (i.e. GL journals and postings from sub-modules such as Accounts Payable, Purchasing, Fixed Assets, and Inventory etc.) but they do not store the account balances.

When you run a report which requires account balances, these are calculated by adding up the individual transactions for the period(s) in question. Although this is usually a task undertaken by the underlying relational database, and is relatively efficient, this still places a considerable load on the system.

This might be acceptable outside of normal working hours (assuming that the system is not accessed 24/7 from multiple time zones) but if many such reports are run during the working day, performance can deteriorate for other users performing the tasks for which the ERP system is designed and optimised, namely entering transactions.

The relational database on which the ERP system sits is not designed to 'understand' the tree hierarchies for each segment of our GL accounts.

The report user typically understands that a number such as Cost of Goods is made up of a number of separate elements, such as Purchases, Stock Movement, Carriage and Settlement Discounts. These elements may also each be represented by a number of separate GL accounts.
If the user needs to analyse the detail behind one of these numbers on a report (i.e. drill-down), it is usually necessary to run a completely separate report to get the detail, placing a repeated load on the database and further reducing system performance.

Apart from the system performance problems with this approach, the production of the report itself can often be a time-consuming process. Certainly the generation of the numbers is not likely to be up to the speed at which the user can request and analyze them.

Considering Essbase as a Solution
Typically, data is extracted from the General Ledger (or other modules) either every night, or perhaps just at Period End, depending on the requirement.

The account balances (not the individual transactions) are loaded into the Essbase cube which not only knows the account codes, but also contains the groupings or consolidation levels to provide the higher-level 'roll-ups' such as the Cost of Sales example we discussed earlier. Not only are the individual account balances stored, but also the system calculates and stores the rolled-up numbers as well.

It is now possible for the report user to interrogate the OLAP system directly. He or she might start with a high-level report showing, for example, a complete Profit and Loss account. The report is generated almost instantaneously as all the required numbers are immediately available from the database without additional calculation.

If further detail is required on a number such as Cost of Goods, the user can drill-down on the number and get an immediate response showing the individual balances which make up the rolled-up number.

Lastly, the extra load on the ERP relational database caused by ad-hoc report generation is entirely removed and replaced by a single extraction routine which can be controlled centrally to ensure that it is run at an appropriate time to minimize user inconvenience.

Conclusion
The design of the OLAP reporting systems and the interfaces between them and the ERP systems are critically important - effort in this area should not be underestimated.
It is also sensible to integrate the thought processes behind OLAP system design, reporting requirements and GL Chart-Of-Accounts and data conversion at the earliest possible stage of an implementation project, particularly if the OLAP tools are to be used for Budgeting and Forecasting in addition to their more traditional reporting roles.