OptimusPy - Ideal dimension order finder
-
- Posts: 31
- Joined: Sat Apr 08, 2017 8:40 pm
- OLAP Product: TM1
- Version: 10.2.2.6
- Excel Version: 2016
OptimusPy - Ideal dimension order finder
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
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
- MVP
- Posts: 1229
- Joined: Thu May 06, 2010 3:03 pm
- OLAP Product: TM1
- Version: Latest and greatest
- Excel Version: Office 365 64-bit
- Location: JHB, South Africa
- Contact:
Re: OptimusPy - Ideal dimension order finder
Sounds interesting - Thanks Marius.
- PavoGa
- MVP
- Posts: 618
- Joined: Thu Apr 18, 2013 6:59 pm
- OLAP Product: TM1
- Version: 10.2.2 FP7, PA2.0.9.1
- Excel Version: 2013 PAW
- Location: Charleston, Tennessee
Re: OptimusPy - Ideal dimension order finder
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.
Ty
Cleveland, TN
Cleveland, TN
- macsir
- MVP
- Posts: 785
- Joined: Wed May 30, 2012 6:50 am
- OLAP Product: TM1
- Version: PAL 2.0.9
- Excel Version: Office 365
- Contact:
Re: OptimusPy - Ideal dimension order finder
Thanks, Marius. Will test it later.
-
- Posts: 31
- Joined: Sat Apr 08, 2017 8:40 pm
- OLAP Product: TM1
- Version: 10.2.2.6
- Excel Version: 2016
Re: OptimusPy - Ideal dimension order finder
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
If Optimus produces interesting or surprising results for your cubes please post the scatterplot here or on GitHub. I'm curious
-
- Posts: 6
- Joined: Fri Oct 23, 2020 2:16 am
- OLAP Product: TM1
- Version: PA 2.0.x
- Excel Version: 365
Re: OptimusPy - Ideal dimension order finder
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
Regards,
Rob
-
- MVP
- Posts: 3681
- Joined: Fri Mar 13, 2009 11:14 am
- OLAP Product: TableManager1
- Version: PA 2.0.x
- Excel Version: Office 365
- Location: Switzerland
Re: OptimusPy - Ideal dimension order finder
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.)
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
-
- Posts: 31
- Joined: Sat Apr 08, 2017 8:40 pm
- OLAP Product: TM1
- Version: 10.2.2.6
- Excel Version: 2016
Re: OptimusPy - Ideal dimension order finder
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.
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.
-
- Posts: 6
- Joined: Fri Oct 23, 2020 2:16 am
- OLAP Product: TM1
- Version: PA 2.0.x
- Excel Version: 365
Re: OptimusPy - Ideal dimension order finder
duh, of course - thanks for the reminder of why!
-
- Posts: 2
- Joined: Fri Jan 05, 2024 7:16 am
- OLAP Product: TM1
- Version: 11.0.9
- Excel Version: MS21
Re: OptimusPy - Ideal dimension order finder
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
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
- Attachments
-
- optimuspy error.png (32.67 KiB) Viewed 3583 times
-
- Posts: 31
- Joined: Sat Apr 08, 2017 8:40 pm
- OLAP Product: TM1
- Version: 10.2.2.6
- Excel Version: 2016
Re: OptimusPy - Ideal dimension order finder
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
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
-
- Posts: 2
- Joined: Fri Jan 05, 2024 7:16 am
- OLAP Product: TM1
- Version: 11.0.9
- Excel Version: MS21
Re: OptimusPy - Ideal dimension order finder
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
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
-
- Posts: 31
- Joined: Sat Apr 08, 2017 8:40 pm
- OLAP Product: TM1
- Version: 10.2.2.6
- Excel Version: 2016
Re: OptimusPy - Ideal dimension order finder
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
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
-
- Posts: 1
- Joined: Fri Oct 08, 2021 4:12 pm
- OLAP Product: TM1
- Version: PAX on Cloud
- Excel Version: O365
Re: OptimusPy - Ideal dimension order finder
Could you post an example of the ini file parameters to connect to v12?
-
- Posts: 31
- Joined: Sat Apr 08, 2017 8:40 pm
- OLAP Product: TM1
- Version: 10.2.2.6
- Excel Version: 2016
Re: OptimusPy - Ideal dimension order finder
If you are using v12 on MCSP, this should work.
Just substitute the 3 placeholders with their appropriate values.
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
-
- Posts: 21
- Joined: Thu Mar 28, 2013 10:12 am
- OLAP Product: IBM Planning Analytics
- Version: 2.0.93
- Excel Version: 2 0 94 7
- Location: Paris, France
- Contact:
Re: OptimusPy - Ideal dimension order finder
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
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