cubewise-code / tm1py-samples

Do more with TM1 with these ready to use TM1py samples.
https://github.com/cubewise-code/TM1py
MIT License
59 stars 45 forks source link

relative_proportional_spread using TM1py #72

Closed pourushgupta closed 4 years ago

pourushgupta commented 4 years ago

I have been trying to implement this in my code but couldn't do that because i am unable to understand what to pass in reference_unique_element_names **def relative_proportional_spread( self, value: float, cube: str, unique_element_names: Iterable[str], reference_unique_element_names: Iterable[str], reference_cube: str = None, kwargs) -> Response:**** please help me with an example as this is an urgent requirement

MariusWirtz commented 4 years ago

Hi @pourushgupta,

if you are looking for a sample, and you don't find something in this project, you can check the tests folder in the TM1py project. We have test cases for most of the functions in TM1py. For the relative_proportional_spread method we have these test cases:

test_relative_proportional_spread_happy_case test_relative_proportional_with_explicit_hierarchies test_relative_proportional_spread_without_reference_cube test_relative_proportional_spread_with_different_reference_cube

This should help.

https://github.com/cubewise-code/tm1py/blob/fcdd5d57a483f1cc3aa30aa60065f393e0e56bb2/Tests/Cell.py#L223

pourushgupta commented 4 years ago

Hi @pourushgupta,

if you are looking for a sample, and you don't find something in this project, you can check the tests folder in the TM1py project. We have test cases for most of the functions in TM1py. For the relative_proportional_spread method we have these test cases:

test_relative_proportional_spread_happy_case test_relative_proportional_with_explicit_hierarchies test_relative_proportional_spread_without_reference_cube test_relative_proportional_spread_with_different_reference_cube

This should help.

https://github.com/cubewise-code/tm1py/blob/fcdd5d57a483f1cc3aa30aa60065f393e0e56bb2/Tests/Cell.py#L223

Thank you so much for the quick response. i tried referring those test cases but getting the below exception: TM1py.Exceptions.Exceptions.TM1pyException: Text: {"error":{"code":"278","message":"DataSpreadFailed"}} Status Code: 400 Reason: Bad Request Headers: {'Content-Length': '70', 'Connection': 'keep-alive', 'Content-Encoding': 'gzip', 'Cache-Control': 'no-cache', 'Content-Type': 'application/json; charset=utf-8', 'OData-Version': '4.0'}

Code used: I have two dimension h_brand and t_month please refer the screenshot below trying to apply the spread at Candy/Cough and Year but it is throwing the above exception. tm1.cubes.cells.relative_proportional_spread(19283,'test_cube',["[h_brand].[Mondelez International]","[t_month].[TOTAL_MONTH]"],["[h_brand].[Candy/Cough]","[t_month].[Year]"])

Please refer the screenshot: image

Please let me know if i am doing anything wrong

MariusWirtz commented 4 years ago

Hi @pourushgupta,

the error is DataSpreadFailed. This means TM1py executed a spread and it failed.

Does it work if you do the relative proportional spread on the same cell without TM1py?

pourushgupta commented 4 years ago

Thank you @MariusWirtz for replying Yes when i am trying it manually then it is working perfectly fine. please let me know if you need anything else please refer the screenshot image

MariusWirtz commented 4 years ago

Hi @pourushgupta , the code looks good. I'm surprised it doesn't work. What version of TM1 are you on?

pourushgupta commented 4 years ago

please refer the screenshot for version information i am using 2.0.7 tm1 version If you want hierarchy or dimension details that also i can send image

MariusWirtz commented 4 years ago

I will try to troubleshoot it this week. Could you upload the .dim files and the .cub file ?

pourushgupta commented 4 years ago

Hi @MariusWirtz Please find the attached .cub and .dim file for your reference. Do let me know if you need anything else. dim_cube_file.zip

MariusWirtz commented 4 years ago

Hi @pourushgupta,

In your screenshot, you are executing a proportional spread.

You can do the proportional spread with TM1py like this:

with TM1Service(address=ADDRESS, port=PORT, user=USER, password=PWD, ssl=SSL) as tm1:
    tm1.cubes.cells.write_values(
        'test_cube',
        {("Mondelez International", "TOTAL_MONTH"): 'P<>100'})

The shortcuts for the different kinds of spreading you find here: https://www.ibm.com/support/knowledgecenter/SSD29G_2.0.0/com.ibm.swg.ba.cognos.tm1_prism_gs.2.0.0.doc/c_tm1_prism_data_entry_shortcuts.html

pourushgupta commented 4 years ago

Thank you @MariusWirtz for the solution but unfortunately it is not working for me as i simply copy pasted the function but it is still throwing the same exception. ERROR BELOW: TM1py.Exceptions.Exceptions.TM1pyException: Text: {"error":{"code":"143","message":"DataSpreadFailed"}} Status Code: 400 Reason: Bad Request Headers: {'Content-Length': '70', 'Connection': 'keep-alive', 'Content-Encoding': 'gzip', 'Cache-Control': 'no-cache', 'Content-Type': 'application/json; charset=utf-8', 'OData-Version': '4.0'}

pourushgupta commented 4 years ago

Could you please provide a solution on this @MariusWirtz

MariusWirtz commented 4 years ago

The above script works for me and doesn't throw an Exception. So the code itself is alright. Here is what you can do:

To upgrade TM1py run: pip install https://github.com/cubewise-code/tm1py/archive/master.zip --upgrade

My version of TM1 is: 11.7.00002.1