The HsGetVal function has been improved significantly in Smart View over the classic add-in EssCell function. The difference in performance is attributed to Smart View HsGetVal using a parallel batch process approach to retrieving the data versus the single cell, serial approach of EssCell.
Here are some statistics for refreshing an Excel 2007 worksheet containing 7,225 cell functions:
Classic Essbase Add-In (EssCell) = 724 seconds to complete
Smart View 11.1.2 (HsGetVal using Function Builder) = 20 seconds to complete
Smart View 11.1.2 (HsGetVal using Copy DataPoints) = 7 seconds to complete
For those wondering, the "Copy DataPoints" based HsGetVal function achieves better performance than the regular HsGetVal function by using a special POV link:
HsGetValue("", "Measures#Profit %", "Market#New York", "Year#Jan", "SVLink12605407100EOL", "Row1", "Col15")
This breakthrough in cell function performance in Smart View should be welcome news to the thousands of classic Essbase add-in users who love the control of querying data using cell functions but were not happy with the performance.
6 comments:
Sounds great indeed - now that I'm [finally] getting into Smart View I'm waiting for the real 11.1.2 release - I mean the one that is backward compatible with Essbase 11.1.1.3 - that should be 11.1.2.1 and it should be backward compatible isn't it?
11.1.2 is backward compatible for HFM and Planning.
For Essbase, you need APS 11.1.2 which is backward compatible.
So can this function be used to combine totals for multiple members? I have been looking for whatever features are available in SmartView to allow users to create reports with customized groupings. In the old days - we would have made a million alternate rollups in the cubes... but seems most front-ends cover this now. I ma just having difficulty seeing what SmartView is using to cover this need. Any thoughts are greatly appreciated.
Hi there,
Great blog you have! I have added you to my links. Would you mind adding a link to my new blog at hyperion.turkblogger.com ?
Thanks.
Matt,
Does SV/APS v9.3.1.2 also use the parallel batch process approach? Will upgrading SV/HPS to an 11 flavor belefit us?
We have a lot of HSGETVALUE functions floating about, and they place a lot of stress on the Essbase server compared to a SV grid refresh or AddIn retrieval.
Would you recommend 11.1.2, or an older version?
Thanks!!
The newer the version and you will be better off with functions but keep in mind there may be other upgrade implications you may want to consider also.
Performance will also be impacted based on how the formula was created. For example for a large group of hsgetvalue functions using copy/paste data points will be more efficient than just typing the formula. This will create a single internal query for all the formula and use that when refreshing.
Post a Comment