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.

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.