April 13th, 2009 by Mark Rittman
If you work in the Oracle BI industry, the tools we work with tend to break down into two types; those that require largely technical knowledge, and those that require you to know a bit about the business. Discoverer for example is just a query tool and you can learn it end-to-end without needing to know anything about how it’s used. Oracle BI Enterprise Edition is the same, whereas the BI Applications typically require you to know as much about the source system it works with as about the tool itself. The Hyperion toolset is a bit like this but much more weighted towards the business side; Essbase and tools like Essbase Studio, WebAnalysis and SmartView can be learnt as just a bit of technology, but most Hyperion developers at one time or another have had a background in accountancy, planning or other financial functions as you’ll need this to properly understand the role of applications such as Hyperion Financial Management, Hyperion Strategic Management, Hyperion Profitability and Cost Management and Hyperion Performance Scorecard. These are obviously not tools you’d pick up on a wet Sunday afternoon and have a play around with for the fun of it.
Hyperion Planning has always interested me though, as it’s built on top of Essbase and at one time or another, most of us have made financial plans. They might be to work out if we can afford to buy a new car, or pay off a credit card, or we might have been in charge of a department and asked to plan out staff numbers and payroll for the year. As such, its a process that most of us can identify with, and if like me you’re a director of a company it’s something you could actually see yourself using. So what is Hyperion Planning then, what’s it used for and what’s the technology underneath it? More importantly, is it something you could set up yourself as a bit of home learning and how much does it build on your Essbase (and ETL) experience?
Over this and two other postings, I’m going to be looking at Hyperion Planning firstly, by explaining what it is; secondly, by taking you through a demo that you can set up yourselves, and thirdly, by going through a planning system I’ve created myself that shows how the development process works. But first of all though, what is Hyperion Planning?
Hyperion Planning is an application that sits on top of the Essbase OLAP server and is used by organizations to perform planning and budgeting. Using the tool, you work with standard dimensions such as period, year, scenario, entity and accounts to which you can add other custom dimensions such as product and geography, with an Essbase cube being built using these into which you add budgeting and planning data. Budgets are typically either set top-down or bottom-up, with users entering their budget data either through web-based forms or through Microsoft Excel, with Essbase then being used to spread and allocate numbers down the hierarchies or add them all up after the budgeting process has finished. As such it differs from Essbase in that it’s an application rather than just a tool, and over the past few years Oracle and Hyperion have added specialized versions to handle workforce planning, capital asset planning and so on.
Hyperion Planning itself is a web-based application that sits in an application server between the Essbase database and the client tools that work with it.
Hyperion have had a budgeting solution of one kind or other for many years, firstly through the Hyperion Pillar application it acquired in 1994 and then through the Planning product the Arbor developers to put together after the two companies merged back in 1998. As such, Planning has been going now for over ten years with its biggest customers in terms of numbers of users being BT, Dell and Telenor (the Norwegian telephone company). Being developed by ex-Arbor staff (Arbor being the original vendor of Essbase) Planning is mainly based around the Essbase multi-dimensional OLAP engine, with a relational database being deployed alongside to hold metadata, textual data and so on. Now that Oracle have named Planning as their strategic planning and budgeting tool and are moving EPB and OFA customers onto this platform, and Microsoft recently threw in the towel with PerformancePoint, Hyperion Planning is the clear market leader in the planning and budgeting market with it’s main competitors being IBM/Cognos (formerly Adaytum) Planning and the Cartesis product SAP/Business Objects acquired back in 2007.
As a developer, how you work with Planning depends on whether you want to create a “Classic” planning application or whether you want to take advantage of some of the new tools that have come along with EPM 11.1. If you start off with the (simpler) Classic planning route, you use the Classic Application Wizard (either from EPM Workspace, or launched from with the Planning web application) to initially define the application datasource (the combination of Essbase server and relational database schema that will hold your planning data) and then your Planning application itself, setting the currency(s) that you’ll work with, the periods you will plan over and the number of plans you’ll create, with plans then translating into Essbase databases.
Again carrying on down the Classic Application route, you then log into the Planning application, initialize the database and then define the additional custom dimensions that you’ll want to plan against. Once this is done, you use Administration Services or one of the other data loading tools to load data into the Essbase cube to create your initial planning data, which will then be allocated down via business rules before it’s made available to end users to start working with.
Once you’ve set the database up, you then create workflow process that take users through either web-based forms or Excel spreadsheets that collect the budget data in. The workflow bit is something new if you’re just starting to work with Essbase, and is an important part of the product as much of budgeting is about the to-and-fro between the centre and managers as the budget is being set (someone once said that budgeting goes on until management get the figures they want or the staff lose the will to live). A typical workflow process looks like this:
with the web-based data entry forms looking like this:
The web-based forms have come on a lot since their initial introduction, and now you can have budget data in the top half of the screen with actuals in the bottom, or you can have budget in the top and drivers (rate of growth, inflation percentage etc) in the bottom. Many users prefer to use Excel as the data entry tool instead though, as you can run this offline, add automation features if you’re responsible, for example, for lots of cost centres. This can be either through the old-school Essbase Excel Add-in, or through the new SmartView add-in, shown below.
Once the budgeting process is complete, business rules are run again to aggregate the figures up the various hierarchies to produce the consolidated budget, which can be fed into other Hyperion tools or added to the actuals data shown in Oracle BI Enterprise Edition.
Hyperion Planning has been through some fairly major changes over the last release, with the Classic Approach to application creation being complemented by the new EPM Architect approach, where a new web-based application is used to define dimension, hierarchies, calculations and so on across multiple Hyperion products (Planning and Financial Management at last count). In previous releases there were a number of tools you could use to create the metadata, dimensions and so on that Planning would use (you’re meant to define dimensions etc outside of Administration Services, in a metadata-driven approach) including customized versions of Informatica and some home-grown tools, but now Oracle have adopted Oracle Data Integrator as the strategic ETL tool for populating Planning applications and a limited-use version of the tool is bundled with Planning.
So why would you use Hyperion Planning instead of good old spreadsheets? Well spreadsheets are certainly a handy tool for running the numbers, and they are particularly helpful if you want to define business rules and calculations on-the-fly and automate the planning process using VBA etc. Where you start to come unstuck though is when you want to distribute the planning process amongst many users, and where the planning process goes through many iterations, which becomes increasingly difficult to control and orchestrate when you’re working with dozens or hundreds of spreadsheets. Having Essbase in the background helps when you want to model some scenarios, and allocate money up and down the various hierarchies, and at the end of the process it’s a relatively simple process to roll all the numbers up and feed them into the rest of the financial management process. I can see, for example, how for Rittman Mead, even though we’ve only got operations in a few countries and a handful of staff, we could benefit from the control, workflow and automation that this product could bring, and when you then factor in the ability to create new scenarios on the fly, model your numbers in a cube to perform some what-if analysis based on a number of drivers, it’s a considerably more powerful and scalable solution than a bunch of spreadsheets.
So, that’s an overview of how the product works. In the next few days I’ll write about how you can get the standard Planning demo up and running, and after that I’ll take the data we usually use for our OBIEE demos and training and create a planning scenario out of it, with the numbers it finally generates being fed back into our OBIEE semantic model. Any questions in the meantime (or feedback on what I’ve got wrong) just add a comment as usual.