Many of the Hyperion Planning and Essbase users still prefer to use the Essbase Add-In in conjunction with, or in place of, SmartView. As you probably already know, deploying the Essbase Add-In in version 11 has challenges. There is over 2GB of data that is required and the installtool.cmd file is not a simple installation that most users can administer without help. Because of the size, deploying it in a distributed package is extremely challenging. There are some instructions on various BLOGs that explain a way to deploy it manually, with edits to the registry. Any time I work with a client and mention editing the registry outside an automated install, this option is quickly disregarded.

In version 11.1.2, Oracle|Hyperion has added a self contained executable for the Essbase Add-In! The download is located in the Hyperion Essbase’s download page.

Regardless of whether the perception of using SmartView for large queries is good or bad, the reality is that finance and accounting users require the ability to pull large volumes of information out of Essbase.  The only limit that I am aware of in the days of the Excel Add-In was the maximum number of rows Excel would allow (assuming the Essbase application cache settings were high enough to support it).  With SmartView, there is a limit.  The limit is controllable very easily, however.  The error that users may question an administrator follows.

"Cannot perform cube view operation. OLAP error (1020011): Maximum number of rows [5000] exceeded."

To increase the maximum number of rows a user can retrieve, or submit, edit the service.olap.dataQuery.grid.maxRows property in the essbase.properties file.  The default is 5000. While editing this property, it may be benefitial to evaluate the size if the columns (.olap.dataQuery.grid.maxColumns), which is set to 255 by default.

Once this is updated, restart the Hyperion services.

The location of the essbase.properties file is dependent on the version of Essbase installed.  Start by going to the server with APS installed. 

Location for version 9.3
     %HYPERION_HOME%\AnalyticProviderServices\bin directory

Location for version 11
     %HYPERION_HOME%\products\Essbase\aps\bin\

For those of you who still enjoy the flexibility of the Hyperion Excel add-in and have upgraded to Microsoft Office 2007, you are well aware that Excel no longer has toolbars or menus.  SmartView integrates with the ribbon methodology well, but the Hyperion Essbase Excel add-in doesn’t.  What used to be the Hyperion menu is stuck in the add-ins ribbon as a drop down menu.  It still functions the same, but users loose the quick access to functions that the ribbon methodology adds.

In2Hyperion developed a ribbon add-in that can be used in conjunction with the Excel add-in.  Nearly all the functionality exists and makes the options easier to use and are embedded in their own ribbon!  This still requires the Hyperion Essbase add-in to be installed.  It simply uses the existing functions and associates them with a ribbon button.

We have also added a few additional functions that make common tasks a little quicker to perform.

• Drill To Bottom:  Use this option to drill down to level 0 of the selected member.
• Drill To All:  Use this option to drill down to all descendants of the selected member. 
• Restore Defaults:  Use this option to set all the global settings back to the default settings when installed.
• Excel Mouse:  Use this option to set the mouse functionality to the Excel options.  This disables the right click and double click Hyperion Essbase functionality.
• Hyperion Essbase Mouse:  Use this option to set the mouse functional so the double click and right click functions interact with Hyperion Essbase.
• Lock And Send:  Use this option if you do not want to select lock, followed by selecting send.

To install the add-in, unzip In2Hyperion Essbase Ribbon.zip to any location and follow these steps. 

1. Click on the office button in the top left corner of Microsoft Excel. 
2. Click the Excel Options button located in the bottom right area of the window. 
3. Select the Add-Ins menu.
4. Select Excel Add-Ins in the Manage drop down, and click Go. 
5. Click the browse button, navigate to the location where you unzipped the downloaded file, and click the OK button.

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.