Ideal dimension order finder for TM1
OptimusPy is a Python script leveraging TM1py to find the ideal dimension order for your cubes. OptimusPy will swap dimension orders until it finds the order with the best query speed and the smallest memory footprint.
How does it work?
OptimusPy will go through all cubes and search for the specified view name. Once the view is found it is going to start the iterations, one iteration can be split in three main steps:
Once OptimusPy has gone through all the iterations, it is going to compare them and find the dimensions order with the best query time and the smallest memory footprint.
- Update dimensions order
- Execute the view (e times)
- Store the median time to refresh the view and the new memory consumption of the cube
The algorithm
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 lowest memory footprint.
- Try all left dimensions (= n-2) at position n – 1. Fixate the dimension that causes the lowest memory footprint.
- Try all left dimensions (= n-3) at position 1. Fixate the dimension that causes the lowest memory footprint.
Results
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.
How to use it?
OptimusPy is a free, open-source and very easy to install. The following article will guide you step by step through your first execution of OptimusPy: