Oracle’s Hyperion Smart View product is great for retrieving data from Essbase, Planning, or HFM applications, but most users have experienced issues with retrieve performance at one point or another. The following is a list of tips and tricks we’ve found from our experience and testing that will help you increase Smart View performance.
1) Retrieval time will be fastest when using a new Excel sheet. Every time you perform a Refresh, Smart View caches the activity (this is why you can press “Undo” so many times). Repeatedly running retrieves off of the same Excel sheet will, therefore, cause performance to slowly diminish over time. If performance becomes noticeably slower after using the same sheet for an extended period of time, open a blank Excel workbook and recreate the query.
2) Select the option to Use Excel Formatting. Without this option, Smart View will reformat each cell based on the grid operations you perform. It will also mark cells as “dirty” each time you change data values. These activities add system overhead to all Refreshes, making them take longer.
3) The most efficient performance will result from small, focused queries on a specific subset of data. Zooming in on extremely large dimensions will cause slow performance. If you absolutely must zoom in on larger dimensions, suppressing rows that have no data or zero data will help.
4) Keeping the default options for displaying No Data, No Access, and Invalid cells will decrease the time needed for a retrieve. The difference in retrieval timing is much more noticeable when using very large (10,000+ rows) queries. Our top scientists are still trying to figure out exactly why this makes retrieves go faster, but it works.
5) The bigger your Excel workbook, the slower your Smart View queries will run. This is why running a query in an Excel workbook that has many other worksheets can take a long, long time.
Retrieval performance will be optimized if the Smart View query is the only tab in the workbook. If other worksheets are being used, the overall size of the file itself will have the biggest impact on performance. Queries used in larger files (over 10 MB) are very likely to run slowly. Obviously this isn’t always feasible to do, but it is helpful to know when you’re having performance issues.
6) Turn off the preserve formulas option when retrieving against an ASO Essbase cube. This has been known to make retrieves take much longer than normal. Interestingly enough, it also can dramatically increase the size of the Excel file itself when this setting is turned on. It does not seem to do this against a BSO or Planning cube.
7) The Excel AutoRecover option can create performance issues or even cause the query to crash. For example, if the AutoRecover option is set to every 3 minutes, a query that takes longer than 3 minutes to retrieve will cause Excel to crash. Disable this option if you are using large queries.
8) On worksheets containing large retrieves, clearing a large number of cells can cause Excel to become unresponsive. This is due to the fact that Smart View tracks each individual cell that is changed in order to improve retrieval time for smaller queries. This is much more noticeable in larger queries with many rows and columns. If all the data displayed on the grid is highlighted and deleted, Excel may become unresponsive until this activity is complete.
With very large queries, Excel may stop working and need to be forcibly closed (see below for everyone’s favorite Excel message).
9) Admittedly, this one is less performance related, but is still useful. If you have already increased your NetRetryCount and NetRetryInterval settings, but are still receiving this message, it may be due to Internet Explorer timeout settings:
Warning: Never modify any registry settings unless you know what you are doing or have the assistance of a trained professional. eCapital Advisors cannot be held responsible for any unintended consequences this may have, including but not limited to crashed servers, unresponsive laptops and subsequent yelling matches with your supervisor.
By default, Smart View will use the Internet Explorer timeout settings. Microsoft gives an overview of how to change these settings right here.
Free eBook Download
In the eBook “Having a Conversation with Data”, learn what the current BI infrastructure has been and associated challenges with the traditional approach. How important the user experience is in order to best maximize data’s value (think visualizations!!) to your organization and how to gain a competitive advantage with modern analytics platforms.
