OptimusPy - Ideal dimension order finder

Post Reply
MariusWirtz
Posts: 14
Joined: Sat Apr 08, 2017 8:40 pm
OLAP Product: TM1
Version: 10.2.2.6
Excel Version: 2016

OptimusPy - Ideal dimension order finder

Post by MariusWirtz » Mon Oct 12, 2020 8:29 am

Hello tm1forum,

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

Image

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

Image

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

User avatar
gtonkin
MVP
Posts: 819
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: PAL 2.0.3
Excel Version: 2016 64-bit
Location: JHB, South Africa
Contact:

Re: OptimusPy - Ideal dimension order finder

Post by gtonkin » Mon Oct 12, 2020 9:56 am

Sounds interesting - Thanks Marius.

User avatar
PavoGa
Community Contributor
Posts: 443
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.09
Excel Version: 2013 PAW
Location: Cleveland, Tennessee

Re: OptimusPy - Ideal dimension order finder

Post by PavoGa » Mon Oct 12, 2020 12:37 pm

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

User avatar
macsir
Community Contributor
Posts: 730
Joined: Wed May 30, 2012 6:50 am
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365
Contact:

Re: OptimusPy - Ideal dimension order finder

Post by macsir » Mon Oct 12, 2020 8:06 pm

Thanks, Marius. Will test it later.
In TM1,the answer is always yes though sometimes with a but....
http://tm1sir.blogspot.com.au/

MariusWirtz
Posts: 14
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

Post by MariusWirtz » Tue Oct 13, 2020 11:21 am

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 🙂

Post Reply