502 Error while requesting data from large view

nicolasbisurgi commented 4 years ago

Hi All, I'm new on the TM1Py world, I've been using it to take out some annoying administration activities and diagnose as well. I'm having troubles with one view alone (the biggest one) that has 17 dimensions as context (at the total consolidated element) and its +280k leaf level element product dimension as rows. The view is zero suppressed and the actual total value of cells is +55k, but when I try either: execute_view_cellcount or execute_view_dataframe (so I can then get a unique count on the df)

to actually get the 55k number from the python script, I ran into the following error:

TM1py.Exceptions.Exceptions.TM1pyException: Text: <!DOCTYPE HTML PUBLIC "-//IETF//DTD HTML 2.0//EN">
502 Proxy Error
Proxy Error

The proxy server received an invalid response from an upstream server.
The proxy server could not handle the request POST /tm1/api/Manufacturing_and_SC_Train//api/v1/Cubes('MSC_Analysis_Throughput')/Views('TM1Py_AnalyzeDensityandSparsity_Product_Itemization')/tm1.Execute.
Reason: Error reading from remote server

Status Code: 502 Reason: Proxy Error Headers: {'Date': 'Wed, 22 Apr 2020 15:47:38 GMT', 'Server': 'Apache', 'Strict-Transport-Security': 'max-age=31536000; includeSubdomains', 'Content-Length': '676', 'Content-Type': 'text/html; charset=iso-8859-1', 'X-Content-Type-Options': 'nosniff', 'Keep-Alive': 'timeout=5, max=65', 'Connection': 'Keep-Alive'}

For all other views (which are smaller) the code runs fine. It seems that there's a limit somewhere in the server that is dennying this request for its size. Could I fix this by modifying some parameters in the tm1s.cfg, in TM1Py or somewhere else?

We are using PA 2.0.8 in IBM Cloud

If anyone could point me out to the right direction I would appreciate it.

Thanks!

nico

MariusWirtz commented 4 years ago

Hi @nicolasbisurgi,

for TM1 in the IBM cloud, there is a hard timeout of 60 seconds. Unfortunately, it can not be configured.

TM1py needs to provide async requests so that you can execute a view with a request and then pull the result in a second request sometime later. We will implement this short term.

For the moment, perhaps you could simply break the view execution into n smaller executions. Like instead of executing one big cube view, you could execute multiple smaller MDX requests with a dynamic selection on the rows. Potentially you make break it down into chunks of 10k leaves.

{SUBSET({TM1FILTERBYLEVEL({TM1SUBSETALL([PRODUCT])},0)},0,10000)}
{SUBSET({TM1FILTERBYLEVEL({TM1SUBSETALL([PRODUCT])},0)},10000,10000)}

Does that make sense?

nicolasbisurgi commented 4 years ago

Hi Marius, Thanks a lot for the quick response. This makes sense as, when working with Cognos Command Center, we had server crashes when certain TI's (security based ones) where triggered and after a minute the TM1 server did not send any communication back. The way they "fix" this was to enabled Async calls which would avoid server crashing, but it also ment that we would never get a response from the TI that was called. I'll try to figure out how to break this into chunks with MDX and I'll wait anxiously for your implementation on async calls.

Thanks again!

nico

MariusWirtz commented 4 years ago

Hi @nicolasbisurgi,

I started working on a TM1py branch to enable full support for TM1 hosted in the IBM Cloud.

Can you please try if the new TM1py version solves the timeout issue? Just run this in the cmd to upgrade TM1py: pip install https://github.com/cubewise-code/tm1py/archive/feature/async-requests.zip --upgrade

code-wise you would only need to change one thing: when you instantiate the TM1Service, you need to pass one additional argument: ibm_cloud_mode=True

nicolasbisurgi commented 4 years ago

Hi Marius, Thanks for sharing this. I've downloaded the new version and tried running it, but every time I try I get the following error: File "C:/Users/bisurnx/PycharmProjects/Test_REST_Async_Calls/Test_New_Function.py", line 2, in from TM1py import TM1Service File "C:\Users\bisurnx\PycharmProjects\Test_REST_Async_Calls\TM1py__init__.py", line 19, in from TM1py.Services.ObjectService import ObjectService File "C:\Users\bisurnx\PycharmProjects\Test_REST_Async_Calls\TM1py\Services__init__.py", line 1, in from TM1py.Services.AnnotationService import AnnotationService File "C:\Users\bisurnx\PycharmProjects\Test_REST_Async_Calls\TM1py\Services\AnnotationService.py", line 7, in from requests import Response ModuleNotFoundError: No module named 'requests'

I checked the prior version and you don't have the line:

from requests import Response

Let me know if I'm doing something wrong or if I have to wait for an update code.

Thanks again!

nico

MariusWirtz commented 4 years ago

Hi @nicolasbisurgi,

It looks like you don't have requests installed. The requests library is one of the dependencies of TM1py. There are other dependencies like pandas and numpy. The dependencies were required for the previous version as well.

Normally if you install TM1py with pip (the little command line statement I posted in my previous post) it should install all dependencies for you.

Please check if requests is installed in your python environment:

pip show requests

nicolasbisurgi commented 4 years ago

Hi Marius, I'm sorry, I think I'm still in long weekend mode since I forgot to do the upgrade on my virtual environment. Good news is that, after upgrading and inluding the new ibm_cloud_mode parameter I'm not able to fetch data from one of our big views! Here are the results:

2,150,295 cells were fetched in 224.14 seconds

Thank you so much for this!!

MariusWirtz commented 4 years ago

Hi,

happy to hear that the new version works. We will merge it into the master and publish this version to PyPI soon.

MariusWirtz commented 4 years ago

Hi @nicolasbisurgi,

after discussing with the team, we decided to change the name of this parameter to async_requests_mode. The code is already changed in the feature branch. I just wanted to let you know, so that you don't get a bad surprise once you upgrade.

nicolasbisurgi commented 4 years ago

Hi Marius, Thanks a lot for the heads up!

cubewise-code / tm1py-samples

502 Error while requesting data from large view #69

Proxy Error