Migrating from Oracle financial analyzer (OFA) to Oracle Hyperion planning

This article compares the two systems and highlights the similarities, differences and details what is to be gained by moving over to Hyperion Planning from OFA. It also looks at the methodology to migrate a current OFA application to Hyperion Planning, listing key areas and detailing how the migration steps can be completed.

Migration to Oracle Hyperion Planning from Oracle Financial Analyzer (OFA)

Introduction

Oracle announced the acquisition of Hyperion in March 2007. Prior to this, Oracle had announced that their Express database technology and OFA and OSA products were to be discontinued and that Express, OFA and OSA will cease to be supported from the end of 2010. Further to these announcements, Oracle had confirmed that the Hyperion Essbase database and Hyperion products, including Hyperion Planning are the recommended migration path for customers using OFA. In order to assist customers in their migration plans, this white paper compares the two systems and highlights the similarities, differences and details what is to be gained by moving over to Hyperion Planning from OFA. It also looks at the methodology to migrate a current OFA application to Hyperion Planning, listing key areas and detailing how the migration steps can be completed.

Comparing OFA And Hyperion Planning

Architecture and client – OFA is a client-server application based on the Oracle Express OLAP database with SNAPI client or Web based user interface. The OFA administration component requires a client installation. Hyperion Planning uses a combination of Essbase as the OLAP engine, with a relational data store (typically SQL Server or Oracle) for additional functionality. Administration is predominantly web-based in version 9.3x, with client-installed tools available for some aspects including Essbase administration. The user interface is either web-based or via ‘SmartView’ which allows data access directly into Excel, PowerPoint and Word.

Reporting – OFA’s reporting capability is within the application but formatting is somewhat limited compared to other applications. Use of the Excel-based SmartView client for Hyperion Planning gives far greater flexibility with report formatting. In addition, Hyperion Planning has an extensive and feature-rich reporting toolset available. This includes two key products we would highlight for OFA customers: (i) Financial Reporting Studio provides fully formatted print-quality reports (presented as HTML or PDF with the option to export to Excel); (ii) Web Analysis gives companies the ability to allow users to analyse data graphically through dashboarding applications. Reporting is delivered to users through the web. Input Workbooks – OFA uses data entry documents to allow users to input data and access the various planning tools available. In addition, different tools and functionality are available on the Web and client forms, so in some cases the tools required may drive the interface that can be used. In Hyperion Planning, data entry is via Web Data Entry Forms. These provide advanced functionality for entering data, including spreading and adjustment, running calculations to populate data based on rules (such as run-rate, linear regression, etc.), cell text to allow comments specific to the input intersection, supporting detail (for example, to allow a Payments cash flow account to be analysed by payment types that may vary by company and over time). Data forms can be exported to excel and data can be copied and pasted between Excel and forms to allow excel based data to be easily entered. In addition, SmartView allows data to be directly submitted from Excel and SmartView also allows for web forms to be directly opened within Excel. System Administration – OFA system administration is via an administration workstation – only one administrator can be logged in at a time, which can cause delays in some processes. OFA security settings are comprehensive, allowing users or groups of users to read and write specific data elements and to access individual documents or entry screens.

Hyperion Planning has a number of elements to administration, which combine to provide comprehensive control over a planning application. Primarily, administration is web-based, via the same workspace as users but with addition admin functions. Essbase’s administration services provide direct access to Essbase functionality. If planning is being used, then generally the maintenance happens in the planning environment with automated refreshes to the Essbase application. Shared Services (which includes security and user provisioning) is part of the web-based environment and allows maintenance to be efficiently handled in one place for multiple applications and products. Planning is fully multi-user including administration, using record and table level locking to control access when tasks between users could conflict. Workflow – There is no built in workflow in OFA. Planning contains workflow as a standard part of the application. Known as Process Management, it allows the administrator to control the process cycle. If this optional feature is turned on, units need to be started before they can complete data. This then goes through a process of promotion (submission) and approval. Data is not visible to higher levels until it is promoted, and cannot be amended by lower levels after it is submitted (unless the submission is rejected and re-opened). The workspace environment represents a fully customisable user portal, which allows the process cycle to be embedded into the workspace as a step by step workflow.

The Benefits of Migrating to Hyperion Planning

Oracle Support – Hyperion Planning is fully supported by Oracle as a strategic product. OFA support will stop at the end of 2010. Architecture – OFA uses the Express OLAP database, which is multi-read but only single-write, which means that there can only be a single stream of ‘tasks’, so a long aggregation task, for example, could potentially delay relatively trivial data input tasks and other users cannot see the changes until they have been processed. Hyperion Planning uses the Essbase OLAP database, which is a true multi-read and multi-write database, so changes to data are seen in a much closer to ‘real-time’ manner. In addition, the Essbase engine is efficient and fast, providing better performance on calculations and data reads. OFA uses client-server technology, and the technology requirements are relatively simple. Hyperion Planning requires a three tier approach with application, data and web server. This is potentially a more complex set up and may require more hardware and infrastructure. However, it provides significant benefits in terms of robustness, system maintenance and access. For example, for the majority of users access to all features is via ‘zero footprint’ web client so there are no ‘roll-out’ issues, application changes can be made available instantaneously, and there is considerable control over user interaction with the system. Logic – OFA can perform very simple aggregations based on single or multiple hierarchies through to very complex calculations using ‘models’ and the Express SPL code. Calculations can be triggered by end users’ data entry or run manually by the system administrator. Hyperion Planning provides default aggregation within the hierarchical outline as standard, without the creation of calculation scripts. This allows data to be accumulated (using simple operators such as +.-.*) to parent levels. In addition, individual members can have more complex formulae attached to them. In Hyperion Planning, the calculation script is more comprehensive and allows a full range of data modelling and manipulation, such as mathematical and statistical functions, allocations, rules based calculations, etc.

Reporting – The data selection tool is available in OFA when running every report. This allows for easy adhoc analysis and drilling for all types of users, however these are primitive in formatting capability. Hyperion offers a number of tools for reporting. Standard (Financial Reporting Studio) reports are of a ‘fixed’ format, so users cannot use these to for ad-hoc analysis. However, they include user selectable point of view items, and drill through on dimension members to lower levels, and it is possible to create highly formatted, presentation quality printable reports. Web Analysis offers full dashboard capability, and has considerable scope for user interaction.

Workflow – OFA does not have workflow functionality. The workflow in Hyperion Planning allows complete control over the reporting cycle with audit trail, locking of data, etc. Consolidation – OFA’s statutory consolidation features are relatively basic and require customisation to use. For example, OFA does not, as standard, do journaling, eliminations and full audit trail reporting. Hyperion Planning can provide statutory consolidation but likewise needs a degree of customisation. Hyperion Financial Management (HFM) and, for middle tier applications, Hyperion Enterprise are specifically designed with this in mind and have a high level of built in financial intelligence suited to statutory consolidation as well as management reporting.

Methodology of Migrating OFA to Hyperion Planning

The methodology we recommend is based on experience from our Planning and Budgeting consultants who have come from Hyperion and OFA backgrounds. The aim of the methodology is to minimise risk for the project, but also minimise effort and time. The full methodology divides each phase into activities – each activity is divided into tasks with deliverables clearly defined at each milestone. Is Hyperion Planning the Right Tool? The first step is to analyse the requirements of the application and decide whether the Planning tool is best tool for the job. For example there may be specific requirements (e.g. consolidation in some cases) for which a different tool is more appropriate. There may be other cases (e.g. GL ‘slice and dice’ reporting with no data entry or workflow) where a lot of the Planning functionality is not required and a more basic Essbase-based solution is more appropriate. Re-implement or migrate? The process we recommend is a mixture of re-implementing certain aspects of the OFA system such as the cube design, data entry screens, security and calculation rules, along with migration of other aspects such as dimensions and hierarchies. Using extraction utilities, we are able to extract data and structures easily from OFA for use in Hyperion. Some analysis of structures is required to ensure that the data extracted from OFA meets the more rigorous requirements within Essbase, for example an aggregate node can only appear in one hierarchy in Essbase. Data and structure design: The Essbase data model design will differ from OFA, although to the end user they will look very similar. For example a formula FDI in OFA is likely to map directly to Member Calculation or a Business Rule in Planning. We would determine the Essbase / Planning design at the early stage of the migration. Data Interfaces: Data interfaces will need to be re-engineered. We can assess the requirements and advise on the best tools and the best design for data integration. In addition to options above, we have our own data integration tools which can be customised to meet individual client requirements and which may prove to be more economic in come instances. Reports and Input Screens: Reports and input screens will need to be re-created in Planning, but report definitions from OFA can be extracted to assist in this process. Workflow: Workflow will have to be developed in Planning as there is no equivalent in OFA.

Typical Customisations

Custom Programs: OFA allows parameter-driven Express SPL programs to be run from the OFA front-end to perform specific tasks. Planning provides a similar approach through Business rules, which can be attached to web forms, and which can contain Run Time Prompts for user parameters. Automate distributions of rights to users: Usually where access to data or functionality is dependant on data values, e.g. ‘users with sales > 1M get a larger expense budget but have to plan expenses in more detail’. There is no equivalent built-in feature in Planning, and this would require additional customisation

ABC calculations: Driver based allocations, potentially also rule based and iterative calculations. Planning offers a range of functions within business rules. Payroll calculations e.g. public sector spinal calculations: Where staff get an automatic pay rise on the anniversary of joining . This type of functionality is commonly built into planning. Hyperion offers a pre-built Workforce Planning module that provides most of the functionality required around staff-related budgeting and planning. This can be customised if additional functionality is required.

Drill to GL: OFA does this via the ADI interface to the ‘live’ GL. No functionality in Planning to do this. Scheduling jobs: The OFA task scheduler allows tasks, e.g. large calculations, interfaces, to be scheduled at a convenient time. The Express administrator allows tasks to be run at a specific time and also allows recurring tasks to be scheduled. Covered through Reports scheduler and via Essbase’s scripting and command interfaces that allow scheduled tasks to be run. Report column customisations: A common customisation within OFA is to build a ‘reporting cube’ that gives commonly used calculations, for example act vs. bud %, YTD vs. YTD last year, this period vs. same period last year, market share, etc. This can be done in reports as calculated columns in Hyperion Planning. We can also achieve this efficiently through member calculations.

Migration FAQs

Some of my OFA models have very complicated calculations in them. Can I replicate these in Planning?
There are several levels of complexity in OFA models. The first is using standard OFA where complex calculations such as moving totals, qualified data references, etc. are defined within the model definition – for example: YTD = MVTOT(SALES, MONTH, YEAR)

These calculations can be replicated using Essbase business rules. The second level of complexity is where the model calls an Express SPL program – this would require our OFA consultants to analyse the program called to establish what it does, and then build an equivalent routine in Planning using appropriate tools. In most cases this is likely to be Business Rules. The Business Rules function list is pretty comprehensive (on a par with excel). In addition it has extensive ability to work with member selections, and has modelling orientated functions for statistical based forecasting, etc.

I use the GL Link to download actuals with segment values and hierarchies. Can Planning do this?
Yes, there is a range of Data Integration tools that can do this.

How do I move budgets from Planning to Oracle GL?
Oracle Data Integrator (ODI) can do this, or alternatively a data extract can be created and loaded back to the GL via Oracle interface tables.

Can Planning integrate with other GLs?
Yes – various tools (FDM, DIM, ODI) have adapters for Planning and main GLs and databases.

I have multiple sets of books – how can I do this in Planning?
You have to use a separate integration process for each one.

My OFA implementation is Multi-tier in OFA – how do you handle in Planning?
The first step is to establish the reason for the multi-tier application – possibly for performance or for other reasons. It may be that Planning does not need to be multi-tier as Planning can share services (i.e. multiple servers / CPUS) a lot more effectively than OFA so the performance issues that hit OFA may not apply. If the multi-tier is for security reasons, it may be that Planning’s security model will resolve it. IF we do need to go further however, we can have separate Plan Types. For example, we can separate the Workforce Planning into a separate plan type, so that people with access to this can see the detail by employee, whilst those that can only see the main plan type can only see staff costs at an aggregate (department or cost centre) level.

Yes – this is a strong feature of planning. Exchange rate tables are held by month by scenario and with scope for average, period end and historic rates. This allows not only multi-currency reporting, but currency restatements such as actuals @ budget rate. In addition, it is possible to build custom exchange rate routines. We have a portfolio valuation model where an investment can be made in a different currency from the currency of the investing company GL. The transaction is recorded at transaction rate, and we calculate the portfolio valuation in USD, by triangulating between the Ledger Currency, the Investment Currency and USD.

How does Performance compare to OFA?
The Express database on which OFA relies is inherently tuned for fast data retrieval, the downside of which is that calculations can sometimes be quite time consuming. In comparison, Hyperion Essbase, especially from version 7.2 onwards, has been optimised for storage, retrieval and calculation. This is principally based on its efficient handling of data sparsity in cubes. A correctly structured cube can retrieve millions of records in seconds, and typically calculations and aggregations take from a few minutes to half an hour. The exception is if the data is very dense, which sometimes happens if an error (e.g. data load or calculation) causes the ‘blank’ cells to be populated with zeros. The thin-client interface for OFA can sometimes be slow if large lists of values are required for presentation to the user. Hyperion’s web-based architecture has built in ‘thin’ technology to serve data efficiently as web pages rather than transmitting actual data files to the client. However, the performance will depend on factors such as infrastructure, bandwidth and data server/web server capability. Also, number of users can impact performance if it exceeds the recommended number for the implemented hardware specification.

Do I need to upgrade my hardware if I move to Hyperion?
It is possible to implement the full planning software on a single machine but there are various services that are processor intensive and which also require large amounts of data read/write activity. Therefore, the most significant implementation will have a multi-server architecture with three or more servers (plus the development and test environments). As the application uses web based services, network bandwidth will govern performance.

Do I need to upgrade my client PC’s if I move to Hyperion?
Planning has a zero footprint on the client, so if performance on the internet and any other web-based application is acceptable, this should not be an issue. SmartView works with all current and recent releases of Excel.

Is data scalability better than in OFA?
Express can handle large volumes of data – up to 20 million cells in an FDI is not uncommon but more than this can in some cases cause performance implications, particularly during aggregations. Essbase is highly scalableand will handle large data volumes. Scalability is enhanced with the aggregate storage model, which is suitable for even near real-time loading of data from transaction and operational systems and can load millions of records per minute. As the number of dimensions increases performance can be impacted (as with any other OLAP system). However, a 12 dimension, multi-million record cube with a foot print of several Gb would still aggregate in around 30 minutes. Incidentally, efficient disk storage means that a 1Tb relational database will take up only 1 to 2 Gb in Essbase.

How do I allow a user to manage metadata without giving them admin rights?
This is a bespoke customisation within OFA, for example allowing a Web Analyst user to add in dimension values. In Planning, it is also a customisation. You would not give users the ability to manually maintain data from a best practice point of view, but you could allow them to run integration processes which loaded metadata from a source system. You could also design something using an API based interface.

Can I run OS level commands from the system?
Yes, from Essbase, using built in MAXL scripts or using ESSCMD command line Planning user activity is web-based, which generally precludes execution of client or server side scripts without some level of customisation. However, you can add custom links to the workspace, enabling you to attach java or vbscript based scripts.

Can I create journals in Planning? How do I extract journals for posting to Oracle GL?
Functionality is not built into Planning (it is in HFM). However, it is common to create journal format web forms and reports which can be used to input journals, and to calculate journals which are then exported to excel or a flat file via reporting.

Summary

Redcentric provides migration and support services for both OFA and Hyperion Planning and would be happy to discuss how we can carry out a migration assessment on your current system.


Related Posts

redcentric

Redcentric

0800 983 2522 sayhello@redcentricplc.com