Is this a Useful PAX tip?

Post Reply
John Hammond
Community Contributor
Posts: 285
Joined: Mon Mar 23, 2009 10:50 am
OLAP Product: PAW/PAX 2.0.72 Perspectives
Version: TM1 Server 11.8.003
Excel Version: 365 and 2016
Location: South London

Is this a Useful PAX tip?

Post by John Hammond »

L & G

A user was moaning that, when he was moving from Cell to cell in a spreadsheet with PAX enabled, it was taking 5 seconds.

So PAX was intercepting an event and doing 5 seconds of work and then returning control to Excel to move the cursor.

We told him to type and run this in VBA: "Application.EnableEvents = false" and the problem went away. Furthermore all explicit actions such as PAX refresh, the Panel etc all worked fine.

So is this a handy way to resolve this problem, or a fool's paradise. We haven't exhaustively tested this, and told the user to try it and see, as it was making his work impossible otherwise.

But what if killing events for PAX may introduce further subtle errors? Does it use events to build internal structures which might become out of sync with reality?

Does anyone know what events PAX intercepts and what it does before returning control to Excel?

What could it possibly do in the 5 seconds of delay our user was experiencing? (The user speculated it was to do with External References as they had recently moved to a slower more secure server and were seeing slowness across the board. But why would PAX care about these?)

If anyone can shed a light on this it would be much appreciated. I'll let you know how our user gets on too...
User avatar
gtonkin
MVP
Posts: 1083
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: PAL 2.0.9.10; PAW 2.0.71
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

Re: Is this a Useful PAX tip?

Post by gtonkin »

Not sure I would do that as many things will no longer work as designed.

Is this a general issue or a particular book/sheet?
Look for volatile functions like today, offset and others. They can kill performance.
Adam
Posts: 74
Joined: Wed Apr 03, 2019 12:10 am
OLAP Product: IBM PA
Version: 2.0.9.x
Excel Version: Office 365 x32

Re: Is this a Useful PAX tip?

Post by Adam »

Recommend running a fiddler trace to get the specifics, but yes when you move in PAFE QR grid from cell to cell there’s some jitter.
Take care.
Adam
Wim Gielis
MVP
Posts: 2935
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.14
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Is this a Useful PAX tip?

Post by Wim Gielis »

If you select / show a different menu in the Excel ribbon, does the lag disappear ?

What is the version number ?
Last edited by Wim Gielis on Fri Aug 05, 2022 8:26 pm, edited 1 time in total.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly https://github.com/wimgielis
jorelb
Posts: 71
Joined: Fri Feb 13, 2009 1:41 am
OLAP Product: IBM Planning Analytics Cloud
Version: 2.0.9 IF (2)
Excel Version: 2016

Re: Is this a Useful PAX tip?

Post by jorelb »

Maybe a couple of things to look into.
1) If a custom report, is the "Refresh data on writeback" option set to "None" in the Planning Analytics Options?
2) Is the Excel Calculation Option set to "Manual".
User avatar
Steve Rowe
Site Admin
Posts: 2299
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: TM1 v6,v7,v8,v9,v10,v11+PAW
Excel Version: Nearly all of them

Re: Is this a Useful PAX tip?

Post by Steve Rowe »

I've hit this at a couple of customers and I forget which versions exactly but some have had the problem and then they fixed it and then it came back again.

The issue is caused by a huge volume of hidden named ranges in the workbooks. There is some product that creates hidden named ranges out there, never been able to track it down.
To see the named ranges

Code: Select all

Sub UnhideAllNames()
 
    Dim nm As Name
 
    For Each nm In ActiveWorkbook.Names
 
        nm.Visible = True
 
    Next
 
End Sub
You should suddenly see "thousands" of named ranges in the Name Manager, many of these will be error.

VBA to delete ALL named ranges in the wkb.

Code: Select all

Sub UnhideAllNames()
 
    Dim nm As Name
 On Error Resume Next
    For Each nm In ActiveWorkbook.Names
 
        nm.Delete
 
    Next
 On Error Goto 0
End Sub
This will skip over some names that have illegal characters in them which you'll need to delete manually.

HTH!
Technical Director
www.infocat.co.uk
burnstripe
Regular Participant
Posts: 159
Joined: Wed May 06, 2020 2:58 pm
OLAP Product: Planning Analytics
Version: 2.0.9
Excel Version: 2016

Re: Is this a Useful PAX tip?

Post by burnstripe »

Steve's solution should fix it. I've also seen this before but in perspectives instead of pax.
You can confirm if this is the issue by trying to open up the name manager, if it just hangs or takes an age it's because there's a ton of references.
User avatar
Steve Rowe
Site Admin
Posts: 2299
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: TM1 v6,v7,v8,v9,v10,v11+PAW
Excel Version: Nearly all of them

Re: Is this a Useful PAX tip?

Post by Steve Rowe »

burnstripe wrote: Tue Aug 09, 2022 4:32 pm Steve's solution should fix it. I've also seen this before but in perspectives instead of pax.
You can confirm if this is the issue by trying to open up the name manager, if it just hangs or takes an age it's because there's a ton of references.
This assumes that the named ranges are visible in the name manager, so make sure to run the first bit of code in my post. To be honest I've not seen issues with the Name Manager even with named range counts in the high thousands but this might be version dependant.
Technical Director
www.infocat.co.uk
Post Reply