Performance of Architect Vs Perspectives

Post Reply
jwafro
Posts: 11
Joined: Thu Sep 07, 2017 8:31 am
OLAP Product: TM1
Version: 10.2.2 / 9.5.1
Excel Version: 2003 2010

Performance of Architect Vs Perspectives

Post by jwafro » Tue Oct 03, 2017 9:44 am

Hi

I am testing my model and am noticing different times to recalculate a standard view between Architect and Perspectives on my server.

I have spun up the same model twice on two instances and have the loggers recording view opening details, so I get precise times.
I restart the instances once the test is complete so I do not have outstanding values in cache to distort results.

For the view opened in Architect (10.2.2), get an average of 15min:29sec.

In Perspectives, on the server, this takes 17min:30sec, so 13% slower.
Perspectives runs on Excel 2010, 32bit.

When I perform these tests in perspectives over a CMP/WAN environment the times deteriorate further.

What I want to understand is if this is a normal difference in performance anyone else experiences, as well as if there are any settings I can change which could affect performance.

Wim Gielis
MVP
Posts: 1571
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0
Excel Version: 2016
Location: Brussels, Belgium
Contact:

Re: Performance of Architect Vs Perspectives

Post by Wim Gielis » Tue Oct 03, 2017 9:56 am

Can I first ask why a view would take more than 1 quarter to calculate and open ? Why is this ?
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 104 TM1 articles and a lot of custom code
Newest blog article: Delete a cube and its dimensions

jwafro
Posts: 11
Joined: Thu Sep 07, 2017 8:31 am
OLAP Product: TM1
Version: 10.2.2 / 9.5.1
Excel Version: 2003 2010

Re: Performance of Architect Vs Perspectives

Post by jwafro » Tue Oct 03, 2017 10:00 am

Hi Wim

Its a test view of several years consolidated data. I can acknowledge the model is lacking performance optimization/overfeeding certainly, but I am limited with time as my users are having to budget..

User avatar
gtonkin
MVP
Posts: 534
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: PAL 2.0.1
Excel Version: 2016 64-bit
Location: JHB, South Africa
Contact:

Re: Performance of Architect Vs Perspectives

Post by gtonkin » Tue Oct 03, 2017 10:07 am

Have you at least got MTQ configured?
Is Excel definitely set to manual calculation?

jwafro
Posts: 11
Joined: Thu Sep 07, 2017 8:31 am
OLAP Product: TM1
Version: 10.2.2 / 9.5.1
Excel Version: 2003 2010

Re: Performance of Architect Vs Perspectives

Post by jwafro » Tue Oct 03, 2017 10:09 am

Yes, MTQ is set to 6, and the view creation in Excel is run through perspectives rather then a data pull through excel

J

jwafro
Posts: 11
Joined: Thu Sep 07, 2017 8:31 am
OLAP Product: TM1
Version: 10.2.2 / 9.5.1
Excel Version: 2003 2010

Re: Performance of Architect Vs Perspectives

Post by jwafro » Tue Oct 03, 2017 12:39 pm

ive read somewhere on the forum this:

"Architect seems to go tcp/ip direct to the Server whereas Perspectives is using the WebServer IIS. Tomcat etc (ours is IIS) so it will always run more slowly."

http://www.tm1forum.com/viewtopic.php?t=1081


Is there anyway to tweek Tomcat (which I am using) to improve the responsiveness, either via cognos configurator or other programs?

User avatar
gtonkin
MVP
Posts: 534
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: PAL 2.0.1
Excel Version: 2016 64-bit
Location: JHB, South Africa
Contact:

Re: Performance of Architect Vs Perspectives

Post by gtonkin » Tue Oct 03, 2017 1:30 pm

Perspectives does NOT use IIS/Tomcat or other Web server. Perspectives is an add-in to Excel and talks directly to the admin server and relevant tm1 server. TM1 Web and Contributor investigations are not relevant for your issue relating to a slice being slower than a view refresh.

jwafro
Posts: 11
Joined: Thu Sep 07, 2017 8:31 am
OLAP Product: TM1
Version: 10.2.2 / 9.5.1
Excel Version: 2003 2010

Re: Performance of Architect Vs Perspectives

Post by jwafro » Tue Oct 03, 2017 1:38 pm

Ok, thanks for the clarification.

To clarify, the issue is a view created in perspectives (within the GUI, not a slice) takes longer then Architect.

User avatar
tomok
MVP
Posts: 2357
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: Performance of Architect Vs Perspectives

Post by tomok » Tue Oct 03, 2017 1:39 pm

That's an 8 year-old post (TM1 has changed a little bit since then) and it's just flat-out wrong. It was wrong back then and it's still wrong today. Architect is a stand-alone app that uses API calls in the background to do it's business and Perspectives is an add-in to Excel that users those same API calls. Is it possible that Perspectives is slightly slower than Architect because of what little Excel overhead there may be? Yes. I've never noticed an appreciable difference between the two. Is there anything you can do to make Perspectives faster? No. Is there anything you can do to your model to make it faster, which is going to be reflected in Perspectives? Absolutely, but it has nothing to do with the client you're using.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/

jwafro
Posts: 11
Joined: Thu Sep 07, 2017 8:31 am
OLAP Product: TM1
Version: 10.2.2 / 9.5.1
Excel Version: 2003 2010

Re: Performance of Architect Vs Perspectives

Post by jwafro » Tue Oct 03, 2017 2:10 pm

Ok, thanks.

The base issue, regardless of model performance, is a material time differential between the two access methods for the same view refresh.


So what I am hearing is that there should be minimal/immaterial lag/time variance between excel/Perspectives plugin and TM1.
There are no settings I can change to affect this relationship; it just works.

I imagine changing up to 64 bit Excel/Perspectives would make little difference in terms of responsiveness.

Paul Segal
Community Contributor
Posts: 229
Joined: Mon May 12, 2008 8:11 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2010

Re: Performance of Architect Vs Perspectives

Post by Paul Segal » Tue Oct 03, 2017 2:54 pm

The difference is only material because your model is taking so long to calc. Without knowing your model in detail it's difficult to be specific, but the speed issue will be down to the way the model is designed - as Win and Tomok have pointed out. I don't think your speed will improve by moving to 64-bit Excel.
Paul

jwafro
Posts: 11
Joined: Thu Sep 07, 2017 8:31 am
OLAP Product: TM1
Version: 10.2.2 / 9.5.1
Excel Version: 2003 2010

Re: Performance of Architect Vs Perspectives

Post by jwafro » Tue Oct 03, 2017 3:28 pm

Thanks, I thought as much about the 64bit.

I totally understand that the model is a factor, its incredibly complex, legacy and I've been tasked with migrating it to a new accounting standard. It absolutely is overfed, poorly designed and all that. But it works, and I don't have time to fix and test any fundamental changes before this goes live. I can do that in the future.


I guess I just don't understand why the exact same model & view performs differently under controlled circumstances with the only variable being the method of interaction. It should not matter how efficient the model is.


I've got no-one to sense check or provide guidance to my logic which is why I am here.



So yea, I get that there will be some minimal margin of overhead processing/speed to go through Excel. But I am seeing upwards of 20% increased time spent for the same base retrieve when run through Perspectives which is the problem, and I dont believe should occur regardless of the quality of the model.

User avatar
paulsimon
MVP
Posts: 552
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: 10.1.1 and 10.2.2
Excel Version: 2013

Re: Performance of Architect Vs Perspectives

Post by paulsimon » Tue Oct 03, 2017 9:05 pm

Hi

How many times have you run the test? With those sort of recalc times, I would guess that you haven't run the comparison tests many times. The network could also be an issue as you can get a lot of variability depending on other load, routing, etc. So if Architect seemed faster that might just be because it happened to run at a time when the network was a little less overloaded or the packets got routed more efficiently, etc. To get a reliable comparison you would need to run the tests on both several times. The number of times would depend on the network variability - the more variable, the more times you need to run the tests to get a reliable average. You would also need to ensure that there was no other load on the server or client at the time the tests were done. I would suggest that, instead of spending time doing that, you might get better dividends by looking at the rules and eliminating some of the worst overfeeding.

Like others I cannot see any reason why Perspectives would be slower than Architect on a View, apart from a minor overhead of interfaces with Excel. Essentially both products share the same DLLs and communicate in the same way.

Regards

Paul Simon

jwafro
Posts: 11
Joined: Thu Sep 07, 2017 8:31 am
OLAP Product: TM1
Version: 10.2.2 / 9.5.1
Excel Version: 2003 2010

Re: Performance of Architect Vs Perspectives

Post by jwafro » Wed Oct 04, 2017 10:16 am

Thanks Paul

It is time consuming, but to avoid network as a variable I've run the initial tests 1 at a time on the server itself, so should be direct, with at worst one or two other processes running (ive capped the MTQ to 25% of server capacity to help prevent that sort of related variability while doing these tests)

I'm also going to check security settings on Excel.. maybe our security packages are interrogating the data transfer from excel?

Drg
Posts: 64
Joined: Fri Aug 12, 2016 10:02 am
OLAP Product: tm1
Version: 10.2.0 - 10.3.0
Excel Version: 2010

Re: Performance of Architect Vs Perspectives

Post by Drg » Mon Oct 09, 2017 9:21 am

I agree with my colleagues and add my assumptions.
ARCHTECT and PERSPECTIVE are launched from one machine that is not a server?
If this is a server from which directories are the clients running?
you use VMM VMT ?
Do your tests have a chaotic startup order?

Post Reply