Internal Server Error SystemOutOfMemory

[user1493]

I tried exporting a TM1 cube using MDX expression but getting the below error, when I reduce the granularity of the output I'm able to export the data. The same granularity is possible when I open the cube view using Architect. But I wonder TM1PY could not handle it!

Can someone please let me know what is the dependency for this memory shortage? Is it not possible to export an entire cube with huge data? I don't mind the time taken, but is it possible? Note: I have installed TM1PY in my VM which has 8GB RAM, the server would have some 256GB RAM.\ Data is stored in server (as always)

`with TM1Service(*config['']) as tm1: mdx = "SELECT " \ "{[n].[o]} {[p].[q]} {[a].members} {[r].members} {[c].[c]} {[e].[f]} * {[g].members} on ROWS, " \ "{[i].[j]:[k].[l]} ON COLUMNS " \ "FROM [m]"

Get view content

content = tm1.cubes.cells.execute_mdx(mdx)
dx = Utils.build_pandas_dataframe_from_cellset(content, multiindex=False)`

`**--------------------------------------------------------------------------- TM1pyException Traceback (most recent call last)

in () 23 24 # Get view content ---> 25 content = tm1.cubes.cells.execute_mdx(mdx) 26 dx = Utils.build_pandas_dataframe_from_cellset(content, multiindex=False) 27 run_time = time.time() - start_time ~\AppData\Local\Continuum\anaconda3\lib\site-packages\TM1py\Services\CellService.py in execute_mdx(self, mdx, cell_properties, top) 174 :return: content in sweet consice strcuture. 175 """ --> 176 cellset_id = self.create_cellset(mdx=mdx) 177 try: 178 return self.execute_cellset(cellset_id=cellset_id, cell_properties=cell_properties, top=top) ~\AppData\Local\Continuum\anaconda3\lib\site-packages\TM1py\Services\CellService.py in create_cellset(self, mdx) 297 'MDX': mdx 298 } --> 299 response = self._rest.POST(request=request, data=json.dumps(data, ensure_ascii=False)) 300 cellset_id = response.json()['ID'] 301 return cellset_id ~\AppData\Local\Continuum\anaconda3\lib\site-packages\TM1py\Services\RESTService.py in wrapper(self, request, data) 28 response = func(self, request, data) 29 # Verify ---> 30 self.verify_response(response=response) 31 return response 32 return wrapper ~\AppData\Local\Continuum\anaconda3\lib\site-packages\TM1py\Services\RESTService.py in verify_response(response) 212 """ 213 if not response.ok: --> 214 raise TM1pyException(response.text, status_code=response.status_code, reason=response.reason) 215 216 @staticmethod TM1pyException: Text: Error: Internal Server Error SystemOutOfMemory Status Code: 500 Reason: Internal Server Error**`

[MariusWirtz]

Try to increase the MaximumViewSize in the tm1s.cfg: https://www.ibm.com/support/knowledgecenter/en/SS9RXT_10.2.2/com.ibm.swg.ba.cognos.tm1_inst.10.2.2.1.doc/c_maximumviewsize_1.html#MaximumViewSize_1

[lotsaram]

I think you need to think about what you are doing here. When a TI process is processing a view it does so one-cell-at-a-time. Although memory will build up over the process if consolidated and/or rule defined cells are processed causing the values to be evaluated and cached there is no big bang initial overhead of constructing the view. However with a Rest MDX query the TM1 server doesn't distinguish between a user query versus an ETL type data feed, they will both be treated the same like a user query to construct a view to be visualized in a UI in a 2-dimensional grid.

So if you are doing an MDX query of effectively the whole cube and if the cube is "huge". Then you can reasonably expect a big hit on resources as the server has to build the view. Especially if the view contains consolidated elements and rule-derived values then this will take a lot of extra memory. The only way around this I think is to limit the size of the view to a "sensible" size either by breaking up the query or using TOP & TAIL. Exactly what a sensible maximum query size is I guess will be case dependent on available server resources.

In your query you are doing a cross-join over 7 dimensions stacked on rows, 3 of which are for all members. You are also not using non empty so you will get all combinations including null records. How big are these dimensions? (since the number of rows will be the product of the count of members from those 3 dimensions). As to why an equivalent view works in architect, hard to say but possibly there is some row cache and fetch going on, or maybe your architect view is suppressing nulls?

[user1493]

Hey Lotsaram, I have always been your fan (atleast for the past couple of months in tm1forum). I'm not an MDX guru and I didn't get what you mean by TOP & TAIL. Thanks for bringing up "NON EMPTY" I will definitely use that and share the results. In Architect I did not use "Suppress Null" but as you mentioned, Stargate views might play some roll there.

[lotsaram]

I was referring to Head & Tail which you can use to break up a set expression into chunks of N items. For example you could use this to break up the MDX query into chunks of N rows.

TM1 Rest API also supports Odata conventions like $top which you could also use to break up the query and fetch different records at a time. This could also be used with multi-threading to increase overall throughput (e.g. for a query with 50K rows send 5 requests simultaneously each for 10K rows).