TM1 Tools Issues And Discussions (Was *KEY_ERR Tracer)

[Martin Ryan]

it is is strange because if i do an alt+f9 then it all calculates correctly so the calculations work

What about if you press shift+f9 (or even F9)?

Martin

[dubs]

hi steve,

yes im logged in, what do you mean by admin license rights? im in the TM1 Admin group.

ive also tried shutting down and starting again - no joy, the version was the latest version posted by martin.

[Steve Vincent]

the difference between the normal user and admin is two fold - the security group is one part but to do alot of admin stuff you also need the perspectives license on your machine. the tracer uses some functionality that only works if that is installed, so i just wondered if you maybe didn't have the license and that might have been causing the error.

[Steve Rowe]

Is that right? Plenty of my users use the tracer and I don't think they have Perspectives??

[Steve Vincent]

some parts of it, yes. If you try and put a user name in to check the security it seems to use stuff that only works if you have perspectives. i'll double check when i get chance but that certainly was the case some time ago.

[Wim Gielis]

I'd just like to add that the KEY-ERROR tracer does not work with the new TM1 v9.4 functionality of active forms.

There, you typically have a DBRW function, whose first argument points to a cell holding a TM1RPTVIEW statement. E.g. a cube is called servername:PnL, a TM1RPTVIEW function could then be servername:PnL:3

However, I think this change can be overcome in the VBA code quite easily.

If cubename has 2 times a double point : then use the StrRev(...) function to return the position of the last : Lastly, use Left(...) to leave out the part after the last :

Wim

[Martin Ryan]

New revision includes the ability to press Escape to close the tracer, and also the addition of the "talk to the hand" button which turns autocalc off. Documentation updated to reflect the changes.

Martin

[Jeroen Eynikel]

Alan,

Awesome. I will try it out asap.

Jeroen

[TJMurphy]

Thank you, I look forward to playing with these.

Tony

[hbell]

Alan

... I tried the TM1 Tools out in our set up (we use Citrix with 9.1.3) and the formula zapper was extremely slow. I pressed escape after 3 mins and it had completed only 84 formulae (out of 4,000!). Is there anything I could have done wrong in installation? I copied the XLA to the Citrix box and added it in to Add-Ins in Excel. I was using your zap method. When I tried the second one (Steve's method), it did complete - but in about 2 minutes.

Alternatively is there any "agent" that Technologies might have put on the Citrix server that would cause it to run more slowly?

hugh

[Steve Rowe]

My instinct would be that you have calculation set to automatic in the spread sheet, without double checking the code I'm not sure how the tool deals with this.
The other option would be some event VBA in the workbook having an impact.
Either way this behaviour is not normal, does it happen on a vanilla slice from a cube viewer?
Cheers,

[Alan Kirk]

My instinct would be that you have calculation set to automatic in the spread sheet, without double checking the code I'm not sure how the tool deals with this.

Unfortunately, it's something that all three of us overlooked. I actually stored the calculation mode because I know that I intended to set it to Manual and then back again at the end, but I didn't. It's not in your or Martin's code at all. I think the expectation would be "who would be using TM1 with automatic calculation on?" when even Iboglix advises against it.

However it's an easy fix. In the meantime, it's important to check that the calculation mode is not Automatic before running it.

The other option would be some event VBA in the workbook having an impact.
Either way this behaviour is not normal, does it happen on a vanilla slice from a cube viewer?

The other option is a virus scanner; I know that when we were using McAfee at the office (since changed to Norton) VBA code ran noticeably slower than on my notebook (and it wasn't the difference in specs that would account for it); from the process monitor it appeared that McAfee seemed to be shoving its nose into almost every loop the code was executing. The difference was about 14,000 bytes per second on one of my personal computers, about 8,000 bytes per second on a McAfee "infected" machine. However even that wouldn't account for this kind of performance. Automatic calculation would, though.

[hbell]

Automatic recalc was OFF. So it is not that (which was my first thought too). I will keep digging and let you know

[hbell]

... incidentally .. yes, it is a totally plain vanilla slice that I am testing on. I tried stepping through in debug mode to see if there is any step that takes an abnormally long time .. but no (I guess 2 seconds through the loop would not really show up when stepping through)...

I'm engaging with Tech on the virus scanner thought - though I would have expected that to impact TM1 too (as it is also an XLA)...hugh

[Steve Rowe]

Hugh, Have you been able to check on other machines in your environment?

[Alan Kirk]

Hugh, Have you been able to check on other machines in your environment?

Whatever it is it doesn't seem to be Citrix as such; I just fired it up on a Citrix login. I took three identical slices consisting of 83,638 formula cells.

Steve's method: 10 seconds.
Mine: 47 seconds.
A commercial add-in that we used to use: 1 minute 42 seconds.

It suggests something in the local environment but outside of the virus scanner (and as I said even McAfee didn't affect code to the extent reported here) I can't think what that might be.

[John D]

Hi all.
I downloaded this tool and tried in our environment. I have been looking for years for solutions to some of the features your tool here gives me. Thanks.
Specially the feature regarding copying ranges of data into a range of DBR formulas has annoyed me a lot.

I just have a problem using it: I live in Denmark, Europe and we are a little different regarding the decimal sign and the Thousand sign. We write 1.111,00 where UK/US is writing 1.111,00.
This courses the tool to fail or let say it will not paste if I try to copy 1234,56. My regional settings is (off course) Danish. If I change to the English/US-way the tool works fine.

I hope that you in a future version could find a solution to my problem as I really can see the use of the tool for us.

Thanks in Advance
John D

[Alan Kirk]

Hi all.
I downloaded this tool and tried in our environment. I have been looking for years for solutions to some of the features your tool here gives me. Thanks.
Specially the feature regarding copying ranges of data into a range of DBR formulas has annoyed me a lot.

I just have a problem using it: I live in Denmark, Europe and we are a little different regarding the decimal sign and the Thousand sign. We write 1.111,00 where UK/US is writing 1.111,00.
This courses the tool to fail or let say it will not paste if I try to copy 1234,56. My regional settings is (off course) Danish. If I change to the English/US-way the tool works fine.

I hope that you in a future version could find a solution to my problem as I really can see the use of the tool for us.

Off the top of my head I can't think of anything that's (shall we say) "region-sensitive" in the tool itself, but it could be the result of a mismatch between the client settings and the server-side settings, especially given that it works when your regional settings (I presume that you mean on the client side) are in English/US. What's the language of the box that the server is running on?

I'll do some further tests and see what I can find.

[hbell]

Steve

... yes, I've tested in both our production and development environments. Same result. Clearly something peculiar to us. I'll defintely let you know when I get to the bottom of it. We have users who are desperate to be able to zap TM1 formulae, so we have a big incentive.

hugh

[Martin Ryan]

I tried stepping through in debug mode to see if there is any step that takes an abnormally long time .. but no (I guess 2 seconds through the loop would not really show up when stepping through)...

Hugh, you could set up an array or string variable that will record times as it goes through each line, then give yourself a msgbox or something other output at the end which will hopefully tell you which step is the one taking the time.

Martin