Stress testing TM1 models

Ever thought about load testing your dear-loved TM1 project? Emulating a hundred (or hundreds) users reading and writing data simultaneously? it’s easy and simple!

Never trust sales slogans like above. Any kind of stress testing system should be approached with a huge amount of doubt, as well as this one. But it’s a simple and useful tool for testing your calculations for scalability (IMHO).

We needed to stress-test one of our TM1 models and looked around for solutions. Most of them are discussed in a very interesting thread on olapforums, but going via Excel + VB way seemed a bit too much of ‘Excel + F9’ to emulate the load. Turned out that it’s rather easy to roll-out a simple customizable Java application for this task, so here it is.

What the tool does

This tool tests your model for scalability and potential locking problems while performing calculations.
It doesn’t test you client connectivity (Excel, Contributor, Websheets) because it works via Java API. It doesn’t test your environment (network latency etc) as well.
It allows you to find and optimize the long-running and potentially blocking calculations in the model.
It allows you to simulate different types of users (like “What will happen when brand managers are inputting sales plan and analysts are working with investment portfolio and CashFlow at the same time?”).

Attention: Tool writes data to your model, don’t use it in production.

How does it work

You fill a user profile, containing:

  • logins&passwords list to be used
  • cubes for reading
  • cubes for writing
  • a list of dimension and their elements for writing — this is used for various purposes like simulating users from different regions (by specifying region in the list). Data will be written only to this elements in all the cubes containing dimension.
  • number of user session to be started with this profile

When you run the tool with filled in profile (you can run different profiles at the same time), it creates a required number of user sessions and in each session:

  • flips a coin to decide whether to read or write
  • (if read) creates a random view in one of the cubes for writing and reading and reads it contents
  • (if write) writes a random number (less than 100) of randomly picked cells into one of cubes for writing
  • waits 3 seconds and goes on again. Maybe this wait interval should be random as well )

After starting the tool you fire up TM1Top and enjoy the results.

You’re interested in locking problems and long running calculations that are nicely depicted there (an example is underlined in the picture above).
You can run TM1perfmon to see the hardware side of the problem at the same time

After you’ve got enough, just hit CTRL + C in command window and it will stop.
As a side bonus you’ll get a test log in the file test_results_profile_name.csv which will contain something like this:

How to install&use it

  1. You will need a only Java machine (jre), which you most likely already have, because of number of web-plugins developed in Java. This the only requirement, there’s no need to have TM1 installed or anything.
  2. Download the tool, unpack to some directory (c:\tm1stress, for example)
  3. Cryptic step of adding security certificate to your Java.
    1. Find jre\bin\keytool.exe utility on your computer
    2. Open the command line and navigate to jre\bin
    3. Run command line keytool -keystore ..\lib\security\cacerts -alias Company -import -file c:\tm1stress\applixca.der
    4. Enter ‘changeit’ as password
    5. Type yes
  4. Create a new user profile xml file, use the planning sample one as an example (if don’t need some sections — just leave them blank)
  5. Unarchive the c:\tm1stress\TM1JavaAPI_version.jar file relevant to your TM1 version (if you have 9.5.2, use TM1JavaApi_952). A c:\tm1stress\com folder should be created.
  6. Open command line, navigate to c:\tm1stress, run java tm1_stress_test your_profile_filename.xml
  7. You should get some informational messages (logins, operations, errors) in command output

There’s application source code in the download archive, it may serve as Java API usage sample.

Update 08/11/2012: Updated to version 0.3. Including 3 TM1API jar files to allow selecting appropriate API version.