search

cubewise-code / tm1py

TM1py is a Python package that wraps the TM1 REST API in a simple to use library.
http://tm1py.readthedocs.io/en/latest/
MIT License
190 stars 109 forks source link

Issue with Extracting view into csv/panda dataframes #209

Closed Ankesh-Jain closed 4 years ago

Ankesh-Jain commented 4 years ago

Dear All,

First of all, I am very new with learning scripting through TM1Py and very excited to explore more. I have started following few sample scripts, I was trying to extract a cube view into a csv using "tm1.cubes.cells.execute_view_csv" and using Panda dataframe but every time it throws some error and I am unable to debug the same.

Note: I am using Jupyter Notebook to run the code.

The script is very simple and as below:

`from TM1py.Services import TM1Service

tm1 = TM1Service(address='localhost', port=36360, user='admin', password='apple', ssl=True)

csv = tm1.cubes.cells.execute_view_csv(cube_name = 'plan_BudgetPlan', view_name = 'Budget Input Total', private = False)

csv[0:200]`

from TM1py.Services import TM1Service tm1 = TM1Service(address='localhost', port=36360, user='admin', password='apple', ssl=True) df = tm1.cubes.cells.execute_view_dataframe(cube_name = 'plan_BudgetPlan', view_name = 'Budget Input Total', private = False) df.head()

The simple extract view using "tm1.cubes.cells.execute_view" is working fine and displays the results.

Version

I am getting the below error while running the above 2 codes: Using "tm1.cubes.cells.execute_view_csv":

TM1pyException Traceback (most recent call last)

in 3 tm1 = TM1Service(address='localhost', port=36360, user='admin', password='apple', ssl=True) 4 ----> 5 csv = tm1.cubes.cells.execute_view_csv(cube_name = 'plan_BudgetPlan', view_name = 'Budget Input Total', private = False) 6 7 csv[0:200] ~\AppData\Local\Continuum\anaconda3\lib\site-packages\TM1py\Services\CellService.py in execute_view_csv(self, cube_name, view_name, private) 369 def execute_view_csv(self, cube_name, view_name, private=False): 370 cellset_id = self.create_cellset_from_view(cube_name=cube_name, view_name=view_name, private=private) --> 371 return self.extract_cellset_csv(cellset_id=cellset_id, delete_cellset=True) 372 373 def execute_mdx_dataframe(self, mdx, **kwargs): ~\AppData\Local\Continuum\anaconda3\lib\site-packages\TM1py\Services\CellService.py in wrapper(self, cellset_id, *args, **kwargs) 21 def wrapper(self, cellset_id, *args, **kwargs): 22 try: ---> 23 return func(self, cellset_id, *args, **kwargs) 24 finally: 25 if kwargs.get("delete_cellset", True): ~\AppData\Local\Continuum\anaconda3\lib\site-packages\TM1py\Services\CellService.py in extract_cellset_csv(self, cellset_id, **kwargs) 796 """ 797 request = "/api/v1/Cellsets('{}')/Content".format(cellset_id) --> 798 data = self._rest.GET(request) 799 return data.text 800 ~\AppData\Local\Continuum\anaconda3\lib\site-packages\TM1py\Services\RESTService.py in wrapper(self, request, data, odata_escape_single_quotes_in_object_names) 41 response = func(self, request, data) 42 # Verify ---> 43 self.verify_response(response=response) 44 return response 45 ~\AppData\Local\Continuum\anaconda3\lib\site-packages\TM1py\Services\RESTService.py in verify_response(response) 327 status_code=response.status_code, 328 reason=response.reason, --> 329 headers=response.headers) 330 331 @staticmethod TM1pyException: Text: {"error":{"code":"","message":"'Content' resource can not be resolved on type 'Cellset'."}} Status Code: 404 Reason: Not Found Headers: {'Content-Length': '98', 'Connection': 'keep-alive', 'Content-Encoding': 'gzip', 'Cache': 'no-cache', 'Content-Type': 'application/json; charset=utf-8', 'OData-Version': '4.0'} Using "tm1.cubes.cells.execute_view_dataframe": --------------------------------------------------------------------------- TM1pyException Traceback (most recent call last) in 5 tm1 = TM1Service(address='localhost', port=36360, user='admin', password='apple', ssl=True) 6 ----> 7 df = tm1.cubes.cells.execute_view_dataframe(cube_name = 'plan_BudgetPlan', view_name = 'Budget Input Total', private = False) 8 9 df.to_csv("data.csv") ~\AppData\Local\Continuum\anaconda3\lib\site-packages\TM1py\Services\CellService.py in execute_view_dataframe(self, cube_name, view_name, private, **kwargs) 430 """ 431 cellset_id = self.create_cellset_from_view(cube_name=cube_name, view_name=view_name, private=private) --> 432 return self.extract_cellset_dataframe(cellset_id, **kwargs) 433 434 def execute_mdx_cellcount(self, mdx): ~\AppData\Local\Continuum\anaconda3\lib\site-packages\TM1py\Services\CellService.py in extract_cellset_dataframe(self, cellset_id, **kwargs) 806 :return: 807 """ --> 808 raw_csv = self.extract_cellset_csv(cellset_id=cellset_id, delete_cellset=True) 809 memory_file = StringIO(raw_csv) 810 # make sure all element names are strings and values column is derived from data ~\AppData\Local\Continuum\anaconda3\lib\site-packages\TM1py\Services\CellService.py in wrapper(self, cellset_id, *args, **kwargs) 21 def wrapper(self, cellset_id, *args, **kwargs): 22 try: ---> 23 return func(self, cellset_id, *args, **kwargs) 24 finally: 25 if kwargs.get("delete_cellset", True): ~\AppData\Local\Continuum\anaconda3\lib\site-packages\TM1py\Services\CellService.py in extract_cellset_csv(self, cellset_id, **kwargs) 796 """ 797 request = "/api/v1/Cellsets('{}')/Content".format(cellset_id) --> 798 data = self._rest.GET(request) 799 return data.text 800 ~\AppData\Local\Continuum\anaconda3\lib\site-packages\TM1py\Services\RESTService.py in wrapper(self, request, data, odata_escape_single_quotes_in_object_names) 41 response = func(self, request, data) 42 # Verify ---> 43 self.verify_response(response=response) 44 return response 45 ~\AppData\Local\Continuum\anaconda3\lib\site-packages\TM1py\Services\RESTService.py in verify_response(response) 327 status_code=response.status_code, 328 reason=response.reason, --> 329 headers=response.headers) 330 331 @staticmethod TM1pyException: Text: {"error":{"code":"","message":"'Content' resource can not be resolved on type 'Cellset'."}} Status Code: 404 Reason: Not Found Headers: {'Content-Length': '98', 'Connection': 'keep-alive', 'Content-Encoding': 'gzip', 'Cache': 'no-cache', 'Content-Type': 'application/json; charset=utf-8', 'OData-Version': '4.0'}
MariusWirtz commented 4 years ago

Hello @Ankesh-Jain ,

you are getting these errors because you are using an old version of TM1. TM1 10.2.2 FP5 has a limited scope of functionality in the REST API, and I believe it may actually be out of support already.

I would recommend you to upgrade to a recent version of TM1 11.x (a.k.a PA 2.x). In TM 11 you will have no constraints in terms of functionality of TM1py and the REST API. Also, TM1 11 comes with lots of enhancements, like alternative hierarchies and reduced locking.

If upgrading is not a short term solution for you, you can still generate a data frame from TM1. You need to do these two steps:

cells = tm1.cubes.cells.execute_view("SaleCube", "Default", private=False)
df = build_pandas_dataframe_from_cellset(cells, multiindex=False)

The resulting data frame will contain all dimensions as columns + one column with the values.

Cheers,

Marius