Performance Issue 9.4.1 FP1 / Excel 2007

Post Reply
User avatar
BigDSter
Posts: 55
Joined: Thu May 15, 2008 8:02 am
OLAP Product: TM1
Version: 9.4.1
Excel Version: 2007
Location: Preston

Performance Issue 9.4.1 FP1 / Excel 2007

Post by BigDSter » Wed May 26, 2010 12:19 pm

Hi,

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
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.
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!

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
David Newton
Burtons Foods

User avatar
BigDSter
Posts: 55
Joined: Thu May 15, 2008 8:02 am
OLAP Product: TM1
Version: 9.4.1
Excel Version: 2007
Location: Preston

Re: Performance Issue 9.4.1 FP1 / Excel 2007

Post by BigDSter » Wed May 26, 2010 3:14 pm

Well we've upgraded our test server to 9.5 to see if that made a difference (release notes indicated it may do)

with 9.4.1 FP1 client over WAN - same issue
with 9.5 client over WAN - fixed, cube comes up pretty instantly as we'd expect.

So ... just need to get everyone onto Excel 2007/9.4.1 FP1 then upgrade server to 9.5 and upgrade every client to 9.5 ... doh :(

just as well I don't work in ops for that job :D
David Newton
Burtons Foods

belair22
Posts: 68
Joined: Wed Feb 25, 2009 2:26 am
OLAP Product: TM1, Cognos Express
Version: 9.5 9.4 9.1 9.0 8.4
Excel Version: 2007 2003

Re: Performance Issue 9.4.1 FP1 / Excel 2007

Post by belair22 » Fri May 28, 2010 2:27 am

Have you tried 9.4.1 FP3?

User avatar
BigDSter
Posts: 55
Joined: Thu May 15, 2008 8:02 am
OLAP Product: TM1
Version: 9.4.1
Excel Version: 2007
Location: Preston

Re: Performance Issue 9.4.1 FP1 / Excel 2007

Post by BigDSter » Fri May 28, 2010 8:01 am

Not for this particular issue. We couldn't go with that server as our users on 8.4.5 couldn't see the server, whereas they can with 9.4.1 FP1. Hence we can get a managed roll out rather than a one shot big bang switch.

We'll carry on upgrading to 9.4.1 FP1 then upgrade to 9.5 soon after.
David Newton
Burtons Foods

viswaraju
Regular Participant
Posts: 177
Joined: Thu Nov 05, 2009 8:45 am
OLAP Product: IBM COGNOS TM1
Version: 9.5
Excel Version: 2007

Re: Performance Issue 9.4.1 FP1 / Excel 2007

Post by viswaraju » Mon May 31, 2010 6:13 am

please can you say me, how to migrate from V9.4 to V9.5?

thanking you.

User avatar
Alan Kirk
Site Admin
Posts: 5996
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2 Classic (PAW-free zone)
Excel Version: 2010 and 2016
Location: Sydney, Australia
Contact:

Re: Performance Issue 9.4.1 FP1 / Excel 2007

Post by Alan Kirk » Mon May 31, 2010 6:28 am

viswaraju wrote:please can you say me, how to migrate from V9.4 to V9.5?
This is getting ridiculous.

Nobody minds answering questions for new users who get lost, but this is starting to sound like an unending echo.

So before anyone wastes any more time answering questions like these, give a simple answer, yes or no... have you read the 9.5 installation guide? No dodging the question with "my apologies", "thanking you", "I got it Steve", "forgive me"... just answer "yes", or "no", to the question have you read that document?

If "yes", which part or parts of it are unclear to you?

And if "no", why on Earth not? The link to the document is in the FAQ thread, and there is no excuse, none, for not having done that basic research.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.

Post Reply