If you have worked with Hyperion Financial Reporting, odds are good that you’ve come across report requirements for Rolling Years and Periods, that is to say a report that spans periods from multiple years. This is an easy build if your data source includes a single “rolling” dimension for ‘Year’ and ‘Period’ – you utilize the “RelativeMember” function to move up or down the hierarchy. The difficulty arises when programming this same logic into a report with separate ‘Year’ and ‘Period’ dimensions. A solution utilizing “Trigger” columns is shown below.

The use of what I call trigger columns allows for Hyperion Financial Reporting to display rolling Years and Periods, whether your requirement is for a 2 or 12 month rolling report. The Trigger section of the report requires both the ‘Year’ and ‘Period’ dimension to be columns on the report, while any dimension; such as Account or Product, can be included on the rows. The steps below detail a rolling 5-month solution.

Step 1:  Add data columns for all 12 periods (Jan-Dec).

These columns will function as the Trigger section, essentially telling the other columns what the end-user selected for Period. This is important because later sections of the report require knowledge of the selected Period to ultimately decide what is displayed.

Keys:

• These 12 columns MUST be Data columns.
• These 12 columns MUST be hidden.
• The Period member selection MUST be set to “Current Point of View for Period”.
• The Year member can be any member – not relevant to the trigger.

• Optional: Overwrite the Period POV cell with the 12 periods (Jan-Dec) as shown below.

Yesterday, Oracle announced the official launch of the Enterprise Performance Management 11.1.2 Release.  This release introduces some major enhancements to the existing Hyperion applications, along with three new products – Disclosure Management, Financial Close Management and Public Sector Planning and Budgeting. 

I’ve had the opportunity to see the release live during a partner launch event and have been very impressed thus far.  Over the next few weeks, In2Hyperion will begin to look at this release and share how to take advantage of new functionality. 

View the press release below.

http://www.oracle.com/us/corporate/press/066183

More information is also available on the Oracle website, which has been updated to include information for the 11.1.2 Release.

http://www.oracle.com/us/corporate/press/mediakits/epm-system-release-media-kit-066160.html.

As Enterprise Performance Management and Business Intelligence systems become adopted as the core decision support mechanisms within organizations, the need for transparent, fact-based decisions increases.  It not enough for these systems to provide the voluminous amount of data to the end user for analysis, but to tie data and decision inputs to the collaborative decisions that these systems support.

Although the organizational adoption of this style of decision making may face challenges, the technological groundwork is already in place.   Oracle’s addition of Annotation Service to Financial Reporting 11.1.x allows the capture of shared information against reporting objects and data.  This tool allows for threaded discussions and comments within EPM Workspace.

Let’s take a look at how users can utilize this tool against a Financial Report for a Planning application.

Whether in a finance organization or a technical role, most of us have had the need to create sample data to use to test Hyperion systems.  In2Hyperion is sharing a tool to make this process more efficient.  By defining a numeric range, the number of columns, and the number of rows, an excel spreadsheet will be generated with the appropriate random data.

This tool can be access at http://www.in2hyperion.com/Tools/RandomNumbers.aspx.

Many Hyperion Planning administrators are eager to customize the Planning application.  Questions are always posted on the Oracle Technology Network forums as to how and what is customizable.  As with any technology challenge, with the right resources and enough time, anything can be customized.  It is unrealistic to think that projects have an unlimited number of people and time to create a completely customized solution.  However, there are a number of things that are developed in Hyperion Planning to support user customization.

• Planning includes templates that control the layout and content of PDF reports of data forms, data form definitions, task lists, and planning units.
• Hyperlinks can be added to the Planning Tools page to support quick access to specific pages
• The appearance of Planning can be customized by changing the appropriate style sheets, which are files that control the UI of the Planning application.
• Templates can be changed to personalize text, colors, and images in the Planning interface
• Workflow tasks can be changed so each state has a unique color.
• Workflow states (Not Started, Approved, etc.) can be personalized so the state more accurately represents the business naming convention
• Workflow actions (Start, Reject, etc.) can be personalized so the action more accurately represents the business naming convention
• Custom spreading patterns can be created.

Future articles will be posted that will provide a step by step approach as to how each of these customizations is accomplished.  The Hyperion Planning Administrator’s Guide will give an overview of how each of these customizations is accomplished as well.

Backing up Essbase can be accomplished in a number of ways.  Some methods suit some organizational cultures better than others.  It is hard to argue that one method is better than another for this reason.  Below are two methods, and the pros and cons of each.

There are a number of factors that must be considered.  If the environment uses some of the new Hyperion tools, like EPMA, then one must allow consideration for the synchronization of the warehouse that holds the data for EPMA.  Where the different Hyperion applications (Shared Services, the web server, etc.) that work together reside is also a factor.

To minimize the complexity of this discussion, only information related to Essbase will be discussed.

Backup the entire server

Pros:  An image of the entire server is available in the case of disaster recovery and is normally in sync to that point in time of failure
Cons: Speed, cost, and data availability

Taking an image of the entire server is one option.  This will provide the most secure backup strategy.  If there is a hardware failure, getting back to the point of failure does not require a server rebuild.  This method is probably the quickest solution to restore all Essbase applications.  Price, speed, and data availability must be considered with this solution.  Taking an image of a server can be very time consuming and quite often, Essbase must be turned off for this to occur without skipping critical files.  Because a large amount of data is backed up, a large amount of storage is required. The time Essbase is down can have a significant impact on the people using Essbase.  There can be a very expensive price tag for the amount of tape and/or SAN that is required.  To effectively image a server without significant downtime, techniques like shadow copy or data mirroring are likely used.

Backup critical Essbase files

Pros: Speed, cost, data availability
Cons: Recovery time is sometimes longer, more effort if a complete system failure occurs, and data from the most recent backup to the point of failure is lost

The files required to be backed up to recover from a catastrophic event are actually very small in size.  The bulk of the amount of data related to Essbase is the pag and ind files, or the data and index files.  These files, in most environments, consume at least 90% of the total space.  If these are ignored during the backup process, the process can be much faster, far less expensive, and Essbase is not required to be off for the backup to occur.  Although this method can take longer to restore an entire server, it can be quicker to restore a few applications.  In most situations, a faster, cheaper solution, where the availability isn’t negatively impacted, is a far more palatable option.  This is only an option if you have either the data that sources the databases or data exports (input or level 0) of the Essbase databases.  If these are available, the databases can rebuild the pag and ind files.

Deciding on a backup method

Determining the best option boils down to cost and resources.  Taking an image of the server requires at least 2 times more disk space, a more complicated network/hardware infrastructure, and far more resources to build and store sufficient backup versions.  What is gained is an up to the minute backup.  If the cost associated with this method outweighs the cost of having to rebuild the data that was loaded between the time of failure and the last backup, then this solution is the best option.  In my opinion, it is hard to justify the investment in the capital required to support this for what little is gained.

First, disasters rarely happen.  With the RAID and SAN solutions today, disk failures that cause data loss are not the main reason a server fails, a hardware component failure is.  If the component that fails is replaced, the data doesn’t have to be restored.

Second, if a database becomes corrupt and unusable, a complete reload of the data is required.  Many times corruption can exist, unnoticed, in a database for weeks.  If the data is not available to reload, it is possible to lose weeks or months of data.

Third, if a disaster does occur, any data sourced from another system can be recreated.  Remember, the only data required is the data that has changes prior to the most recent backup, which is normally the previous night.  The data loaded by users, either through Hyperion Planning web forms or spreadsheets (Excel Add-In or SmartView), also exists somewhere else.  It might be frustrating for users to enter it again, but the data does exist and can be restored, normally with minimal effort.  In very large environments, this backup method can save millions of dollars.

Whether the decision is made to mirror the server, backup the critical Essbase files excluding the data consolidations and index files, or some method in the middle, it is wise to test the disaster recovery plan.  There is nothing worse than restoring from a backup only to find out that it is useless.

The second installment of this topic will be dedicated to how and what is required to have a secure DR plan if the pag and ind files are ignored in a backup strategy.

Fragmentation occurs naturally when a database is used frequently by adding, deleting, and modifying the data within it.  The more changes occur, the more fragmented the database gets as data becomes scattered through the pag files, and the size of the database becomes inflated.  The index files have to compensate for this, and what starts as a simple map becomes a spaghetti mess.

If you are unfamiliar with Essbase’s storage method, here is a brief overview.  Essbase has two sets of files related to the data stored in a database.  The numeric data is stored in files with an extension of pag.  Essbase also has files with an ind extension.  These index files are used to store the pointers to the data in the pag files.  As data is requested, Essbase must read the index files to know where the data is located in the pag files.

The result of a more fragmented database can have drastic effects on size and performance.  If you have a database where performance continues to decrease, fragmentation might be the source of the problem.  Performance degradation can occur over weeks or months, but can also occur much more frequently.  Databases with frequent data loads, or updates, can be impacted within a day.

A great way to identify the impact fragmentation is having with a database is to export your data (level 0 in most cases), reload it, and execute the process in question.  By exporting and reloading the data, fragmentation can be completely eliminated.

For more information about pag or ind files, please refer to the database administrator’s guide provided by Oracle.

Over my journey through the Hyperion toolset, there are a plethora tools I have developed that make repetitive tasks easier, make some tasks unnecessary, and provide functionality that doesn’t exist otherwise.  I am going to devote time to add these to the in2hyperion BLOG so that others may benefit.  You can find a new menu item that reads tools that will take you to the tools section of the BLOG.

The first free tool added is a utility that will convert Hyperion Essbase business rules’ exports, which are in the XML format, to a more readable format for documentation.  This tool will extract the name, description, and syntax/formula for all the business rules in the export.

“Installation and Configuration”

In installment #1 and #2 of this guide, we reviewed the architecture considerations and pre-installation requirements.  If you haven’t read the two previous post or haven’t read the Hyperion “Installation Start Here” guide, you’ll want to be sure to do that.

With this installment I’ll review the Installation and Configuration activities necessary for a Hyperion 11.x environment.  The installation and configuration are separate items.  The installation can takes place first and it only lays out the files to run the system.  The configuration ties everything together, creates repositories, deploys applications, and creates services.  This will cover both including the following items:

• Hyperion Fusion Installer and How it Works
• Preparing the Fusion Installer
• Using the Fusion Installer
• Hyperion Configuration Utility

The companion Hyperion Documentation for this post is either of the following documents found in the Oracle Documentation Library:
Oracle Hyperion Enterprise Performance Management System Installation and Configuration Guide Release 11.1.1.x
Oracle Hyperion Enterprise Performance Management System Manual Deployment Guide Release 11.1.1.x

You probably are not going to read them in their entirety since they are rather lengthy but they are very useful in fully understanding what is going on and priceless for complex environment or when things don’t go well.

Hyperion Fusion Installer and How it works.

So let’s get started on this installation already.  One of the great features of Release 11.x Fusion Edition is the Fusion Installer.  It is a nice application for guiding you through the installation.  The first thing to do is download the Fusion Installer and copy it to each server in your architecture.  The Fusion Installer is only the shell for the rest of the installation.  Under the Fusion Installer create a folder called “assemblies”.

Preparing the Fusion Installer

You’ll next need to download the remaining Foundation Services as well as any other applications you are using.  For our example we are going to assume the client is using Foundation, Planning, and HFM.  You are probably looking at something in the neighborhood of 4GB to download.  Each download, when unzipped contains a group of folders looking something like this:

Each server will need the appropriate assemblies copied to its own \<FusionInstaller>\assmblies directory.  This way, whenthe Fusion Installer starts, it knows what is available to install.  Some of the common components are needed on each server.  If you are missing something, the Fusion Installer will let you know in the status window at the bottom application.  For details on which assemblies are required for each application, refer to the Installation and Configuration Guide.

Using the Fusion Installer

As you start the Fusion Installer you will see something like this:

I like to choose “Choose Components Individually” since it feels like I have a little more granularity.  At this point I’ll select all of the components I want to install on each server.  Once again, this is run on every server in the architecture.  The Fusion Installer only lays out the application files; it doesn’t need any information so the sequence of installation can occur in any order.  It seems to work pretty well when all the components on a server are chosen together.

The last thing to do is to review all the install logs for any errors.  It is much easier to catch them now than after the configuration is started before anything is written specific is written to registries and relational databases.  Once the configuration starts, you are committed.

Configuration

The first thing to do is to configure Shared Services.  After the installation is complete, each server will have a Configuration Application.  It can be launched on a Windows Server from Start >Oracle EPM Applications > Foundation Services > EPM System Configurator.  This application will guide you through the configuration with such things as creating and distributing Java applications, creating relational repositories, and building the Windows Services.  The EPM System Configurator displays the installed components and then you can select which components to configure.  It looks something like this

The first thing to do is configure Shared Services.  This needs to be done by itself and before any other components are configured.  As soon as this is complete, launch Shared Services and verify that it is working appropriately.  If it isn’t, it’s will be a long day.  If you are able to log in to Shared Services, it is also probably best to go ahead and configure any external authentication provider at this time.

When Shared Services is complete and verified, you can move from server to server configuring all the components.  The documentation says that you can configure all the components at once but this will attempt to configure all the selected products in the same relation schema/table.  The documentation also says that some of the repositories need to be separate.  I prefer to do it one at a time to be certain I can keep all the relational repositories separate and I can validate each component as it is competed.  I usually start with all the Foundation Services and then make sure Workspace functions before moving on to the EPM application like Planning and Financial Management.  The last thing to do is to redeploy Workspace so it is configured to proxy all the remaining Web Applications.

You will want to be careful with each screen to make certain every component is configured as you planned.  It is easy to keep hitting ‘NEXT’ only to find out you mixed your Calculation Manager Repository in with your Shared Services repository.

As with the installation, I like to review all the configuration logs on each server very carefully.  Better to catch an error now than later.  When I’m comfortable with the configuration, I shut everything down and bring it back up.  The start order is quite finicky.  The Oracle Installation and Configuration Guide has specifics regarding the start order but I usually do something like this:
1.    Shared Services OpenLDAP
2.    Shared Services Application Server
3.    Hyperion Annotation Service
4.    EPM Workspace Agent (CMC Agent)
5.    EPM Workspace UI (CMC UI)
6.    EPM Workspace Web Server
7.    EPM Workspace Application Server
8.    Hyperion RMI Registry
9.    Performance Management Architect Services

Process Manager automatically starts the following services:

•   Hyperion EPM Architect – Engine Manager
• Hyperion EPM Architect – Event Manager
• Hyperion EPM Architect – Job Manager
• Hyperion EPM Architect – .NET JNI Bridge

10.    Performance Management Architect Web Services
11.    Essbase Server
12.    Administration Services Application Server
13.    Smart Search Application Server
14.    Essbase Studio Server
15.    Provider Services Application Server
16.    Hyperion Financial Reporting – Java RMI Registry
17.    Hyperion Financial Reporting – Print Server
18.    Hyperion Financial Reporting – Report Server
19.    Hyperion Financial Reporting – Scheduler Server
20.    Web Analysis Application Server
21.    Performance Management Architect Application Server
22.    Performance Management Architect Data Synchronizer Application Server
23.    Financial Reporting – Web Application
24.    Calculation Manager
25.    Planning Application Server
26.    Financial Management
27.    Hyperion Financial Management DME Listener
28.    Hyperion Financial Management Web Service Manager
29.    Hyperion Financial Data Quality Management – Task Manager

Assuming everything starts, we’ll discuss validation in the next part.

The EPM Reformation

Enterprise Performance Management (EPM) is undergoing the same transformation that Enterprise Resource Management (ERP) systems brought about in the early 90s.  As complimentary solutions such as Asset Management, Payroll, and General Ledger converged into one consolidated, modular system, so too are the solutions that comprise EPM (Financial Consolidation, Budgeting/Forecasting, Strategic Planning, and Reporting).   Along with the obvious benefits and economies of scale that accompany this transition, we must be aware of the pitfalls associated with the design, implementation, deployment, and support of these mission critical applications.

EPM as a Compliment to ERP

Just as Enterprise Resource Planning (ERP) solutions are an essential component of the Back Office operations of every Fortune 500 Company, Enterprise Performance Management systems are complimentary in nature and provide insight into the operational and financial effectiveness of the organization.  Metaphorically speaking – If the organization was an automobile, ERP would be the engine and EPM would be the gauges.  Carrying the analogy forward, nothing prevents us from operating a car without a speedometer, gas gauge, or heat indicator.  Furthermore, when the car is running well (at least in so far as we perceive) we have little interest in these instruments.  But, what about when we hear the first ping in the engine, or the car doesn’t respond when we hit the accelerator.  Worse yet – A seize up (aka Recession).  In the absence of information conjecture prevails and we are forced to speculate as to the cause of the problem. Without EPM, organizations are essentially operating their business in a similar fashion; reactive at best, “from the gut” at worst.

The Problem

From a business (functional) perspective, EPM solutions are categorized by the convergence of Analytic Application such as Financial Consolidation, Budgeting/Forecasting, and Strategic Planning with traditional Business Intelligence Solutions hallmarked by Query & Reporting, Key Performance Indicator (KPI) Dashboards, and Enterprise Scorecards.  As EPM has evolved from it’s siloed upbringing as a departmental solution to the Enterprise-class solutions of today, the underlying technology required to support these applications has become more broad and in recent years increasingly more complex.  This evolution is both natural and expected.   Given the expansive use of EPM based solutions, technical constructs such as multidimensional databases, data marts, enterprise data warehouses, workflow engines, web services, SOA, Calculation Scripts, ETL packages, and Master Data (Hierarchies) have all become vital components to the architecture.

In so far as organizations appreciate the criticality of EPM solutions to their organization, there is a gross under estimate of the effort associated with deploying and supporting these mission critical applications.  This similar lack of effort appreciation is shared by the ERP implementation of the 90s.  How often did we hear about the $2 million dollar ERP solution that came in at $20 million or more?

Gaining an Appreciation

Few would argue that the ERP solutions of today have not brought about a degree of integration and consistency throughout the business.  The ability to integrate key operational back-office systems up and down the organization with the capacity to exchange data between functional modules without fear of inconsistency is certainly a hallmark of the ERP promise.  But, this integration did not come without a price.  The same can be said for EPM.

ERP and EPM are both the harbingers of consistency, transparency, and audit ability.  As such, they force the institution of standards and controls where they have not historically existed.   Furthermore, there is an illusion that these disciplines run contradictory to loosely coupled legacy processes that are thought to be more flexible, nimble, and sufficient for supporting the business.  Whereas this may appear to be true when viewing each process as a stand-alone, siloed operation (forecasting separate from budgeting, separate from financial consolidation, separate from operational reporting).  It is important to have the right perspective here.  As with traditional ERP solutions, to gain an understanding of the EPM value proposition, you must first rise above the individual business solutions that encompass performance management (i.e. Financial Consolation, Budgeting/Forecasting, Reporting, etc).  Only by viewing these applications from a holistic, integrated business perspective can you appreciate the business and technology economies of scale that accompany Enterprise-class EPM solutions.

The Point

EPM solutions if approached correctly must be seen as the acronym implies, “Enterprise” in scope.  Similar to their ERP counterpart, EPM solutions can and in many cases should be implemented modularly, but under the auspice of an overall Solution Deployment Strategy.  Notice the term “Solution” not “Application”.  Applications are but one component of the EPM strategy.  Others include: Technical Infrastructure, Data Management/Governance, Process Integration, Communication/Change Management, and Administration & Support.  When you view EPM solutions from this perspective it is hard not to appreciate the level of involvement required from Executive Leadership, The Business, and Information Technology.  Organizations must think of their EPM solutions as “ERP Projects”; enterprise enabling solutions that require the establishment of well documented and endorsed strategy that align with the corporate directive.   In this vain, EPM requires a realistic investment of resources, time and capital to be successful.   Then again, you could pull away from the car lot with a 1971 Pinto and hope no one hits you from behind…