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.

"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.

I had the honor of presenting at the September 2009 user group in the Hyperion track for those who attended the Ohio Valley Oracle Application User Group in Louisville.  The presentation focused on Maxl best practices, and how to integrate the results of maxl into other technologies.  The presentation was driven from a project completed late last year.  A recent client spent a tremendous amount of time verifying the results of daily and monthly processes. 

Adding some consistency in the Maxl scripting, I integrated the results of the scripts, including the error and process logs, with .NET to produce a website that summarized the state of nearly 50 processes.  Administrators were able to view a web page that showed real time status of all their applications, including links to error logs.  The increased productivity of the administrative staff created a positive ROI in the first month of use.

Download the presentation

I started my career as an accountant and never had any aspirations of doing the same thing all day, every day.  While I struggled through what I considered monotonous job functions, I developed a knack for finding ways to automate my job.  As a result, I didn’t have to do repetitive tasks and I had more time to learn the business. Don’t get me wrong, accountants possess a unique set of skills and talent that I respect trumendously. It is a critical function of any business.  So, kudos to you accountants!

When I get involved with building new applications with Hyperion, or updating existing models, it pains me to see accounting, finance, and the staff who support Hyperion continue to perform repetitive tasks that dominate their time.  It can drive talented people to look for employment elsewhere.  It inflates salaries and jeopardizes credibility with an increase in human error. It also deteriorates the quality of business analysis, introducing a greater risk of poor decisions.  Inflated expenses and poor management decisions can be catastrophic to any business.

Automation in accounting and finance areas is critical to productivity.  Being able to support the constant push from management to become better and faster with less resources is always challenging.  If your Hyperion environment is supported outside of finance, IT areas are under just as much scrutiny.  How much of your time, or staff, is spent generating reports?  How much more time could be spent helping analyze the business and adding value to management decisions?  From an IT prospective, how much of your time is spent supporting the environment and responding to requests where answers could be automatically generated?  If 20% of your reparative tasks were eliminated, how much more effective you would you be?  How much more experience would you gain?  How much more marketable would you be both internally and externally?

Many of the possibilities for automation are never discussed.  Most people don’t even realize how much time they spend performing repetitive tasks that could be automated. Some think it would be impossible to automate and others think it would be too expensive.  The examples below were both accomplished in a matter of weeks.  The investment had a positive return within months.  The non-monitory gain was felt immediately. 

Don’t think of why it can’t be done.  Think of a solution without constraints and ask, “How can we get there?”  With the proper guidance and background, massive improvements can be accomplished with minimal effort.

To spark some thought, think about these situations.

Monitoring Essbase jobs and keeping users informed of system status

Are you responsible for managing all the jobs that run on Essbase server(s) and are constantly asked if something has completed, or when something will complete, by your users?  Some organizations have a person dedicated to managing this information flow. 

I implemented a solution at a large financial institution to conquer this problem.  The result was a solution that required zero effort to maintain and provided a summary of over 50 processes in one web page.  It gave the status of the process, when it last executed, if there were any errors, and a link to the log and error files if they were required.  Access was granted to all the Essbase administrators.  Another page was available for all users that displayed the status of the application, when it was last loaded, when it was last calculated, and several other useful sources of information.

The days of searching through folders on multiple servers are now long gone for system administrators.  Users are more informed and support tickets diminished substantially.  The estimated time savings was 4-6 hours per day. 

This solution was built using existing technologies, limited to Maxl, Windows scripting, ASP.NET, and access to an IIS Server to host the website.  It was 100% maintenance free and built dynamically enough so that new applications could be added and applications could be renamed or deleted.  All this is possible without changing any code or processes.

Distribution of reports

A large international organization distributed over 150 reporting templates to an equal amount of people in the US and abroad.  These templates were distributed daily through the monthly close of business.  The daily adjustment cycle finished updating the reporting Essbase application around 2 AM.  When a finance staff member arrived around 8 AM, the work began.  The template was refreshed and saved for each of the 150 business entities.  Emails were then sent to each of the 150 people with their respective report.  This process took about 6 hours every day it was performed.

Using existing technology, a process was created to traverse through a spreadsheet that had 2 columns, which was maintained by finance.  The first was the business unit, followed by the email the report was to be sent to.  Using the Essbase toolkit and Excel, a process was initiated as soon as the database was updated that opened a spreadsheet that included the template, changed the business unit, refreshed the template, saved it, and emailed to the intended recipient.  This process took less than 1 hour and all the reports were distributed before 4 AM.  Customers received their reports earlier (those in Asia a day early), no human errors were made, and the finance staff now had an additional 6 hours to add value.

“Pre-Installation Requirements”

In installment #1 of this guide, we reviewed the architecture considerations and defined a simplistic architecture to use as a reference moving forward.  I recommend you read the previous post before you pick up this one.  I also recommend reading

“Oracle Hyperion Enterprise Performance Management System Installation Start Here Release 11.1.1.2.pdf (128 pages)” from the Oracle Documentation Library.

To reiterate our general approach, the Hyperion architecture establishment and installation activities in our organization cover the following five areas.

1. Defining an Architecture – Work with the client to define the hardware, software, and the distribution of Hyperion components
2. Provide Pre-Installation Requirements – Provide the client with a detailed list of activities prior to the installation
3. Installation – Running the installation and configuration utilities
4. Validation – Perform all functional activities necessary to validate the environment readiness
5. Documentation – Provide the client with all the details of the environment as it is configured.

In this post, I will go through step 2 that the Hyperion architect, should deliver.  Steps 3-5 will be available in the coming weeks.  For the sake of simplicity I will be using the example of a common installation, primarily Hyperion Planning, Hyperion Financial Management (HFM), and the core BI applications.

As part of any installation, some items need to take place before the Fusion Installer is started.  I like to create a checklist of things that need to be done.  Often times these things are out of my control and I will rely on Database Administrator, Network Administrators, and other System Administrators.  This checklist contains the following elements.

• Web Application Server Specifications
• Relational Repository Information
• General System Administration
• Network Information
• Additional Components
• DCOM Configuration
• IIS and .NET Configuration

I’ll start with the Web Application Server Specification.  Once the web application server platform is chosen from the table below, the installation and configuration often falls on System Administrators.  Items such as clustering, system account management, and JVM setting are managed outside of the Hyperion installation.  Other times, I’ll get admin access and manage it myself.  The first item to do is to validate the application server is certified.  This is directly from Oracle Enterprise Performance Management System - Supported Platforms Matrices “Oracle Enterprise Performance Management System, Fusion Edition Release 11.1.1.2)” in the Oracle document library.  I recommend reviewing this document.  It can change from release to release.

I request the URL and authentication information since this will be needed during the deployment.  If I am doing a manual deployment, I will request contact information from the web application server administrator and work in collaboration on the deployment.

The next item on my checklist is to get the Relational Repositories Information set up.  This is mostly straightforward.  In general, I like to create a tablespace/database for each component ((Hyperion Foundation, Essbase Admin Services / Business Rules, EPMA, Planning, Financial Management, etc).  A distinct tablespace/database for each component makes it easier to manage in my opinion.  Although it may not be strictly necessary, the documentation does not seem to be clear on the matter.  I say ‘better safe than sorry’.  For the installation and deployment, I’ll need credentials for each table.  Based upon some Q&A, I’ll make initial size recommendations. 

The target installation servers have a General System Administration checklist containing the information that I’ll need to execute the installation.  This is made of the following items.

• Operating Systems version/build
• Account on each server to run the Hyperion services and account requirements
• External Authentication information (MSAD, LDAP, or OID if applicable)
• Drive/Volume information identified for installation of the Hyperion software.
• DCOM and .NET account information if HFM or FDM are to be installed

Next, I identify the Network Information necessities for appropriate communication between servers.  This includes IP addresses, DNS information, validation of name resolution, trace between servers, subnet configuration, etc.  This is vital so the components can communicate via Fully Qualified Domain Name, Short Name, and IP address.  Some components use different variations of name resolution probably because the components were developed separately and have not been fully standardized.

In addition to the Hyperion Software, Web Application Servers, and Relational Repositories there are a few Additional Components that need to be installed.  A PDF writer is needed for Reports Server to render .pdf reports in Workspace.  This can be GhostScript or Acrobat distiller.  I suggest referring to the “Start Here” documentation to see what is currently supported but we often go with GhostScript due to its cost.

For the Windows Administration, we provide the DCOM Configuration information needed to support FDM, EPMA, and FDM.  This includes the DCOM account information, permissions, and authentication information.  Although this is spelled out in detail in the “Start Here” manual, I like to provide step-by-step information with screen shots since DCOM is often confusing…well it is to me at least.

The last thing we review is the IIS and .NET Configuration.  IIS is often not installed as part of a standard OS build.  We make sure this requirement is specified, ensuring .NET is installed, and validate it is the appropriate version.

As with any installation, I recommend the Installation Architect read, and re-read, the Hyperion Manuals on there own rather than relying on this information or intuition.  It can always change and your installation may have some caveats that I have not covered.  For our purposes, with all the above activities completed and validated, we should be ready to start laying out the binaries and start the Hyperion Installation.  We will review the Fusion Installer and Hyperion Configuration Utility in our next installment. 

Comparing the current period to the prior period is relatively easy to accomplish in Essbase, and is often required when creating a Cash Flow hierarchy.  Assume the following scenario.

An outline exists that includes a Year and Time_Period dimension. The Year dimension includes 2007, 2008, and 2009.  The Time_Period dimension includes Full Year, Quarter 1 through 4, and all 12 months.  The dimension type for the Time_Period dimension has to be set to Time.  A dimension named COA (chart of accounts) holds the general ledger account structure. Below is an example of the Time_Period dimension

To enable a dynamic approach to solving this problem and minimizing the maintenance required as new years are added, an understanding of the following two functions is required.

@PRIOR
The PRIOR function provides a way to compare a member outside of the Time_Period dimension in multiple Time_Period members.  For example, accounts for July could be compared to June, or Quarter 2 could be compared to Quarter 1.  There are two parameters that this function accepts.  The first is the member to get the prior value for.  The second is the number of periods you want to shift the comparison.  If @PRIOR(“Asset123”,1) is used, it would provide the value for the period previous to what you had selected in the Time_Period member.  So, if June was selected, it would provide the value for May. If the formula was @PRIOR(“Asset123”,2) and June was selected, the result would be the value for April (2 periods back).  The function uses members at the same generation, so @PRIOR(“Asset123”,1) would provide the difference between Qtr2 and Qtr1 if Qtr2 was selected.

So, what happens if January or Qtr1 is selected?  There is no previous member for these.  This is where the second function comes in to play.

@MDSHIFT
MDSHIFT is similar to PRIOR, but it lets the calculation reference members across dimensions.  Where PRIOR only allows references on one dimension, MDSHIFT allows references to move across multiple dimensions.  If the user expects to see the different between Jan and Dec of the prior year or Qtr1 to Qtr4 of the prior year, MDSHIFT enables that to happen without hard coding the script.  Again, the goal is to have a script that doesn’t need to be maintained.

MDSHIFT accepts a set of parameters.  If your shift needs to occur along one dimension, it requires one set of parameters.  If your shift needs to occur along more than one dimension, it will accept multiple sets.  The function’s first parameter is the member you are evaluating, just like the PRIOR function.  The next set of parameters is what can exist multiple times if you are shifting along multiple dimensions.  The set consists of three parameters, of which the first two are required.  The first of the set is the number of positions to shift.  The second is the dimension to shift on.  The third is a range of member to use to shift along.  If this is left blank, Essbase uses level 0 members. 

To get the prior value for Jan, or Dec of the previous year, it would be MDSHIFT("Asset123",-1,"Year",,11,"Time_Period",).  The first parameter is the member to evaluate.  The next two parameters are used to reference the previous member (-1) in the Year dimension.  Since the Year dimension members are level 0, the fourth parameter is not required.  The next series, or set, references Dec (+11, or move forward 11 from Jan) of the Time_Period dimension.  The last parameter is not required since we only want to reference level 0 members again.

Putting it all together
If we put these two functions together with a basic if/then/else statement, we get a dynamic formula that won’t need to be updated as we progress through time.  It would look something like this:

If(@ISMBR("Jan"))
     /* if Jan, then we have to compare Jan in the current year to Dec [shift +11] in the prior year
         [shift -1]  */
     "Asset123" - @MDSHIFT("Asset123",-1,"Year",,11,"Time_Period",);
ELSEIF(@ISMBR("Qtr1"))
     /* if Qtr1, then we have to compare Qtr1 in the current year to Qtr4 [shift +3] in the prior year
         [shift -1]
         The last parameter includes a range since we are not using level 0, which is the default  */
     " Asset123" - @MDSHIFT("Asset123",-1,"Year",,3,"Time_Period",("Qtr1","Qtr2","Qtr3","Qtr4"));
ELSEIF(@ISMBR("YearTotal"))
     /* if Year, then we have to compare to last year [shift -1] */
     " Asset123" - @MDSHIFT("Asset123",-1,"Year",);
ELSE
     /* all other members, which would include Feb through Dec */
     " Asset123" - @PRIOR("Asset123",1);
ENDIF;