| 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<max_ver> 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.xMicrosoft 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 performanceSolution
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 PerformanceNOTE: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
GKontos
Comments