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\

"Validation"

In installment #3 of this series we installed and configured the 11.1.x software.  In this installment we will discuss what Infrastructure Architect will do before the environment is turned over to the development or migration teams.

It is quite frustrating to the developers if the environment is not fully functional when they start using the system.  Additionally, it is very frustrating for the installation architect to have users in the environment as debugging of issues is occurring.  Each installation and configuration project plan should include at least a day or two to review an environment, restart it a few times, check out the logs, and then test the functionality of all installed components.  The number of items to validate depends on the products used and licensed by the client but it should start with the following and adjust as necessary.

• Shared Services
• Essbase
• EAS and Business Rules
• Planning
• Financial Reporting
• Web Analysis
• Interactive Reporting
• SQR
• Workspace
• Smart View and Provider Services
• Financial Management
• Financial Data Quality Management
• Oracle Data Integrator
• Data Relationship Management
• Strategic Finance

The Installation Architect will test the use functionality of each of the installed product to ensure there are no errors.  This activity takes a combination of functional and technical ability.  The installation architect must know how the application works from the interface as well as understanding what any potential errors mean and how they may be corrected.  I’m not suggesting the infrastructure engineer know how to create a P&L report or design a Planning application, but the ability to navigate the user interfaces and test functionality eliminates the problems of encountering them after development has begun.

Early in my exposure to these applications, I’d spend a lot of time with a developer or functional user of the applications to show me how to test the system.  I’d ask them to tell me the first thing they try to do when they get a new environment.  It is always useful to know more about how the applications are used. 

Some of the common problems that occur include the following.
EPMA dimension server does not resolve in Workspace
Shared Services doesn’t find users in Active Directory
Cannot create Planning Application
Cannot create FDM Database
ODI repositories are not available
Common Essbase commands do not work

The solutions to some of these problems may range anywhere from Database Access Permissions, Windows Security Rights, DCOM Settings, or incorrect Active Directory Setup.  Over the past few years working on dozens of installation, I’ve seen all of these.  From encountering many of these, the pre-installation requirements covered in installment #2 have been improved.  Some of these problems don’t arise until functionality is tested.  It’s important to test each installation and environment.  I’ve had situations where the development environment will test out fine and the QA Environment will have issues.  Each installation is usually different from each prior installation because of server settings, security policies, database settings, firewalls, or some other nuance.

If there are problems with the functionality there are a number of resources available to assist in troubleshooting.  I find the Oracle Technology Network Forum to be very useful.  I recommend anyone looking to work in this space, get an ID, and get involved.  You may also find some real useful things on blogs like this or a number of other very experienced bloggers.  There is a wealth of information at Oracle Support in the knowledge base.  In addition, if you have a support agreement with Oracle, register here and you can get support from Oracle.

Assuming everything is functioning as expected, the environment is turned over to the appropriate support person, or maybe support falls on the same individual that did the installation.  Either way, there is a lot of information that needs to be collected.  In the next installment, we’ll look at the information that should be compiled to capture the state of the environment as it was at the end of the installation as well as information that is useful to those that will be using the system.

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.

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.