Using EPMA for Managing Hyperion Planning Applications

The Irish OUG BI Special Interest Group are running an Essbass-themed event on November 11th in Dublin, and as well as delivering a session on Oracle BI EE and Essbase Integration I've also agreed to do a session on the technology behind Hyperion Planning. This particular session is aimed at developers like myself, who generally work with Oracle's BI technology tools and are wondering how this "Planning" application works; how does its architecture look, what are the typical development tasks that are associated with it and how do you get data in, and out of it.

I covered a basic introduction to Hyperion Planning a few months ago, where I outlined the basic idea behind the product and how it fitted into the EPM Suite framework. To recap, Hyperion Planning is a part of Oracle EPM Suite and is currently at version 11.1.1.3. It is used by organizations to plan and budget within their organization, and where it differs to ordinary Essbase is that it's a packaged application designed to do one thing (planning), rather than being just an OLAP server or a toolkit like Java. Planning uses a combination of Essbase (for multi-dimensional analysis, allocations, forecasts and calculations) and relational storage (for additional planning metadata), and is generally accessed by end-users either through the Planning Web Application (part of EPM Workspace), or through Excel via SmartView or the classic Excel add-in.

At a very high-level, Planning fits in with Oracle's general EPM/BI architecture at the "application" level, along with the Oracle B Applications.

At a product level, Planning sits underneath the EPM Workspace and Smartview/Essbase Add-in and is administered using both it's own Web Application and through Enterprise Performance Manager Architect, a general metadata management/lightweight ETL/application management tool that first appeared with Hyperion System 9. Planning as I said stores its planning data in Essbase, additional supporting metadata in relational tables, and can take data from applications, data warehouses and master data management tools, or from files and other structured data sources.

Note that EPM Architect, the Planning Web Application and infact Workspace itself are all delivered through the Hyperion Workspace application, so they all appear in the same user interface but accessible through different menu options, but conceptually they can be thought of as different applications. A typical end-user session in the Planning Web Application looks like the screenshot below, note the fact that the forms (of course) support write-back, and its this ability for users to submit their own numbers coupled with the workflow element of the product that sets it apart from a standard sales analysis-type Essbase application.

So, the question is, how do we build a Planning application? The actual "application" itself is stored in Essbase as a regular application, with an outline, dimensions, attributes and data stored within it, with everything stored in a single Essbase database within the application. If you opened up Essbase Administration Services and took a look at the application, a typical one would look like this:

But you can't load a Planning database like a regular Essbase database, as you have to manage it using the Planning application itself. Planning acts as a wrapper around Essbase and manages dimensions etc within its own repository, publishing these to the Essbase server when you deploy a Planning application. Essbase is effectively hidden under the covers (at least in the current EPMA-enabled release, in previous versions you still needed to know a lot about Essbase to get it working) and you use Planning, and from System 9, Enterprise Performance Manager Architect (EPMA) to manage applications instead.

Data in Planning breaks down into two main areas; dimension data, such as entity, period, scenario and ones that you define yourself, and data, the actual numbers (actuals, forecast, budgets) that you load into the database. Planning (in its standard form, there are variations for workforce and capital expenditure planning) ships with six standard dimensions:

Entity, for organizational units, countries or similar (current calculations are based around these)
Scenario, so that you can try different types of plan or planning methods
Version, typically top-down or bottom-up, but also allowing you to split plans into public ones and ones for internal use
Period, such as financial period, month, financial quarter etc
Year (typically split from period to make plans easier to work with)
Account, the measures in the plan such as profit, margin, costs etc

In addition, you can define your own dimensions to allow you to budget by product, for example.

When you load data into a Planning application, the actual numbers go into the Essbase database, whilst a support relational database holds all the textual data, workflow steps, plan status and so on. Up until System 9, you would create Planning applications using the Planning administration tool, which allows you to define the application in metadata before deploying it to a suitable Essbase server. This approach is preserved for backward compatibility in System 9 and EPM Suite 11.1 (a.k.a. System 9.5), and I walked through an example in a previous posting where I created an application using what is now called the Classic Application Wizard. This works pretty well (indeed, most Planning administrators still use this approach for reasons I'll outline later on), but it's not the "strategic" way of doing it and we can't take advantage of the metadata management features of EPMA if we go down this route (though we can upgrade classic planning applications to EPMA ones, if you like, in order to take advantage of these features).

So after running through the upgrade myself on one of my classic applications, I thought I'd run through the process of creating a new one from scratch, just using EPMA. The first hurdle with this is that EPMA needs Microsoft Internet Information Services to run, and so I dug out the Windows CD and installed it (feeling a bit dirty in the process), then got all the services up and running.

Hyperion tends to create lots of services anyway (at last count, including Planning, there were 31 Hyperion-related services running on my Windows machine) but as you can see from the screenshot above, EPM Architect and Calc Manager take up eight of their own. The different services for EPMA hint and what the product actually does, as EPMA actually consists of a number of components:

A Dimension Library, for holding metadata on the shared and local dimensions used by EPMA-enabled applications
An Application Library, for holding definitions of the various Essbase, Planning and HFM applications EPMA manages
A Data Sychronization Engine, for loading data (numbers) into EPMA-managed applications
Workspace, Security and Taskflow elements

EPMA has its own repository that holds all the metadata on the applications it manages, and as with classic planning applications you define the dimensions there first and then deploy them to Essbase. To start off them, as with the Classic Application Wizard you can define the basics of the Planning application using a wizard in EPMA, where you can say whether the application has one or more plans, whether it's multi-currency, what time periods it uses and so on.

Note the option in the above screenshot to "auto-create local dimensions". The first (System 9) version of EPMA made you define all dimensions as "shared", which meant that you defined them in the "dimension library" within EPMA and then made them available for use by various applications, each of which then used the same dimension definition. EPMA 11.1.1 introduced the concept of local dimensions, detached independent dimensions that only exist within a particular dimension, allowing you to have different definitions of entities for example in different planning applications. Classic applications that are imported into EPMA have by default local dimensions, and local dimensions are the default when setting up fresh applications directly within EPMA, however if you are looking to standardize data and access across all of your planning applications you'll probably want to move towards shared ones. For me though, I created my application using the standard dimensions and in shared mode, choosing to create period and year ones at the same time.

As well as creating the standard dimensions, I also wanted to create a custom, "product" dimension to allow me to plan across my product line. This also was created as a local dimension, again using the wizard, as shown below.

So now I've got my basic dimensions including one for Accounts, to hold the measures that I'll be planning against. What I haven't got at this stage though are any members for my dimensions, which I'll need to hold details such as the scenario name, entity names, period and products.

If the dimensions are small (scenario, for example), you can define them directly within EPMA in a similar manner to Essbase Administration Services. In the screenshot below, I'm adding a child member to the Accounts dimension:

Doing this allows me to build up the accounts that will hold my actuals, budgets, forecasts and so on.

Repeating this for Version and Scenario allows me to quickly add the members for these dimensions, and even Entity is straightforward as there are only a few territories where my company is operating.

Period and Year have their members automatically created for you by the EPMA Application Wizard, and so the only dimension left to create members for is the Product dimension, which won't be easy to create easily as it has lots of members. When I created a planning application using the Classic Application Wizard I was able to use the Outline Load Utility to mass-create dimension members using a flat file as a source, but you can't use this utility when managing a Planning application using EPMA (nor can you use ODI or DIM, strangely enough). So how do you get large amounts of dimension members into a Planning application when you are using EPMA?

It's probably worth taking a moment out to understand how data and metadata flows through Planning when EPMA is involved. As you can see from the diagram below, data and metadata from the various sources goes first into interface tables or files before being accessed by EPMA, which then takes this data, maps it and then uses it to construct the applications that you want to create.

So what we need to get to grips with then, to bulk load dimension members into our product dimension, is either the interface tables or the interface file structure, and the Dimension Library part of EPMA to bring them in. In my case, the data that will define the members in my dimension is held in a flat file, to start I select the Dimension Library tool within Workspace, and create what's called a new "profile".

The profile will contain the information on the file that I'll be importing, so I start by naming the profile, pointing it to the file containing the list of members, and selecting a particular application rather than the Shared Library, thereby making the dimension local to that application.

Now the tricky bit for me was getting the data in the input file in the correct format for the Dimension Library application to read. Unlike the Outline Load Utility which uses one format, input files for dimensions into EPMA use another, and after a bit of experimentation I got one together that firstly defines the dimension that we'll be importing, then the list of dimension members, then the hierarchy within the dimension.

See the "Managing Application Metadata" / "Working with Dimensions" section of the EPM 11.1 documentation for full details on the dimension file format.

Once the file is imported and then processed, you can then check back into the EPMA Dimension and Application libraries to see the newly created dimension members and hierarchy. In my case, my products are all now showing as I'd expect, and it's time to deploy the Planning application to Essbase.

Deploying it was the easy bit, as it takes care of the creation of the Essbase outline directly from within EPMA, meaning that you only really need to use the Planning Web Application to create the forms and workflows/tasks. Before you can start working with forms though, there's one more task to do - to load the data (i.e the actuals, the baseline planning data) into Essbase. Getting the initial data ready for Planning is actually a two stage process; firstly, you usually want to load some initial data in, and then secondly you process, calculate and otherwise manipulate this data to allocate, for example, top-level budget figures down to individual departments based on headcount, which in classic planning applications you'd do using Hyperion Business Rules and in EPMA-managed ones, you'd use the Calc Manager. Starting off with the data load though, you've got a couple of options on how to get the initial data in.

Probably the easiest way is to do an old-fashioned data load into the Essbase database. Unlike dimensions which can only be loaded from EPMA, data can be loaded in whichever way you see fit, although if you bypass EPMA you'll miss the logging, management and other benefits you get when you manage all your loads using this environment. Doing a data load in this way is pretty straightforward though, you can for example select the dimension in Essbase Administration Services, right-click on it and select Load Data, then point it towards the file containing your data.

Your file needs to hold the data in a particular format (see this previous posting for more details) but it's easy to understand and only takes a few minutes to set up. The "proper" way of doing it though is to use the Data Synchronizer feature within EPMA, which can bring in data from applications such as Financial Management, in the future it'll link to Oracle GL, but for now we can point it towards the interface tables I mentioned previously or files, as I'm going to use in the final example.

Data Synchronization, like the Dimension and Application Libraries, is accessed from the Workspace menu.

Once the application is loaded, you start by defining a new External File Definition (or a Data Interface Table Definition, if you are bringing data in from interface tables).

Then, in a process familiar to Warehouse Builder developers, you sample the file and go on to specify which dimension each file field refers to.

Then once the file definition is complete, you then use the tool to define a new "data synchronization", which maps the incoming fields to the dimensions in your application, with the remaining field containing the data for the specified account.

So there you go. Once you've set all of this up and brought some data into the Essbase database, you can follow the same steps in the Planning Web Application to define the web forms and task list that the application will use, and from that point onwards the planning application is the same as far as end-users are concerned (though you need to make any dimension metadata changes in EPMA and redeploy as necessary, rather than make the changes in the Planning Web Application).

So how did it all pan out, compared to creating a Planning application using the Classic Application Wizard? Well I was discussing it with Venkat as I went through the process, and it was interesting to see how restrictive using EPMA can become once you start using it. Tools such as ODI and DIM (the adapters for Informatica) only work with classic applications, which means that you can't make use of ODI's ETL capabilities when working with EPMA Planning applications. You can't use the Outline Load Utility to load your dimension metadata, you have to use Profiles and the Dimension Library, but you can use regular data loads from EAS if you don't want to use Data Synchronization.

EPMA is clearly the way that Oracle want us to go though, and there are are obvious benefits if you have a whole estate of Planning, Profitability, Financial Management and Essbase applications to manage. It's also worth thinking about how EPMA might work going into the future - will Essbase dimensions continue to be managed using this tool, could there perhaps be some merging between the Semantic Model that OBIEE uses, will we ever get to that nirvana where we have one repository, one dimensional modeling tool, one set of metadata for all our Oracle Bi tools? Who knows, but for now this is how planning applications using EPMA are initially put together.