There is, what appears to be, a bug in Hyperion Planning that causes business rules that take longer than 5 minutes to re-launch.  The following, published by Oracle, explains the root issue of this problem.  It is not a bug, but a setting in the host web server that causes the request post multiple times.  This explaination from Oracle clearly states that this is ONLY an issue when accessing Hyperion Planning through Hyperion Workspace.  I have seen the same response while accessing Hyperion Planning directly.  Regardless of your entry point, it is a good proctice account for either entry method and should be applied.

This applies to Hyperion Planning, Version: 9.3.1.0.00 to 11.1.1.3.00 and is applicable to all operating systems.

Symptoms

When accessing Planning, Business Rules that normally take more than 5 minutes to complete
run for an unlimited period of time.  By viewing the running Essbase sessions in the EAS console, you can see that the Business Rules “Calculate” sessions are being re-launched every 5 minutes, so that a new instance of the Rule is launched before the first can complete.

This issue only affects Business Rules that normally take more than 5 minutes to complete.

This issue does not affect Business Rules launched directly from Planning (accessing Planning directly on its own URL, bypassing the Workspace).  This issue does not affect Business Rules launched from the EAS console.  This issue only affects systems using Weblogic as a web application server.

Cause

This issue is caused by a default timeout setting of 5 minutes (300 seconds) in the Weblogic HTTP Server Plugin.  This plugin is a set of configuration files in which Weblogic defines how it will interact with the HTTP Server through which Workspace is accessed.  More information on Weblogic Plugins is available here:  http://download.oracle.com/docs/cd/E13222_01/wls/docs92/pdf/plugins.pdf

Solution

Hyperion System 9 and Oracle EPM 11.1.1.x support the use of either Microsoft Internet Information Services (IIS) or Apache as an HTTP server. The steps to increase the timeout depend on which you are using.  The new timeout value should be set to a value larger than the time the longest-running Business Rule takes to execute. The examples below use a setting of 30 minutes (1800 seconds).

Apache HTTP Server

Step 1

Edit %HYPERION_HOME%\common\httpServers\Apache\2.0.52\conf\HYSL-WebLogic.conf

Step 2

Add (or edit, if already present) the following parameters to the two sections for Planning, and also to the two sections for Financial Reporting and Workspace, as the 5 minute timeout issue can cause problems in all three products.Each section begins with an XML tag.

WLIOTimeoutSecs 1800
HungServerRecoverSecs 1800
  <LocationMatch /HyperionPlanning>
<LocationMatch /HyperionPlanning/*>

Add the new “WLIOTimeoutSecs 1800” and “HungServerRecoverSecs 1800” properties as new lines within the tags.  If you are using a version of Weblogic prior to 9.x you need to add the second line “HungServerRecoverSecs 1800” in addition to the “WLIOTimeoutSecs 1800” parameter. This second parameter is not necessary for Weblogic 9.x and later (though it will do no harm).

PathTrim /
KeepAliveEnabled ON
KeepAliveSecs 20
WLIOTimeoutSecs 1800
HungServerRecoverSecs 1800

Internet Information Services (IIS)

Step 1

There are several copies of the iisproxy.ini file. Oracle recommends you modify the files for Planning, Financial Reporting and Workspace, as the 5 minute timeout issue can cause problems in all three products.

Paths (note that “hr” below stands for Financial Reporting):

%HYPERION_HOME%\deployments\WebLogic9\VirtualHost\hr
%HYPERION_HOME%\deployments\WebLogic9\VirtualHost\HyperionPlanning
%HYPERION_HOME%\deployments\WebLogic9\VirtualHost\workspace

Step 2

For each copy of iisproxy.ini, add the following lines at the end of each file.  If you are using a version of Weblogic prior to 9.x you need to add the second line “HungServerRecoverSecs=1800” in addition to the “WLIOTimeoutSecs=1800” parameter. This second parameter is not necessary for Weblogic 9.x and later (though it will do no harm).

WLIOTimeoutSecs=1800
HungServerRecoverSecs=1800

Step 3

Restart IIS from the IIS Manager and restart the Workspace web application service

Oracle HTTP Server is used

Step 1

Modify the file mod_wl_ohs.conf file under the directory, $EPM_ORACLE_INSTANCE\httpConfig\ohs\config\OHS\ohs_component with the following content:

<LocationMatch ^/HyperionPlanning/>
SetHandler weblogic-handler
WeblogicCluster PlaningServer:8300
WLIOTimeoutSecs -1
WLSocketTimeoutSecs 600
</LocationMatch>

Step 2

Restart the Oracle HTTP server and the Workspace web application services after the modifications are complete.

Changes to an Essbase outline cause changes to the Essbase index and data files, regardless of the method (Essbase Administration Services, Hyperion Planning database refreshes, or from a script).

Changes that require restructuring the database are time-consuming (unless data is discarded before restructuring).  Understanding the types of restructures and what causes them can help database owners more effectively manage the impacts to users.

TYPES OF RESTRUCTURES

Essbase initiates an implicit restructure after an outline is changed, whether done with the outline editor, through an automated build, or some other fashion like a Hyperion Planning database refresh.  The type of restructure that is performed depends on the type of changes made to the outline.

DENSE RESTRUCTURE:  If a member of a dense dimension is moved, deleted, or added, Essbase restructures the blocks in the data files and creates new data files. When Essbase restructures the data blocks, it regenerates the index automatically so that index entries point to the new data blocks. Empty blocks are not removed. Essbase marks all restructured blocks as dirty, so after a dense restructure you must recalculate the database. Dense restructuring, the most time-consuming of the restructures, can take a long time to complete for large databases.

SPARSE RESTRUCTURE:  If a member of a sparse dimension is moved, deleted, or added, Essbase restructures the index and creates new index files. Restructuring the index is relatively fast; the time required depends on the index size.

Sparse restructures are typically fast, but depend on the size of the index file(s).  Sparse restructures are faster than dense restructures.

OUTLINE ONLY:  If a change affects only the database outline, Essbase does not restructure the index or data files. Member name changes, creation of aliases, and dynamic calculation formula changes are examples of changes that affect only the database outline.

Outline restructures are very quick and typically take seconds.

Explicit restructures occur when a user requests a restructure to occur.  This can be done in Essbase Administration Services or via Maxl (and EssCmd for those of you who still use it) and forces a full restructure (see dense restructure above).  It is worth noting that this also removes empty blocks.

CALCULATING IMPLICATIONS AFTER RESTRUCTURES

When a restructure occurs, every block that is impacted is tagged as dirty.  If Intelligent Calculations are used in the environment, they don’t provide any value when a dense restructure occurs as all blocks will be calculated.  When member names or formulas are changed, the block is not tagged as dirty.

WHAT DICTATES THE RESTRUCTURE TYPE

The following outline changes will force a dense restructure, which is the most time- consuming restructure.

DENSE AND SPARSE

• Defining a regular dense dimension member as dynamic calc
• Defining a sparse dimension regular member as dynamic calc or dynamic calc and store
• Defining a dense dimension dynamic calc member as regular member
• Adding, deleting, or moving dense dimension dynamic calc and store members
• Changing dense-sparse properties [Calc Required]
• Changing a label only property [Calc Required]
• Changing a shared member property [Calc Required]
• Changing the order of dimensions [Calc Required]

DENSE (DATA FILES)

• Deleting members from a dense dimension  [Calc Required]
• Adding members to a dense dimension
• Defining a dense dynamic calc member as dynamic calc and store member

SPARSE (INDEX)

• Adding members to a sparse dimension
• Moving members (excluding shared members) in a sparse dimension
• Defining a dense dynamic calc member as dynamic calc and store
• Adding, deleting, or moving a sparse dimension dynamic calc member
• Adding, deleting, or moving a sparse dimension dynamic calc and store member
• Adding, deleting, or moving a dense dimension dynamic calc member
• Changing the order of two sparse dimensions

NO RESTRUCTURE OCCURS

• Deleting members of a sparse dimension [Calc Required]
• Deleting members of an attribute dimension
• Deleting shared members from a sparse or dense dimension [Calc Required]
• Adding members to an attribute dimension
• Adding shared members to a sparse or dense dimension
• Moving a member in an attribute dimension
• Renaming a member
• Changing a member formula [Calc Required]
• Defining a sparse dynamic calc member as dynamic calc and store member
• Defining a dense or sparse dynamic calc and store member as dynamic calc
• Defining a regular dense dimension member as dynamic calc and store
• Defining a sparse dimension dynamic calc and store member or dynamic calc member as regular member
• Defining a dense dimension dynamic calc and store member as regular member
• Changing properties other than dense-sparse, label, or shared [Calc Required]
• Changing the order of an attribute dimension
• Creating, deleting, clearing, renaming, or coping an alias table
• Importing an alias table
• Setting a member alias
• Changing the case-sensitive setting
• Naming a level or generation
• Creating, changing, or deleting a UDA

WHAT DOES THIS MEAN

Understanding this can help users and administrators manage applications to better meet the needs of all those involved.  When designing an application, knowledge of this topic can be instrumental in the success of the application.  Here are some things to keep in mind.

• When updating an outline or refreshing a planning application, it may be faster to export level 0 (or input level) data, clear the data, perform the update, and reload/aggregate the export when  changes cause a dense restructure.
• For dimensions that are updated frequently, it may be beneficial to define those dimensions as sparse.  Changes to sparse dimensions typically require only restructures to the index file(s), which are much faster.
• If frequent changes are required, enabling incremental restructuring may make sense.  Using this defers dense restructures.  The Essbase restructure happens on a block by block basis, and occurs the first time the data block is used.  The cost is that calculations will cause restructures for all the blocks included and the calculation performance will degrade.
• Setting the isolation level to committed access may increase memory and time requirements for database restructure.  Consider setting the isolation level to uncommitted access before a database restructure.
• If multiple people have access to change the outline, outline logging may be useful.  This can be turned on by adding OUTLINECHANGELOG = TRUE in the essbase.cfg.
• Monitoring progress of a restructure is possible when access to the server is granted.  Both sparse and dense restructures create temporary files that mirror the index and data files.  Data exists in the .pag files while indexes are stored in .ind files.  As the restructure occurs, there are equivalent files for each (pan for data files and inn for index files).  In total, the restructure should decrease the size of the ind and pag files, but the pan and inn files can be used for a general idea of the percent of completion.

The results are back, and our session at Collaborate 2011 was very well received. We would like to thank all of those who attended and provided feedback. The presentation and white paper are available below. Thanks to the folks at Abercrombie & Fitch for their involvement with the presentation.

We met a lot of great people in Orlando. Thanks to all of you for the kind words and thank yous for the In2Hyperion effort.

Downloads

• Driving Accountability Through Disciplined Planning at Abercrombie & Fitch – Session 8454.pdf (106.09 kb)
• 2011_8454_Forrest.ppt (2.94 mb)

Working with finance and accounting professional the majority of my career, I see a lot of spreadsheet “templates” that are reused for multiple budget passes or monthly forecasting processes (any repatative process).  When the workbooks have a number of worksheets, and they are large, it can be extremely tedious to clear out the old data and get back to a fresh, empty shell.  The script below can be executed on any worksheet to clear out all the numeric values and cell comments.  It ignores cells with dates, formulas, or text values.

    'loop through each cell in the range of cells used in the worksheet
    For Each c In ActiveSheet.UsedRange.Cells
        'If the cell value is null, don't do anything
        If Not IsNull(c.Value) Then
            'Do not execute on formulas or non numeric values
            If Not c.HasFormula And IsNumeric(c.Value) Then
                'If a cell comment exists and it is not equal
                'to "KEEP", set the value of the cell to null
                If Not (c.Comment Is Nothing) Then
                    If c.Comment.Text <> "KEEP" Then c.Value = Null
                ElseIf c.Comment Is Nothing Then
                    c.Value = Null
                End If
            End If
        End If
        'Execute on all cells in the range where the cell has a comment
        If Not (c.Comment Is Nothing) Then
            'If the comment is equal to "KEEP", don't delete the comment
            If c.Comment.Text <> "KEEP" Then c.Comment.Delete
        End If
    Next c
    MsgBox "Complete"

Breaking It Down

The outside loop will loop through each cell using the ActiveSheet.UsedRange.Cells.  This function will get the range of cells on the worksheet that has been used.  UsedRange will take the equivalent range of using CTRL-HOME to get the upper left cell and CTRL-END to get the bottom right of the range.

For Each c In ActiveSheet.UsedRange.Cells
…
Next c

Each cell will be checked to verify that the value is not blank, is not a formula, and is numeric (not text).  If this criteria is true, the value will be set to nothing.

If Not IsNull(c.Value) And Not c.HasFormula And IsNumeric(c.Value) Then
c.Value = Null
End If

If the cell has a cell comment, it will be removed as well.

If Not (c.Comment Is Nothing) Then
c.Comment.Delete
End If

In the full example, some additional lines are added to ignore clearing any cell with a cell comment of “KEEP”.

How To Use

To use this script, it must be added to a module.  The easiest way to do this is to create a macro and associate a CTRL-? key to it.

In Excel 2007, select the Developer ribbon and click the Record Macro button.  Immediately click the Stop Recording button.  This will create a function in a new module for you.  If the Developer tab is not visible, click the Office Button and click the Excel Options button.  On the Popular tab, select Show Developer Ribbon.

In Excel 2003, select the Tools / Macro / Record New Macro menu.  Immediately click the Stop Recording button.

After opening Visual Basic in Excel, expand the spreadsheet in the Project window.  Expand the Modules tree and open the module.  Inside the module will be a procedure that is empty.  Paste the script inside the procedure.  This can now be accessed by the CTRL-? that was assigned.

I will be posting more scripts like this.  If you find this helpful, add your email to our mailing list near the top of the right sidebar.  You will get an email any time we add a new article!

Audit logs, or SSAUDIT, are a crucial component of backing up Hyperion Essbase applications in many environments.  It is the equivalent of a transaction log in a relational database.  To use this effectively, the audit log has to consistently log database changes.

If the audit feature in Hyperion Essbase is used, the following information is absolutely critical to know to effectively manage this feature.  If the application is on a shared environment where multiple groups/people are administering the applications, it is critical that everybody understands this, and plays nicely together!

The audit logs are turned off without any notification when the following actions occur on an Essbase server.  To turn the audit feature back on, the Essbase application in question has to be stopped and started.  It is not required to cycle the Essbase service.

• Any operation that causes a database restructure.
• The creation of a new application
• The creation a new database
• Copying a database
• Renaming a database

After any of these operations occurs on the server, stop and start all applications that use the audit feature.

Many clients have issues with Workspace logging users out when attempting to open/export certain types of documents. For example, a user logged into Hyperion Workspace attempts to open a Supporting Detail document, only to be prompted with a logout warning message before the document opens. The same can happen when a user attempts to export an FR report file (.DES file) from Workspace. After being logged out of Workspace, the user is able to log back into the application and open/export the necessary document. This issue can easily fixed by adjusting a few browser settings as shown below.

NOTE: Microsoft documents typically cause this logout issue (Excel, Word, PowerPoint).

1. Open your web browser (This blog entry will assume IE).

2. Select “Tools” -> “Internet Options…” from the menu bar.

3. Select the “Internet” option inside the “Security” tab. Select “Custom Level…”.

4. Navigate to the “Downloads” section and “Enable” Automatic prompting for file downloads. Click “OK”.

5. Now select the “Local Internet” option inside the “Security” tab. Select “Custom Level…”.

6. Navigate to the “Downloads” section and “Enable” Automatic prompting for file downloads. Click “OK”.

7. Finally, select the “Trusted sites” option inside the “Security” tab. Select “Custom Level…”.

8. Navigate to the “Downloads” section and “Enable” Automatic prompting for file downloads. Click “OK”.

9. Now that the settings have been set properly, Click “OK” on the “Internet Options” dialog box.

10. Close and re-open your web browser for the settings to take effect. Test these settings by logging into Workspace and opening a Supporting Detail document.

Often times with a Hyperion Essbase or Planning application, an allocation of data will be required.  Many times, the allocation is simply moving data from one member to another.  When the number of members involved is large, developing the script can be time consuming.  When the members frequently change, the maintenance of the calculation can be a nuisance.

When the members involved in the allocation are similar on both sides (the from and the to), the following method can be employed to speed the development and limit, or eliminate, any maintenance required.

Requirement

The application has 50 members in which the data needs to be moved.  The data originates from an account coming from the general ledger.  The data needs to be moved to a new member that doesn’t exist in the chart of accounts.  The new member will exist in a different part of the hierarchy.

Solution

The first step is to create a corresponding member for each of the 50 accounts that need allocated.  These accounts will be identical to the original 50, except they will be prefixed with a “D” identifying them as a dummy, or made up, account.  Each of these new accounts will have a UDA of “allocation.”  The prefix of the member and the UDA are not critical.  They will likely be something more meaningful to the requirements.

Once the hierarchy is ready to handle the allocation, the following function can be used.  In layman’s terms, this only executes on the new members added (identified by the unique UDA) and makes them equal to the corresponding member without the added prefix.  We will assume that this is being executed on a scenario that equals “Actuals.”

FIX(@UDA(“allocation”))
/* Make the new member equal to the old member */
“Actuals” = @MEMBER ( @SUBSTRING ( @NAME(@CURRMBR(“Account”)) , 1));
/* Clear the old member */
@MEMBER ( @SUBSTRING ( @NAME(@CURRMBR(“Account”)) , 1)) = #Missing;
ENDFIX

Let’s assume that the UDA is NOT added to the new, or dummy, member.  If the UDA is on the originating member, the calculation would look like this.

FIX(@UDA(“allocation”))
/* Make the new member equal to the old member */
@MEMBER ( @CONCATENATE(“D”,@NAME(@CURRMBR(“Account”)))) = “Actuals”;
/* Clear the old member */
Actuals = #Missing;
ENDFIX

Now we can break down these functions. Remember, the calculations loop through all members in all dimensions.  In this example, setting the result equal to “Actuals” is simply making the account that the calculation is looking at, at that particular point in the loop, equal to whatever is on the other side of the equation.

@MEMBER ( @SUBSTRING ( @NAME(@CURRMBR(“Account”)) , 1))
There are four functions used in this string.

• @MEMBER will convert a string to a member name
• @SUBSTRING requires 2 parameters (3 optional).  The first is the larger string from which you want to take a smaller string.  The second is where to start, with 0 being the first character.  The third is how many characters to include.  If this is left blank, it will take all the characters to the right of the second parameter.
• @NAME will convert a member to a string.
• @CURRMBR gets the current member of a specified dimension.

Putting this all together, this calculation (from inside out) is getting the current member of the Accounts dimension (“d345678”).  It converts that member to a string.  It takes all the characters to the right of the first character (“345678”).  Then it converts the string back to a member.  At this point, we can set that member equal to something.

@MEMBER ( @CONCATENATE(“D”,@NAME(@CURRMBR(“Account”))))
The functions here are the same as above, except we are not removing the “d.”  We are adding it.

• @CONCATENATE accepts two parameters and will combine those two in to one string

Putting this all together, this calculation (from inside out) is concatenating two strings, a “D” and the current member of the Accounts dimension (“d345678”).  It then converts the string to a member. At this point, we can set that member equal to something.

Benefits

By using these functions, the calculations can be much smaller, quicker to develop, and completely maintained by the outline.  This effectively gives the user community ownership on the maintenance.

Microsoft Excel does a great job of calculating only what is needed.  If automatic calculation is turned on (and is by default), it only calculates formulas that have changed since the last calculations.  If automatic calculation is turned off, F9 will accomplish the same thing.

CTRL F9 goes one step further and calculates formulas that have changed, as well as the formulas dependent on them.

CTRL ALT F9 calculates all formulas in the workbook, regardless of whether they changed since the last calculation.

When custom functions are used, Microsoft Excel doesn’t always know the dependencies because the function can reference cells outside those provided in the function arguments.   When this occurs, using CTRL SHIFT ALT F9 is critical to ensure that all cells are calculated correctly.  This rechecks dependent cells and calculates all formulas regardless of whether they have changed since the last calculation.  This is the only way to ensure that all data is calculated.

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\