Finding ideal dimensions order with OptimusPy

This article explains how to leverage OptimusPy to optimize your cubes for performance and memory.

If you are interested in why it is important please refer to this article:

• Making TM1 applications faster and lighter

OptimusPy is a python script using TM1py and Matplotlib Librairies, therefore:

• Python is required
• Install TM1py v1.5 as minimum (pip install TM1py)
• Install Matplotlib (pip install matplotlib)

Once the prerequisites are ready, the next step is to download OptimusPy from GitHub. To download OptimusPy, click the link below:

Go to Code and click Download ZIP file:

Unzip the folder and copy the file into a new directory, in this example we create a new folder in C:TM1pyoptimus.

TM1py accesses the TM1 instances through the TM1 REST API. Therefore OptimusPy can run anywhere as long as TM1py is installed and the TM1 REST API is available.

Credentials to access the TM1 instance are stored in the config.ini file (More information to check the connectivity with your TM1 instances can be found here).

An example of config.ini:

[tm1srv01]address=localhostport=8352user=Adminpassword=applessl=True

Password does not have to be hard coded in the config.ini file. TM1py supports Single Sign On and Windows security (mode 3).

OptimusPy will look for all views with a specific name, once the view is found, it will start reordering the dimensions.

You will need to create a view with a specific name in all cubes that you want to optimize the dimensions order.

The view should be large enough (taking few seconds to refresh). In the example below, the view is called Optimus:

The view needs to be based on what the users are most likely to use. As it is explained in the article making TM1 applications faster and lighter, the dimensions in titles is what has the most impact on the calculation speed of the views.

Once you know that TM1py can connect to your TM1 instance and that the views have been created, the next step is to let OptimusPy do the magic!

Optimuspy has 4 arguments:

• i: instance name (as it is in the config.ini)
• v: name of the cube view
• e: number of execution of the view
• f: fast mode if set to True, it determines only the optimal first and last position

To execute the optimuspy script, just open a command line, navigate to the optimus script location and execute the following command:

python optimuspy.py -i="tm1srv01" -v="Optimus" -e="10" -f="False"

Once OptimusPy finished successfully, all cubes optimized will be set with the ideal dimensions order according to the view defined previously.

⚠️Depending on how big the view is and the number of cubes, OptimusPy might take few minutes. Therefore it is recommended to execute OptimusPy outside working hours or in a non-production environment.

Once OptimusPy has finished, the results can be found in the results folder. It visualizes the results in a csv report and a scatter plot per cube.

The csv file stores the information about all iterations. In the example below we can see that the 7th iteration was the ideal dimensions order to optimize query speed and memory:

As you can see in the view the dimension that OptimisPy put first is the one that is on titles.

To visualize the results, OptimusPy produces a scatterplot that can be found in the results folder (as you can see below, the 7th iteration is at the bottom left of the chart):

• Run on the same machine
• Use big and representative views
• Choose a sensible number of executions
• Provide enough spare memory on TM1 server
• Fast mode determines first and last position only

To learn more about the magic behind OptimusPy, please refer to the OptimusPy page:

• code.cubewise.com/optimuspy