Is Smartview drill exponentially slow vs. classic addin?
June 14 2012 at 10:40 PM No score for this post
from IP address 50.80.23.209
I have an ASO cube that has 11 dimensions. I have an excel grid with 36 columns that I am trying to drill to the bottom of four dimensions with suppression on is Smart View (users want to be able to find all new members added to the dimensions since last month).
Using the classic add-in it is extremely fast (.71 seconds, 3.61 seconds, 11.27 seconds, 35.65 seconds).
Using smartview the first drill is ok at about 7 seconds but it gets exponentially slower from there. The second drill takes a few minutes and the third churns for about 10 minutes and then the aps weblogic app runs out of the 6 Gig of memory that has been allocated to it (11.1.2.1.102 64-bit OS) and crashes.
So am I doing something wrong or is Smartview just that slow? I look in the Essbase app log and the retrieve completes in a matter of seconds but then it takes minutes to render in excel via aps (and then crashes).
Are the only options for this to either use the classic add-in or go to some other product like Dodeca?
Scoring disabled. You must be logged in to score posts.
I will preface all of this with I am a big fan of Dodeca. But everyone who reads this board/knows me probably knew that already. :)
With that semi-disclosure out of the way, I'm working on a Dodeca application right now and the speed is kind of ridiculous in a nice way.
I've played with drilldowns in Smart View, the Classic add-in, and Dodeca, and Dodeca is even faster than the add-in (which was pretty much my gold standard for reporting performance).
Interestingly, Smart View blows by the add-in in terms of numbers of rows that can be selected (the add-in still and will, I think, always have a limit of 65,536 rows) but at least on my not-installed-by-someone-who-can-even-spell-infrastructure (me, in other words) serverette, Smart View seems to go into vapor lock when the number of rows gets really big. Dodeca just chugs through and PDQ. FWIW, it looks like it's actually *Excel* that is the bottleneck (retrieve from Essbase is done, APS isn't registering much activity in Task Manager, Excel is chewing up resources) when the retrieve seems to go into Rip Van Winkle mode.
Regards,
Cameron Lackpour
Scoring disabled. You must be logged in to score posts.
I have worked some with Dodeca as well and agree it is awesome! I am trying to talk my client into that...
But, I agree with you on the RVW of excel. I can drill to bottom of two dimensions successfully albiet slowly but the third I get the "Smartview concurrent requests" error and when I check weblogic it has eaten through all 6GB allocated to the APS JVM and then excel goes into lala land.
Scoring disabled. You must be logged in to score posts.
so I might imagine migrating spreadsheet add-in toward Smart View in about 10 years, if only stabilized by that time...
We have around 200 users, half of them are essbase addicts, this mainly because of the add-in simplicity & stability, it would then pure suicide to replace it for something unstable.
Scoring disabled. You must be logged in to score posts.
Please log an SR on this so we can actually diagnose this issue. I have seen many times that this can be simply related to options set on the SV client or SV client version. The product is absolutely stable and has many features that make it much better than the classic add-in but with out details and proper diagnosis it is hard to make a judgment call.
If you log an SR send me the SR number and I will make sure it gets the attention it deserves.
Thanks,
Matt
Scoring disabled. You must be logged in to score posts.
We've been using Dodeca for the last 5 years. We're very happy with the product. End users to Execs, use it with ease. Our global reporting packages all use Dodeca. Dodeca is cost effective, simple to use, and has a comprehensive scripting layer for customization. The support is second to none. Tim and his team are always reaching for new heights of perfection for the product. Users really love how it handles hybrid analysis.
It really should be the gold standard in any Essbase shop..
Scoring disabled. You must be logged in to score posts.
Interestingly enough the creator of Tim Tow has on his blog, a reason for potentially slow Smart View performance and how to resolve it. It has to do with making sure Use excel formatting is set. For complete info look at http://timtows-hyperion-blog.blogspot.com/2012/03/smart-view-11121102-performance-tip.html
Scoring disabled. You must be logged in to score posts.
There are a couple of settings that can affect performance in the new Smart View. First, the 'Use Excel Formatting' option must be set to True (or checked) for the best performance. This option is similar in functionality to 'Use Styles' in the classic add-in *except* it applies styles by default. You can easily find yourself waiting a *significantly* longer time if you don't turn 'turn off' this auto formatting by selecting to 'Use Excel Formatting'.
Second, the 'Repeat Member Labels' setting from the Excel add-in has been changed to a 'Suppress Repeated Members'. This option, which is False (or unchecked) by default, causes the all member cells in *row orientation* to be filled with member names. This setting is opposite the behavior of the classic add-in and will cause the xml to be much larger if you have a large retrieve.
All of this being said, Rob is also seeing an issue with memory on the APS server and I don't believe either of these settings should cause a significant performance or memory issue on the server side; I would expect to see this issue on the client only. The only thing I could think of that may cause an issue from the client perspective may be the Microsoft XML version that is installed on the client. In other words, I would certainly file an SR on this.
Tim Tow
Oracle ACE Director
Applied OLAP, Inc
Scoring disabled. You must be logged in to score posts.
There is one more thing I thought of and is in the Essbase Labs blog. By default data is sent uncompressed between the server and client, In the essbase.properties file is a setting to turn on compression speeding pup retrievals
Scoring disabled. You must be logged in to score posts.
Thanks for jogging my brain. After working the SR and reading the blog posts on this topic for the umpteenth time I realized I wasn't suppressing repeat members. That resolved my issue.
It is still not quite as fast as the classic add-in but it is pretty close (I'll have to run some benchmarks to know the full impact) and given the better feature set of SmartView it more than makes up for the slight bit of a performance hit.
Here is an interesting side note though...
I didn't realize repeat members weren't suppressed because given the nature of the data drills that I am doing each row returned is unique. So even though the suppressing of repeat members didn't make a difference in the resulting excel grid it must alter the way the xml is built inside of provider services.
Scoring disabled. You must be logged in to score posts.
I would not have suspected that the repeat member suppression would have made that much difference unless it contributed further to the formatting due to the application of styles. I would be interested to know how long the same retrieve performs in the classic add-in with the repeat labels turned on.
Also, this is going to be a huge and recurring training issue for Smart View users. If I were Oracle, I would set these two settings, 'Use Excel Formatting' and 'Suppress Repeated Member Labels', to True *by default* to minimize the impact on users switching from the classic add-in.
Tim Tow
Oracle ACE Director
Applied OLAP, Inc
Scoring disabled. You must be logged in to score posts.
Another challenge is how the options change per connection type, especially around Ancestor Position (great functionality) only working for HFM, and Formatting behaves differently by connection type.
We're also seeing interesting things around setting options with VBA affecting other options that it shouldn't, and/or (still researching this one) user default options overriding the VBA set options causing havoc with app templates.
All that said, the 103 release of SV (and 11.1.2.2) is fantastic, I'm very impressed.
Scoring disabled. You must be logged in to score posts.
I agree also on the defaults of the settings - it is one thing from a "user experience" argument (can make a case either way) but, from a performance view it is a necessity in my opinion tha the defaults are to turn on repeat member suppression and use excel formatting. From this experience we built a new 64bit server to get past the 2 gig of memory limitation that was crashing APS to lead us to the "real" problem. If the defaults were set to the most efficient way then when it stops working after you change something you would know what caused it.
So I ran a quick test with repeated members turned on in the classid add-id. I performed the same retrieves (4 zoom to bottoms) and the results were as follows:
Here is the data from the EAS logs (note the rendering in excel was basically instantaneously completed after the spreadsheet extractor elasped time was written to the logs).
Zoom on dimension 1: .34 seconds
Zoom on dimension 2: 2.90 seconds
Zoom on dimension 3: 8.50 seconds
Zoom on dimension 4: 35.01 seconds
Interestingly enough if I turn off the repeating of members the zoom operations complete in almost the same time. Here are those:
Zoom on dimension 1: .21 seconds
Zoom on dimension 2: 3.33 seconds
Zoom on dimension 3: 9.42 seconds
Zoom on dimension 4: 30.40 seconds
Here is the same operations using Smart View with repeat members suppressed. I would say from these test you can call Smart View performance on par with the classic add-in (as long as you don't repeat members).
Zoom on dimension 1: .17 seconds
Zoom on dimension 2: 3.03 seconds
Zoom on dimension 3: 10.29 seconds
Zoom on dimension 4: 33.85 seconds
Scoring disabled. You must be logged in to score posts.
I was wrong...it was GZIP Compression that fixed it.
No score for this post
June 21 2012, 4:39 PM
So in putting together my "Zoom-Off" analysis I found that the having repeated members on or off made very little difference in performance but the reason Excel was bombing was not having the gzip option turned on. Tim, this probably makes more sense to what you were thinking should be happening - it does now to me that I understand it.
That is what I get for changing more than one thing at a time. I changed the setting in Smart View to suppress repeat members at the same time I added the setting to turn on gzip compression.
Smart View is now successfully retrieving over 2 million rows. It does take some time to process that large of a retrieve but it does correctly return and render the data.
So to sroux,
I'd say that smartview is stable and I would recommend moving your users to it. Even before this issue was resolved our users loved the improvements/features in 11.1.2.1.103. I've been using the classic add-in for over 4 years now and would have to say that I like Smart View better now (and I was a classic junkie).
And Oracle was great to work with on the support issue. I think Matt was spot on when he said you just need to configure it correctly and understand it because it does have more moving parts than the classic add-in.
Let me know if you have questions...I'd be happy to go through all the "settings learning curve" we've gone through over the past few months getting everything to work well.
Scoring disabled. You must be logged in to score posts.
Re: I was wrong...it was GZIP Compression that fixed it.
No score for this post
June 21 2012, 10:32 PM
No I won't be in San Antonio. I was at New Orleans, and DC and I am very sad that my new employer doesn't believe much in conferences. I hope to make it back to Kaliedescope again soon!
Scoring disabled. You must be logged in to score posts.
Re: I was wrong...it was GZIP Compression that fixed it.
No score for this post
June 21 2012, 7:31 PM
Rob,
That definitely makes a lot more sense. A couple of questions.. How are you retrieving 2 million rows when Excel stops at 1,048,576 rows? Are the numbers you posted earlier the numbers for the retrieve that retrieves 2 millions rows (or is it cells?)
Tim
Tim Tow
Oracle ACE Director
Applied OLAP, Inc
Scoring disabled. You must be logged in to score posts.
