TM1 Stress Testing

[TomBr]

Hi,

I will fairly soon need to stress test TM1 and ideally need to find a way to simulate 50-100 users logging on to TM1 and refreshing an Excel Report concurrently.

The only way I can think to do it (without involving 50-100 users, which isn’t feasible) is to have maybe 10 users who have 5 to 10 different Excel\TM1 sessions running. They can open the spreadsheet in the different sessions and press F9 in each one consecutively.

I think the problem with this approach is that you would need a fairly decent PC to support multiple sessions of Excel. Sometimes with even one large TM1 spreadsheet it can freeze the whole PC for a time so you might have to wait to press the next F9 which would miss the point.

I have tried it with 3 Excel sessions and it works fine on a small spreadsheet.

Has anybody tried anything similar ? Perhaps something clever with macros or virtual PC's ?

The environment is 9.1 on 64 bit – upgrade from v8.4 on 32 bit. The cubes are pure data rather than rules driven.

Any thoughts appreciated.

Tom

[John Hobson]

If you're theory about running multiple Excel sessions would work, then you could achieve something similar with less system overhead by just using Architect.

OK this would not be a true test of TM1 in an excel environment but then neither would multiple Excel versions on the same PC.

The other way to "simulate 50-100 users logging on to TM1 and refreshing an Excel Report concurrently" might be to go to your local DIY store and purchase a litre of Dulux and a brush, open the can, dip the brush in the contents, smear it on a wall and start the simulation. Having said that that level of absolutely concurrent usage would be very unusual in my own experience.

The "drying time" is going to depend on, whether any data is updated during the simulation (the biggy), whether any summary data has already been cached, the number of calculations on the Excel sheet, the levels and complexity of the data being sourced from TM1 and the structure of your model - i.e. will all calcs come from the same object (cube).

Hope this helps

J

[Alan Kirk]

Hi,
I will fairly soon need to stress test TM1 and ideally need to find a way to simulate 50-100 users logging on to TM1 and refreshing an Excel Report concurrently.

I concur with John; it'll be pretty unlikely that you'll have that number of users pounding the key simultaneously in real life anyway.

We haven't done stress tests like this since we went from 7.1.4 to 8.2.12 (well, 8.2.10 really but I try to put that out of my mind), but the way we did it was VBA-based. Get a number of volunteers, in our case both Citrix (Terminal Services at the time) and local client based, then have them log in and load up a spreadsheet which automatically steps through a sequence of both reads and writes and spits the result out to a text file for analysis. (The code was triggered by the Workbook Open event, which meant that the users could do this just before they go home.) Unfortunately ours wouldn't be any good to you (even if I could find it, and I'm not sure that I could) because it was customised to our cubes. (Unfortunately it didn't test that one combination where input to cube A while someone was reading Cube M would crash the server. Or that may have been 8.2.11. Whichever.)

The next time we do it we'll go that route again, but we'll also have TM1 Top running (not an option that we had with 8.2.12) to help with the diagnostics.

[TomBr]

Thanks for the replies so far - just a bit of further information.

The Excel sheets will be read only and the numbers will generally be coming from more than one cube.

While 100 users concurrently might be stretching it - 50 is entirely possible at certain times after data refreshes on key days at month end.

Thanks again

Tom

[David Usherwood]

As it happens we did a stress test exercise earlier this year which was VBA based - but it was focused on update rather than retrieval. I wanted to test out how the much-touted locking changes in 9.1 worked in practice. I wrote some VBA which populate a send area with data (with controlled sparsity) then sent it, with some randomisation of the timings. I circulated it round the community (Dan Bernatchez and Michael Cowie) then sent it to Cognos. In a conversation with Dave Corbett at the Vegas conference he suggested that the 9.1 locking needed microcubes to work well, so I rewrote/retested/resubmitted it.

I'd be happy to put it up for others to use - Martin, how do I do that?

We did the multi user testing in our own office, where we have many more PCs and servers than people - but I didn't try more than four. The results were 'interesting'. Just let's say that my view that 9.1 isn't worth moving to (over 9.0SP3U4/7) was not disturbed.

My own take on what you are doing is that since you are doing multi-user reads the main block will be network bandwidth since the results will be cached on the server. But I would expect 9.0 to beat 9.1. 9.4 might be worth looking at - should be out about now.

[Martin Ryan]

I'd be happy to put it up for others to use - Martin, how do I do that?

When you make the posting just click on the "Upload attachment" tab at the bottom next to the Options tab. Excel files probably won't be accepted, but zip files will be.

If you want you could start a new post and tag it with the "useful tools" tag already in use.

Martin

[Alan Kirk]

9.{Version Number Censored} might be worth looking at - should be out about now.

Oh PLEASE tell me that you didn't use the 9.F-word!

INNNNNCOMMMIINNNNGGGG!!!!

{Everyone dives into the nearest foxhole as Eric comes through the door...}

[Mike Cowie]

The Excel sheets will be read only and the numbers will generally be coming from more than one cube.

While 100 users concurrently might be stretching it - 50 is entirely possible at certain times after data refreshes on key days at month end.

Hi Tom,

I certainly understand your wanting to do a test that represents the number of people who might be in at one time. But just be sure, when designing your test, that you think about whether you really want to do a stress test or more of a simulated load test. When I hear stress test I think of pushing a system to a point of some kind of failure. For example, bringing on a couple people at a time until the TM1 Server can't handle any more people and effectively stops responding efficiently to user requests and/or crashes. A load test would entail bringing on a number of users to simulate some expected load. Of course, this could turn into a stress test if TM1 can't handle that load of users, but more likely than not you're just going to be monitoring response times and performance on the TM1 Server to see how things run with that user load.

If you're interested in a representative load test and if you go a more automated route, I would try to make sure you are doing something that more closely mirrors the number of users and their habits. For example, an automated VBA routine that continually refreshes the workbook is not representative of what users do - users will refresh, look at the results, and then probably refresh another report, etc - so, be sure to build in a delay - sometimes testing programs call these wait times or think times. Try to be realistic here - it's likely to be a good 30 seconds or possibly minutes between refreshes. Also, you may not want to just start hammering away with all 50 - it might be more interesting to bring people on gradually to see where, if it happens, performance starts to drop off significantly. And, another very important thing for TM1 testing: if you expect updates to cubes on the server to be happening during your tests (maybe not by these users, but possibly someone entering an adjustment here and there), be sure to incorporate that type of occasional update into your test. This will force TM1 to not always rely on cached data for the users, which could give you a rosier picture of performance.

*whoops, accidentally hit Submit too soon*

I definitely support some of the other points in this thread about measuring the performance. It is important not to just look at the time stamps on the client side. If client performance is going downhill because of network bandwidth you would most likely see this at the TM1 Server, for example if its CPU usage was very minimal. In other words, try to get metrics from the various components involved in the test or at the very least try to watch both the server and clients (TM1TOP being one newer tool that we have to look at).

Regards,

[ykud]

Sorry for raising the dust, but I've made a simple Java-based tool for this purpose. Hope it helps someone.

- Yuri

[image2x]

Sorry for raising the dust, but I've made a simple Java-based tool for this purpose. Hope it helps someone.

- Yuri

Yuri, just wanted to thank you for sharing this. I haven't implemented the stress test (yet) but I've found the example code to be invaluable given the dirth of javaapi documentation/examples.

-- John

[ykud]

Yuri, just wanted to thank you for sharing this. I haven't implemented the stress test (yet) but I've found the example code to invaluable given the dirth of javaapi documentation/examples.

-- John

Glad to help, John. Java API is scarcely documented, so if you're stuck anywhere -- drop me a line.

- Yuri.