OptimusPy for TM1

Find the ideal dimension order for your TM1 cubes

Installing

Install required python packages:

pip install TM1py
pip install seaborn

Clone or download the optimus-py Repository from GitHub

Usage

• Adjust config.ini to match your TM1 environment
• Create uniquely named views in the relevant cubes
• Execute the optimuspy.py
• provide 8 arguments: -i (name of the instance) -c (name of the cube) -v (name of the cube view) -e (number of execution) -f (fast mode: True or False) -o (output: csv or xlsx) -u (update original order: True or False) -t (name of a ti process to measure runtime) -d (optional: comma split list of dimensions to keep positions as per the storage order)

C:\Projects\optimus-py\optimuspy.py -i="tm1srv01" -c="Cube Name" -v="Optimus" -e="10" -f="True" -o="csv" -u=True -t="load.csv.file"

C:\Projects\optimus-py\optimuspy.py --instance="tm1srv01" --cube="Cube Name" --view="Optimus" --executions="15" --fast="True" --output="csv" --update=True --process="load.csv.file"

You can use this public Google Sheet to construct the command prompt for the execution

https://docs.google.com/spreadsheets/d/1dtgl9WkYcsyokWNdX29m4K_5oNm3MI3iTOH2f_g6Kd4/edit?usp=sharing

Output

OptimusPy determines the ideal dimension order for every cube, based on RAM and query speed. For traceability and custom analysis, Optimus visualizes the results in a csv report and a scatter plot per cube.

ID	Mode	Mean Query Time	RAM	Dimension1	Dimension2	Dimension3	Dimension4	Dimension5	Dimension6	Dimension7	Dimension8	Dimension9
1	Original Order	0.00445528	259072	Industry	SalesMeasure	Product	Executive	Business Unit	Customer	Version	State	Time
2	Iterations	0.00379407	520184	SalesMeasure	Customer	Executive	Industry	Product	State	Time	Version	Business Unit
3	Iterations	0.00378995	520184	Business Unit	SalesMeasure	Executive	Industry	Product	State	Time	Version	Customer
4	Iterations	0.00422788	520184	Business Unit	Customer	SalesMeasure	Industry	Product	State	Time	Version	Executive
5	Iterations	0.00458372	520184	Business Unit	Customer	Executive	SalesMeasure	Product	State	Time	Version	Industry
6	Iterations	0.00479290	259072	Business Unit	Customer	Executive	Industry	SalesMeasure	State	Time	Version	Product
7	Iterations	0.00548539	259072	Business Unit	Customer	Executive	Industry	Product	SalesMeasure	Time	Version	State

Considerations

• Ideally run on the same machine as TM1
• Use big and representative views (e.g. typical slices that end users consume)
• Choose a sensible number of executions between 5 and 10
• Provide enough spare memory on TM1 server
• Fast mode determines first and last position only (Should get you 80% of possible improvement potential)
• XLSX output is preferable over CSV output but requires optional xlsxwrite dependency
• Choose a TI that loads data to the cube and runs for at least a few seconds

Need a .exe version of OptimusPy?

The latest executable build is available as an artifact in the GitHub Actions workflow runs. To download it:

1. Go to the Actions tab of the repository.
2. Click on the most recent workflow run titled Build Executable.
3. In the workflow summary, look for the Artifacts section.
4. Download the optimuspy-winOS artifact.

Built With

• TM1py - A python wrapper for the TM1 REST API
• matplotlib - A comprehensive library for crating visualizations in Python.

License

This project is licensed under the MIT License - see the LICENSE.md file for details