OptimusPy - Ideal dimension order finder

[MariusWirtz]

Hello tm1forum,

we just released a new helpful utility powered by TM1py: OptimusPy
https://code.cubewise.com/optimuspy



It optimizes the dimension order for any cube in your TM1 model in a fully transparent and comprehensible approach.
Essentially it takes the guesswork out of the dimension reordering.

The algorithm to determine the right order is based on the theory that the higher positions in the dimension order drive the memory footprint of the cube, while the lower positions are a driver for the query speed.
Essentially what Optimus does is this: For a cube with n dimensions
- Try all dimensions at position n. Fixate the dimension that causes the lowest memory footprint.
- Try all left dimensions (= n-1) at position 0. Fixate the dimension that causes the best query speed.
- Try all left dimensions (= n-2) at position n - 1. Fixate the dimension that causes the best query speed.
- Try all left dimensions (= n-3) at position 1. Fixate the dimension that causes the best query speed.
- ...

Based on the results of this iterative approach, Optimus produces a CSV and a scatterplot. A sample is attached below.
Optimus will always try to strike a balance between RAM and query speed. In certain cases, the user may favor one over the other. Based on the CSV and the plot an alternative order can be found.

OptimusPy is free and open-source.
It's built with TM1py and uses matplotlib to create the plots.

Please feel free to contribute code or ideas if you have improvements or suggestions in mind.
https://github.com/cubewise-code/optimus-py



The plot compares the original order with all iterations (= evaluated dimension orders) and the ideal order

[gtonkin]

Sounds interesting - Thanks Marius.

[PavoGa]

Really neat utility. Getting better reductions in memory footprint vs. the built-in optimizer. Ran into some issues with query speed last week, but conducting some additional testing.

[macsir]

Thanks, Marius. Will test it later.

[MariusWirtz]

Thanks for the feedback!
If Optimus produces interesting or surprising results for your cubes please post the scatterplot here or on GitHub. I'm curious

[schmrob]

this might be a bit of a newb question but are there any issues caused by OptimusPy when it moves the measures dimension to a position other than the last?

Regards,

Rob

[lotsaram]

this might be a bit of a newb question but are there any issues caused by OptimusPy when it moves the measures dimension to a position other than the last?

As long as the cube contains no string data then no. (And if the cube does contain string data then the last dimension can't be moved. As in it's not possible.)

[MariusWirtz]

If the measure dimension has string elements, TM1 doesn't allow its repositioning
If this is the case in your cube, Optimus will determine the best order for position 1 to n-1 and exclude the measure dimension from the analysis.

Because the last position has the most impact on the memory footprint of the cube, it is considered a bad practice (for large cubes) to include string and numeric elements in the same measure dimension, as it doesn't allow efficient dimension reordering.

[schmrob]

duh, of course - thanks for the reminder of why!

[Ajit Kumar]

Hi Marius,

Greetings!

I was trying to use optimuspy as per steps provided on cubewise portal.

https://code.cubewise.com/tm1py-help-co ... -optimuspy

downloaded and installed all the packages as required, but i am getting the the error while running the command on CMD.
error screenshot has been attached.
can you please guide me here what wrong i am doing.

Reply is highly appreciated.

Thanks in advance!
Ajit Kumar

[MariusWirtz]

I can't reproduce the error.
Two questions:

- Are you using the latest version of OptimusPy?

- Do you have the same folder structure locally as in the project?
You need to download the folder as it is in the project here: https://github.com/cubewise-code/optimus-py
So the "optimuspy.py" file must be in the same folder as the "executors.py"

I hope this helps.
If the error persists, please raise the issue here: https://github.com/cubewise-code/optimus-py/issues

[Ajit Kumar]

Hi Marius,

Thanks for your reply.

I believe this is the latest version because i have downloaded from the link (https://github.com/cubewise-code/optimus-py) which was mentioned in the blog of cubewise for optimuspy.

I am referring this link to study for optimus py https://code.cubewise.com/tm1py-help-co ... -optimuspy


Yes, i have created the folder same way as it was instructed like, created TM1py/optimus/all files of optimuspy

i have used the same link https://github.com/cubewise-code/optimus-py to download optimus py.

i am getting some different errors now, will raise it on the link as you suggested.

Can you tell me if we have any online class available for tm1py or anything is scheduled to be done in future, i am curious to go through tm1py features and wanted to use in daily tasks of tm1.

Thanks in Advance!
Ajit kumar

[MariusWirtz]

Hi Ajit,

To get started on TM1py, I can recommend this training.
https://edu.cubewise.com/training-tm1py-fundamentals/

I also recommend familiarizing yourself with the basics of Python.
This YouTube tutorial covers all the basics in the first few hours.
https://www.youtube.com/watch?v=nLRL_NcnK-4

[ice1000]

Could you post an example of the ini file parameters to connect to v12?

[MariusWirtz]

If you are using v12 on MCSP, this should work.
Just substitute the 3 placeholders with their appropriate values.

Code: Select all

[tm1srv01]
base_url=https://us-east-1.planninganalytics.saas.ibm.com/api/<TenantId>/v0/tm1/<DatabaseName>/
user=apikey
password=<TheActualApiKey>
async_requests_mode=True
ssl=True
verify=True

[zbhmida]

Hi all,

Hope you are doing well!

i'm trying to use OptimusPy on IBM cloud, and i don't know if i can use it!

i ve succefully connected tm1py from my local PC to the cloud server ?

Any ideas,

best regards,
Zied