Planning Analytics

Planning Analytics

Get AI-infused integrated business planning

 View Only

  • 1.  OptimusPy for TM1 - Ideal dimension order finder

    Posted Mon October 12, 2020 05:36 AM
    Hello,

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


    logo
    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
    Optimus results as scatter plot

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

    ------------------------------
    Marius Wirtz
    ------------------------------

    #PlanningAnalyticswithWatson


  • 2.  RE: OptimusPy for TM1 - Ideal dimension order finder

    Posted Mon October 12, 2020 09:33 PM
    Hi Marius. Very nice tool! I was wondering about the query time column. Are all the query times based on "apples to apples" comparisons, meaning, are all the times based on cached views? Also, a suggestion, the RAM % change, might be better if each row's calculations are all based on the Original row rather than the previous row. Thank you!

    ------------------------------
    James Naff
    ------------------------------

    Original Message


  • 3.  RE: OptimusPy for TM1 - Ideal dimension order finder

    Posted Tue October 13, 2020 07:17 AM
    Hi James,

    thanks!

    We do "apples to apples" comparisons as much as we can.
    Before Optimus starts working on a cube, we manipulate VMM and VMT to make sure it doesn't interfere (it's set back to the original value when Optimus is done).

    To measure the query speed for a dimension order, we execute a view n times (default is 10) and measure the time it takes to build the cellset. Then we take the median value.
    Do you have more ideas on how to eliminate caching and one-off effects?

    I'm not sure I understand the second point.
    Internally, the TM1 REST API provides us the RAM percentage change (new vs. previous) when we apply a new order.
    However, we don't want to expose those relative values to the end-users. In the plot and the CSV, the RAM is always the translated absolute value.
    Are you suggesting to calculate those absolute values differently?

    ------------------------------
    Marius Wirtz
    ------------------------------

    Original Message


  • 4.  RE: OptimusPy for TM1 - Ideal dimension order finder

    Posted Tue October 13, 2020 12:57 PM
    Thank you for clarifying on VMM and VMT.

    On the second point, I was wondering if each row's calculation for the RAM Change in % based on the Original Order's row. For example:

    Original Row: RAM = 1000
    2nd Row: RAM = 750, RAM Change in % = -25%, which is what the output does today
    3rd Row RAM = 800, RAM Change in % = -20%, however today's output would show +6.7%.

    Another column could be added to show similar calculations for Query Time.

    Updating the CSV file to do these is easy so no big deal...just a thought.

    ------------------------------
    James Naff
    ------------------------------

    Original Message


  • 5.  RE: OptimusPy for TM1 - Ideal dimension order finder

    Posted Wed October 14, 2020 02:02 PM
    I ran this and one of our chores kicked off and put the thread Optimus-py was running in an IX lock. I am wondering if this could add to the query time?  I'll deactivate all the chores to be sure.  Again, great job with this tool, very helpful!

    ------------------------------
    James Naff
    ------------------------------

    Original Message


  • 6.  RE: OptimusPy for TM1 - Ideal dimension order finder

    Posted Wed October 14, 2020 10:48 PM
    @James Naff, I just hope that you are running this in a Development server and not the Production.  ​

    ------------------------------
    Vinoth Kumar Ravi
    ------------------------------

    Original Message


  • 7.  RE: OptimusPy for TM1 - Ideal dimension order finder

    Posted Thu October 15, 2020 07:18 AM
    Hi @JamesNaff,
     
    Thanks.
     
    If just one view execution was affected, it shouldn't impact the results (assuming that -e is > 2 and the view is executed at least a few times).
    Optimus takes the median value among the n view execution times that were measured.
    So it's robust against occasional outliers on query speed like the one you had when a chore interfered.
     
    I agree. It's good to have the chores deactivated anyway. You never know :)


    ------------------------------
    Marius Wirtz
    ------------------------------

    Original Message