cubewise-code / optimus-py Goto Github PK

Hi when running optimus.py (on macOS) I get following error for the first cube reordered:
Traceback (most recent call last):
File "optimuspy.py", line 183, in
success = main(
File "optimuspy.py", line 150, in main
optimus_result.to_csv(view_name, RESULT_CSV.format(cube_name, view_name, TIME_STAMP))
File "/Users/tomasfelcman/Documents/Projects/TM1Py/optimus-py-master/results.py", line 91, in to_csv
os.makedirs(os.path.dirname(file_name), exist_ok=True)
File "/Applications/anaconda3/lib/python3.8/os.py", line 223, in makedirs
mkdir(name, mode)

Is there any prerequisite or limitation of optimus.py to the OS?
Thank you,
Tomas

Hi

I am trying to test the TM1Optipyzer, but I am now getting this error. I am not sure how to troubleshoot this?

I am just using the UX_Samples to test.

Please help me.

Hi Marius,

In the command prompt, Optimupy currently shows the new order:

I think it would be helpful to write a line just before with the original order.

Cheers,

Vincent

The output format must be configurable. The user must be able to choose between CSV and XLSX.

Unfavorable dimension orders can cause the TM1 server to run out of memory or crash.
Currently, Optimus aborts in those cases.

Optimus must save the results, that is has gathered so far, into CSV and PNG before aborting the execution.

Hello, trying to run this with a small view but see this error:

2020-08-26 13:56:59,417 - optimuspy - INFO - Starting. Arguments retrieved from cmd: Namespace(cube_name='TestCube', executions='5', instance_name='tm1srv01', max_permutations='5', measure_dimension_only_numeric=1, mode='Best', view_names='view1')
2020-08-26 13:57:01,726 - optimuspy - ERROR - Fatal error
Traceback (most recent call last):
File "optimuspy.py", line 76, in main
permutation_results += original_order.execute()
File "C:\Cubewise\optimus-py-master\executors.py", line 90, in execute
return [self._evaluate_permutation(self.original_dimension_order, retrieve_ram=True)]
File "C:\Cubewise\optimus-py-master\executors.py", line 53, in _evaluate_permutation
ram_usage = self._retrieve_ram_usage()
File "C:\Cubewise\optimus-py-master\executors.py", line 68, in _retrieve_ram_usage
value = self.tm1.cubes.cells.execute_mdx_values(mdx=mdx)[0]
TypeError: 'generator' object is not subscriptable

Introduce a new execution mode that finds and activates the best dimension order (based on best execution mode) for every cube in the instance.

Implement the SmartMode

Approach:

• Measure how every dimension behaves as the first dimension. Find dimension that has the best query speed as the first dimension
• then do the same thing for position 2 to n
• Once the changes from n to n + 1 are becoming insignificant abort

Hi Marius,

Great work on Optimus, I just tried it and it is working well.

I just tried it on a small cube and it took 5 min to run, I could see that after the 10th iteration, the Query time and Ram haven't changed much but Optimus still did 30 more iterations. It would be handy sometimes if you are short in time to have a parameter to enable a "fast" mode.

Something like python optimuspy.py -i=tm1srv01 -v=Optimus -e=15 -fast=true

If fast = true then Optimus will stop if the RAM Change < 1% and Query Time Change < 1% (or 5%)

Cheers,

Vincent

Hi Marius,

In the optimus result csv file, there is a column RAM change % that is very handy to see the impact on the RAM:

It would be convenient to have a new column for the query time, like Query Time Change %.

If you have this column/calculation, for ticket #9 , you could check if RAM Change % < 0.01 and Query Time Change % < 0.01 then stop Optimus!

Cheers,

Vincent

Hi,
Would it be possible not to skip cubes that have a dims with string elements? The expected behaviour is:
If the dim with strings is not the latest -> no string value is stored and thus any permutation can be run.
If the dim with strings is the latest, keep it latest (possible related ER) and run permutations of all the other dims.
Thank you

Unfortunately, to optimize for query speed, there is no silver bullet.
The query speed depends on the composition of the view.

Currently, Optimus is optimizing for one view only. It would be better if Optimus would take multiple views into account.
The new Query Time Compared To Orignal Order would be a weighted average over multiple views.

Hi Marius,

It's great that optimus supports multi cubes. I can see that for the first cube Employee Reporting, Optimus did 30 iterations (ID 1-> 29) but then for the next cube General Ledger did also 30 iterations (ID 30 -> 58).

I think when doing iteration for a new cube the ID number should start back to 1, as you can see in the screenshot, it might be confusing as you would expect to find the first iteration as 1 in the chart for General Ledger but actually, it is 30.

If you have 5 or 10 cubes you want to optimize, the ID will quickly grow to 3 digits and it will be harder to read the chart.

Cheers,

Vincent

Set VMT to a high value before starting the analysis

Reset VMT to the original value when done.

I'm using the optimus-py in my system, and I have a cube with 6 dimension, when I use the optimus-py it tests 19 interations, but with 6 dimension we have a possible number of 6 factorial (720) interations, So Why it only tests 19, and how we know that the best is one of this 19 and not one of the other 701 the we do not have tested.

After a few "Evaluated order" messages, the script aborts with the following error message:

Fatal error
Traceback (most recent call last):
File "C:/Users/qlikadm/IdeaProjects/optimus-py/optimus-py-master/optimuspy.py", line 167, in main
optimus_result = OptimusResult(cube_name, permutation_results)
File "C:\Users\qlikadm\IdeaProjects\optimus-py\optimus-py-master\results.py", line 127, in init
self.best_result = self.determine_best_result()
File "C:\Users\qlikadm\IdeaProjects\optimus-py\optimus-py-master\results.py", line 227, in determine_best_result
process_speed_range = [result.median_process_time(result.process_name) for result in self.permutation_results]
File "C:\Users\qlikadm\IdeaProjects\optimus-py\optimus-py-master\results.py", line 227, in
process_speed_range = [result.median_process_time(result.process_name) for result in self.permutation_results]
File "C:\Users\qlikadm\IdeaProjects\optimus-py\optimus-py-master\results.py", line 73, in median_process_time
median = statistics.median(self.process_times_by_process[process_name])
TypeError: 'NoneType' object is not subscriptable

Hi,
I have done some testing of the optimus py and there have been strange results for on of the cubes:

ID,Mode,Is Best,Mean Query Time,RAM,Dimension1,Dimension2,Dimension3,Dimension4

1,Original Order,False,0.09010518,4195328,MetrikaPL,Perioda,Verze,Rok
2,Iterations,False,0.08690834,4195328,Verze,Perioda,Rok,MetrikaPL
3,Iterations,False,0.08564043,4195328,MetrikaPL,Verze,Rok,Perioda
4,Iterations,True,0.08697665,2098688,MetrikaPL,Perioda,Verze,Rok
5,Iterations,False,0.08890033,4195328,MetrikaPL,Perioda,Rok,Verze
6,Iterations,False,0.08666933,2098688,MetrikaPL,Perioda,Verze,Rok
7,Iterations,False,0.08690453,2098688,Perioda,MetrikaPL,Verze,Rok
8,Iterations,False,0.15419602,2098688,Verze,Perioda,MetrikaPL,Rok

It looks like the optimal order is the original order - which should be correct as I run optimus againts the same view for multiple times. But the weird thing is the measures between the lines 1 and 4 differ highly in RAM.
It that because of the low overall RAM (2 MB - I have changed the scale in code)?

Thank you for any ideas...
T.

There should be way to configure Optimus to leave certain positions in a dimension order of a cube untouched. E.g. dimension 1 must always be time.

OptimusPy reads from the }StatsByCube cube to report on memory usage as it evaluates each permutation.

Where a permutation is tested and completes in less than a minute, there is a risk that the statistics are incorrect as "LATEST" is only updated each minute. Any tests completed before "LATEST" is updated will show the same memory usage.

To mitigate this, the run time of each test should be calculated and a if it took less than a minute wait until at least a minute has passed before reading the statistics from "LATEST".

There is a TM1s.cfg parameter called StatisticalSamplingPeriodMS but this does not seem to be effective when setting to something like 5000 with the expectation that "LATEST" may be updated every 5 seconds rather than every 60.

Hi,
Would it be possible to enable password saving to the config.ini file? It is purely for safety reasons.
e.g. Add parameter to prompt for password...?
Thank you

Hi Marius,

Currently if Performance monitor is not on, OptimusPy will try to get the RAM 4 times before generating the python error below:

Failed to retrieve RAM consumption. Waiting 15s before retry
Failed to retrieve RAM consumption. Waiting 15s before retry
Failed to retrieve RAM consumption. Waiting 15s before retry
Failed to retrieve RAM consumption. Waiting 15s before retry
Fatal error
Traceback (most recent call last):
  File "optimuspy.py", line 120, in main
    permutation_results += original_order.execute(reset_counter=True)
  File "C:\TM1py\optimus\executors.py", line 106, in execute
    return [self._evaluate_permutation(
  File "C:\TM1py\optimus\executors.py", line 69, in _evaluate_permutation
    ram_usage = self._retrieve_ram_usage()
  File "C:\TM1py\optimus\executors.py", line 92, in _retrieve_ram_usage
    raise RuntimeError("Performance Monitor must be activated")
RuntimeError: Performance Monitor must be activated

Screenshot below:

Instead of waiting for 4*15sec and generating an error, it would be great if OptimusPy could check first if the parameter PerformanceMonitor = True:

If it is not True, OptimusPy should not even start and just ends with a message like. OptimusPy requires PerformanceMonitor = True, please update the tm1s.cfg file before running OptimysPy again*

Cheers,

Vincent

Hi,
Here is the report that shows the wrong row as the optimal solution. Meanwhile I can see the better iteration in the report file:

I installed TM1py and MatPlotLib, tested the Check.py script to ensure that I can connect to my environment. I also updated the CONFIG.ini file to match my environment and ran the code and received the following error code:

c:\Users\User\AppData\Local\Programs\Python\Python310\Scripts\optimus-py-master>python optimuspy.py -i="tm1" -v="Global_Balance Sheet Trend - ACT" -e="10" -f="False" -o="csv" -u=True
Starting. Arguments retrieved from cmd: Namespace(instance_name='tm1', view_name='Global_Balance Sheet Trend - ACT', executions='10', fast='False', output='csv', update='True', password=None, process_name=None)
Traceback (most recent call last):
  File "c:\Users\User\AppData\Local\Programs\Python\Python310\Scripts\optimus-py-master\optimuspy.py", line 276, in <module>
    success = main(
  File "c:\Users\User\AppData\Local\Programs\Python\Python310\Scripts\optimus-py-master\optimuspy.py", line 134, in main
    with TM1Service(**tm1_args) as tm1:
  File "C:\Users\User\AppData\Local\Programs\Python\Python310\lib\site-packages\TM1py\Services\TM1Service.py", line 15, in __init__
    self._tm1_rest = RestService(**kwargs)
  File "C:\Users\User\AppData\Local\Programs\Python\Python310\lib\site-packages\TM1py\Services\RestService.py", line 200, in __init__
    self._start_session(
  File "C:\Users\User\AppData\Local\Programs\Python\Python310\lib\site-packages\TM1py\Services\RestService.py", line 354, in _start_session
    self.b64_decode_password(password) if decode_b64 else password,
  File "C:\Users\User\AppData\Local\Programs\Python\Python310\lib\site-packages\TM1py\Services\RestService.py", line 442, in b64_decode_password
    return b64decode(encrypted_password).decode("UTF-8")
  File "C:\Users\User\AppData\Local\Programs\Python\Python310\lib\base64.py", line 87, in b64decode
    return binascii.a2b_base64(s)
binascii.Error: Incorrect padding

I tested the code against other views and received the same issue. Is this related to a cloud implementation of TM1/PA?

Thank you in advance!

Hi, I have been playing around with optimus-py and I was getting an error in some intermittent case:

Traceback (most recent call last):
  File "/_install_pathPython/optimus-py-master/optimuspy.py", line 300, in <module>
    success = main(
  File "/_install_pathPython/optimus-py-master/optimuspy.py", line 221, in main
    optimus_result.to_png(
  File "/_install_pathPython/optimus-py-master/results.py", line 187, in to_png
    df = self.to_dataframe(view_name, process_name)
  File "/_install_pathPython/optimus-py-master/results.py", line 146, in to_dataframe
    rows.append(result.to_row(view_name, process_name, self.original_order_result))
  File "/_install_pathPython/optimus-py-master/results.py", line 96, in to_row
    original_median_query_time = float(original_order_result.median_query_time(view_name))
AttributeError: 'NoneType' object has no attribute 'median_query_time'

I traced it back to the case when optimal order is the original orded. IMHO it is due to replacement of permutation_result.mode:
results.py: line 140: permutation_result.mode = executors.ExecutionMode.RESULT

I think sufficient would be to add condition for result not being ORIGINAL_ORDER:
if permutation_result.permutation_id == self.best_result.permutation_id **and permutation_result.mode != executors.ExecutionMode.ORIGINAL_ORDER**: permutation_result.is_best = True permutation_result.mode = executors.ExecutionMode.RESULT

I will create Pull request for that...

Thanks,
Tomas

Just making a note for a new feature that I plan on implementing when I have a moment.

1. Add TI run time to test, ideally intended to test write back speed, but could test anything
2. change graph to 3D scatter plot
3. Add flags to control what tests to run

Cheers

I am using OptimusPy. The first thing it reports is the original dimension order of the cube. It shows a different order than what Arc shows. Screenshots are uploaded. TM1 Architect shows the same order Arc does.

Hello Marius,

I am trying to run the tool on a model on my own laptop. However, the connection to TM1 cannot be establised. Would you happen to have an idea what it could be ? See the picture please (if that works). It's the one in Notepad++, the one in Notepad is the example file. Login in my TM1 model is also admin-apple

For certain large views, the process seems to roll back:

2021-03-05 12:56:51,872 - optimuspy - INFO - Skipping cube 'group_le_planning' since view '____long_opening_view' does not exist
2021-03-05 12:56:52,047 - optimuspy - INFO - Starting analysis for cube 'group_planning'
2021-03-05 12:56:52,074 - optimuspy - INFO - Original dimension order for cube 'group_planning' is: '['scaling', 'group_view', 'group_layer', 'group_account', 'group_function', 'group_performance_segment', 'group_region', 'gs_gaap_code', 'group_scenario', 'projection_type', 'submission_version', 'currency', 'period', 'value']'

stuck here

I believe that the code that is retriggered is

self.tm1.cells.create_cellset_from_view(cube_name=self.cube_name, view_name=view_name, private=False)

Is there a time limit to this function?

There is no mention of xlsxwriter being utilized in the Readme, nor in the prerequisites.
It would be nice to have it mentioned as optional in case of Excel exports.

Thank you!
T.

Hello Marius,

I am currently trying to test optimus-py, however I get an error as soon as a cube is found that contains the view referenced in the command line call an error message occurs:

Skipping cube 'FCST Seasonality' since view 'z_Optimise' does not exist
Traceback (most recent call last):
File "optimuspy.py", line 184, in
success = main(
File "optimuspy.py", line 105, in main
original_vmm, original_vmt = retrieve_vmm_vmt(tm1, cube_name)
File "optimuspy.py", line 88, in retrieve_vmm_vmt
return tm1.cells.execute_mdx_values(mdx)
AttributeError: 'TM1Service' object has no attribute 'cells'

Hi Marius,

When looking at the result.csv, it is hard to tell which order is the one OptimusPy used to reorder the cube. As in the Mode column we can see Original Order, it would be great to have New Order for the ideal order:

Cheers,

Vincent

Hi Marius,

As you can see in the screenshot below, all logs in the command prompt are duplicated, for example:

Do you know why all outputs appear twice? For example:
Skipping cube 'Product' since view 'Optimus' does not exist
Skipping cube 'Product' since view 'Optimus' does not exist

Cheers,

Vincent

I gave OptimusPy a try today, but I run into errors at the end.
Any idea what could be wrong? Am I missing a package or something (I did check requirements.txt)?

Completed analysis for cube '011_PnL'
Updated dimension order for cube '011_PnL' to ['Function_PL', 'Cost_element', 'Data_source', 'Reporting_entity', 'Currency', 'Product_group', 'Period', 'Version_Master', 'PnL_measure']
Traceback (most recent call last):
File "C:/TM1py/tm1py-samples-master/optimus-py-master/optimuspy.py", line 253, in
output=cmd_args.output)
File "C:/TM1py/tm1py-samples-master/optimus-py-master/optimuspy.py", line 197, in main
RESULT_PATH / RESULT_PNG.format(cube_name, view_name, TIME_STAMP))
File "C:\TM1py\tm1py-samples-master\optimus-py-master\results.py", line 166, in to_png
plt.savefig(file_name, dpi=400)
File "C:\ProgramData\Anaconda3\lib\site-packages\matplotlib\pyplot.py", line 689, in savefig
res = fig.savefig(*args, **kwargs)
File "C:\ProgramData\Anaconda3\lib\site-packages\matplotlib\figure.py", line 2094, in savefig
self.canvas.print_figure(fname, **kwargs)
File "C:\ProgramData\Anaconda3\lib\site-packages\matplotlib\backends\backend_qt5agg.py", line 88, in print_figure
super().print_figure(*args, **kwargs)
File "C:\ProgramData\Anaconda3\lib\site-packages\matplotlib\backend_bases.py", line 2075, in print_figure
**kwargs)
File "C:\ProgramData\Anaconda3\lib\site-packages\matplotlib\backends\backend_agg.py", line 521, in print_png
cbook.open_file_cm(filename_or_obj, "wb") as fh:
File "C:\ProgramData\Anaconda3\lib\contextlib.py", line 112, in enter
return next(self.gen)
File "C:\ProgramData\Anaconda3\lib\site-packages\matplotlib\cbook_init_.py", line 407, in open_file_cm
fh, opened = to_filehandle(path_or_file, mode, True, encoding)
File "C:\ProgramData\Anaconda3\lib\site-packages\matplotlib\cbook_init_.py", line 392, in to_filehandle
fh = open(fname, flag, encoding=encoding)
FileNotFoundError: [Errno 2] No such file or directory: 'results\011_PnL_Optimus_2020-12-02_19-09-39.png'

For medium/ bigger size cubes, no logging is done between

Starting analysis for cube '***'
Original dimension order for cube '***' is 

and

Completed analysis for cube '***'
Updated dimension order for cube '***' to [

Once they are hooked up for analysis it would be nicer to have logs written for every update_storage_dimension_order with the result.