Knowledgebase: Enterprise
Slow Retrieve Performance Using Microsoft Excel 2007 and Enterprise 6.5.0 or 6.5.1.
Posted by Priya Doyle on 16 February 2011 11:40 AM

Slow Retrieve Performance Using Microsoft Excel 2007 and Enterprise 6.5.0 or 6.5.1.

 Modified 10-JAN-2011     Type PROBLEM     Status PUBLISHED 

Applies to:

Hyperion Enterprise - Version: 6.5.0.0.00 and later   [Release: 6.5 and later ]
Information in this document applies to any platform.

Symptoms

When using Microsoft Excel Version 2007 (with or without service packs) The following symptoms are seen when using HP Retrieve (RHXLL32.XLL)

Slow performance calculating HPVAL formulas ( RHXLL | REFRESH).

Performance is lower than with Excel 2003.

Retrieve workbooks take a long time to open when compared to Microsoft Excel 2003

Changes

Oracle Hyperion Enterprise  has been updated to version 6.5.x
Microsoft Excel has been updated to version 2007 (with or without service packs)

Cause

To enable backward compatibility the addins for Excel use the Excel SDK version 5 and are hence not optimized for Excel 2007 Multi-Threading is in place. Multi-Threading can have an adverse effect on performance





Solution


Four areas of configuration to consider to optimize performance.

Saving  the spreadsheet as an XLSX extension file to avoid Excel converting the spreadsheet each it is opened.

Disable Multi-Threaded Calculation in the Excel Spreadsheet.

Select the Office 2007 Symbol at the top left of the Excel screen whilst the spreadsheets is open
Select the "Excel Option" button
Select Advanced
Deselect...Enable-Multi-Threaded Calculations.
Save the workbook.

Turn Off Auto Calculation.

Select the Office 2007 Symbol at the top left of the Excel screen whilst the spreadsheets is open
Select the "Excel Option" button
Click Formulas
Set the Work Book Calculation Radio Button to Manual
Save the workbook

Tune the HACCESSDATATABLECACHECOUNT parameter in Hypent.ini default section.

Use the HACCESSDATATABLECACHECOUNT option in the hypent.ini to specify the number of Hyperion Enterprise data tables that are cached by Hyperion Enterprise.

A data file contains all data for the combination of a category, entity, and consolidation detail. For best performance, you should set this number to be equal to or greater than the number of data files used in an Enterprise Retrieve spreadsheet. However, if you use a large number, it may increase your RAM requirements.

If you do not enter this setting, the system uses a default value of 20.

Example:
If data is retrieved from three categories for 20 entities, the setting would be HACCESSDATATABLECACHECOUNT=60

The maximum setting for this option is 100.

This setting has no effect on any other add-on modules performance. It only needs to be in the hypent.ini file of users who access large spreadsheets.

Save the Hypent.ini file and close and restart Microsoft Excel/Retrieve.

With these settings in place an improvement in performance will be seen.














References

NOTE:1181603.1 - Tuning Hyperion Enterprise to Improve Performance
NOTE:585549.1 - Using the HACCESSDATATABLECACHECOUNT Option in the Hypent.ini File to Improve Retrieve Spreadsheet Performance
NOTE:593517.1 - Slow Performance When Using Refresh All Linked Cells In Retrieve
NOTE:591404.1 - Refreshing a Large Spreadsheet in Hyperion Retrieve is Very Slow


(0 vote(s))
Helpful
Not helpful

Comments (0)
Post a new comment
 
 
Full Name:
Email:
Comments: