A couple of weeks ago, I was preparing to speak on the new Smart View 11.1.2.1.102 at the ODTUG Hyperion SIG Dallas meeting and found a performance issue when trying to zoom in on large datasets. Here is the sheet I was zooming:
I used Sample Basic and zoomed in to all levels on Year, Measures, and Product. When I zoomed in on Market, Smart View never returned but rather just kept trying to zoom in for a while. After working on it for a bit, I decided to try the same thing in the classic Excel add-in and, guess what? The same sequence of operations is not even possible there as it easily shoots past the 65,535 rows supported by the classic add-in. I tried it in Dodeca and it returned a bit over 172,000 rows in 5 seconds, so it certainly isn't an issue with the Essbase server and large retrieves.
I next talked to one of my friends inside Oracle and found the source of the performance issue. If you have used the classic Excel add-in, you may remember seeing the
Use Styles option which formats the cells returned by Essbase based on member cell or data cell attributes. Based on my VBA experience, I can tell you that doing lots of format operations in an Excel worksheet can be quite slow. In Smart View 11.1.2.1.102, there is a new option called
Use Excel Formatting which also defaults to false. This setting is conceptually the
opposite of
Use Styles in the classic add-in. In other words, Smart View 11.1.2.1.102, along with all previous versions of Smart View, automatically formats the cells that are returned. In fact, you can see the formatting in the screenshot above.
To prevent this automatic formatting and speed up your retrievals, set the
Use Excel Formatting option to true.
Once I made this change, the zoom in operation on Market did complete and brought back more than 172,000 rows.
Another thing you may note from the spreadsheet is that member labels are repeated. This is another difference between the classic add-in and Smart View. The
Repeat Member Labels option has been renamed
Suppress Repeated Members and the default behavior has changed. With Smart View, you must select the option to
Suppress Repeated Members to see the same behavior you saw by default in the classic add-in. Frankly, I don't remember if this is something new in Smart View 11.1.2.1.102, but just be aware of it. In the screenshot below, I have set the
Suppress Repeated Members setting to mimic the classic add-in.