We've just upgraded our 2 main servers to 9.4.1 FP1 (32 bit server on 64 bit windows 2003). As we haven't been able to update all the clients in 1 go we are still in a mixed state of 8.4.5/Excel 2003 and 9.4.1 FP1/Excel 2007.
Most of our users are on terminal servers which are located next to the actual server itself, so performance from them is very good. However in the past week or so we have noticed serious problems while trying to access certain cubes with the 9.4.1/2007 combination when it is over the WAN.
We've tried several combinations to see what this might be, seeing how long it took to change a view (to a previously cached one)
9.4.1/Excel 2007 over WAN - 3 minutes 30 seconds
9.4.1/Excel 2007 over LAN - 2 seconds
8.4.5/Excel 2003 over WAN - 5 seconds
9.4.1/Architect on Server - 1 second
We looked at all the network traffic, but as far as we could tell this wasn't the problem, especially as the two different clients over the wan had such contrasting performance.
So it seems that it might be something to do with 9.4.1/Excel 2007, so I've searched the forum for some insight.
One thing that did pop out was a comment by lotsaram on a 9.4.1 FP2 thread
Looking at the particular cube/view we were having the most issues with, one of the title dimensions was looking at ALL. As soon as we changed this to looking at just 1 element, then the response time for the 9.4.1/Excel 2007 came down to seconds (as it should be). Even selecting ALL as a title dimension took minutes to confirm, even before F9 was pressed!Re: Issue with the client on 9.4.1 FP2
by lotsaram » Wed Apr 21, 2010 12:24 am
Although the dataset is the same are your views in fact identical? There can be dramatic differences in performance if an (apparently identical) view uses dynamic subsets vs. static or if large title dimensions have a selection of subset ALL rather than a small list.
If however the same ALL dimension was a row, and just a 20 selection title was selected, then it ran in seconds again, despite there being 1000's of items of data compared to a few 100.
Obviously I can go into each default and change the title dimensions so that it won't be a problem on entry to the cube, but what happens the first time a boss switches a dimension with 1000's of elements in it from a row to a title dimension? Them hitting the wall I'd imagine.
Has anyone else had problems like this?
Not sure what to do ongoing, we are still migrating fully to Excel 2007, but then we may go to 9.4.1 FP3 or 9.5, particularly if this is fixed.
Seems to be either something about Excel 2007 or 9.4.1 that the WAN just doesn't like.
Thanks
