Planning Analytics

 View Only
Expand all | Collapse all

Strange behavior of drill through in PAfE

  • 1.  Strange behavior of drill through in PAfE

    Posted Tue November 29, 2022 04:34 PM

    Hello!

    Do you have any experience with drill through from PAfE? Do you have any tips for better performance? Are there any config parameters or timeouts that could be related to this?

    We have ODBC drill and for scenarios with relatively small number of returned records it works fine. But some scenarios return about 4000 records and then behavior is strange/slow. I will describe 2 issues.

    1. Slow speed of loading additional rows and columns

    I tested same scenario in Perspectives, TM1 Web and Architect. Perspectives and TM1 Web return all 4000 records at once as a new sheet and everything is fine. Architect works fine as well, when scrolling I could see in session monitor that function SQLTableGetNextRows is executed and rows are returned instantly.

    However scrolling in PAfE in pop-up drill window is sometimes slow, I can see Watson logo with cancel option and it takes few seconds(sometimes even more) to show next rows. Below you can see that it took about 40s. 
    I can understand that when drilling it can take some time to run query against ODBC data source until it shows first few rows in pop-up drill window. But after that I can see in the logs only GET RowSets request and this is quite slow from our perspective. Below you can see records from tm1server.log. 

    Drill through execution: 

    784   [36]   DEBUG   2022-11-29 14:54:14.612   TM1.HttpRequest   POST /api/v1/ExecuteMDX?$expand=Axes
784   [36]   DEBUG   2022-11-29 14:54:14.614   TM1.HttpRequestBody   {"MDX":"SELECT xxx"}
784   [36]   DEBUG   2022-11-29 14:54:14.614   TM1.HttpResponse   201 Created
784   [36]   DEBUG   2022-11-29 14:54:14.614   TM1.HttpResponseBody   {"@odata.context":"$metadata#Cellsets(Axes)/$entity","ID":"aJON6jwUAICrAgAg","Axes":[{"Ordinal":0,"Cardinality":1},{"Ordinal":1,"Cardinality":1},{"Ordinal":2,"Cardinality":1}]}
784   [36]   DEBUG   2022-11-29 14:54:14.618   TM1.HttpRequest   GET /api/v1/Cellsets('aJON6jwUAICrAgAg')/Cells(0)/DrillthroughScripts?$count=true
784   [36]   DEBUG   2022-11-29 14:54:14.619   TM1.HttpResponse   200 OK
784   [36]   DEBUG   2022-11-29 14:54:14.619   TM1.HttpResponseBody   {"@odata.context":"../../$metadata#Cellsets('aJON6jwUAICrAgAg')/Cells(0)/DrillthroughScripts","@odata.count":1,"value":[{"@odata.type":"#ibm.tm1.api.v1.RelationalDrillthrough","Name":"Drill"}]}
784   [36]   DEBUG   2022-11-29 14:54:14.621   TM1.HttpRequest   POST /api/v1/Cellsets('aJON6jwUAICrAgAg')/Cells(0)/DrillthroughScripts('Drill')/tm1.ExecuteWithRowset
784   [36]   DEBUG   2022-11-29 14:54:14.623   TM1.HttpRequestBody   {}
784   [36]   DEBUG   2022-11-29 14:54:14.623   TM1.HttpResponse   202 Accepted
784   [36]   DEBUG   2022-11-29 14:54:14.624   TM1.HttpRequest   GET /api/v1/_async('XSGBXKXY5YZNN24rg0wzK6occ5_gF')
784   [36]   DEBUG   2022-11-29 14:54:14.624   TM1.HttpResponse   202 Accepted
15064   [36]   INFO   2022-11-29 14:54:56.460   TM1.Process   Process "}Drill_process":  finished executing normally, elapsed time 41.83 seconds
15064   [36]   DEBUG   2022-11-29 14:54:56.462   TM1.HttpResponse   201 Created
15064   [36]   DEBUG   2022-11-29 14:54:56.462   TM1.HttpResponseBody   {"@odata.context":"../../../$metadata#Rowsets/$entity","ID":"aJON6jwUAICsAgAg"}
784   [36]   DEBUG   2022-11-29 14:55:04.291   TM1.HttpRequest   GET /api/v1/_async('XSGBXKXY5YZNN24rg0wzK6occ5_gF')
784   [36]   DEBUG   2022-11-29 14:55:04.304   TM1.HttpResponse   200 OK
784   [36]   DEBUG   2022-11-29 14:55:04.304   TM1.HttpResponseBody   HTTP/1.1 201 Created
Content-Length: 91
Connection: keep-alive
Content-Encoding: gzip
Cache-Control: no-cache
Content-Type: application/json; odata.metadata=minimal; odata.streaming=true; charset=utf-8
Location: ../../../Rowsets('aJON6jwUAICsAgAg')
OData-Version: 4.0
784   [36]   DEBUG   2022-11-29 14:55:04.308   TM1.HttpRequest   GET /api/v1/Rowsets('aJON6jwUAICsAgAg')/Rows?$skip=0&$top=120
2022-11-29 15:00:31 GMT  Shared memory reader PID 14452 started, for shmem "tm1s.exe-11416_16"
784   [36]   DEBUG   2022-11-29 14:55:05.664   TM1.HttpResponse   200 OK
784   [36]   DEBUG   2022-11-29 14:55:05.664   TM1.HttpResponseBody   {"@odata.context":"../$metadata#Rowsets('aJON6jwUAICsAgAg')/Rows","value":xxx


    Scrolling in pop-up drill window:

    12116   []   DEBUG   2022-11-29 15:00:31.599   TM1.HttpRequest   GET /api/v1/Rowsets('aJON6jwUAICsAgAg')/Rows?$skip=52&$top=120
2022-11-29 15:05:14 GMT  Shared memory reader PID 13680 started, for shmem "tm1s.exe-11416_16"
12116   [36]   DEBUG   2022-11-29 15:01:13.006   TM1.HttpResponse   200 OK
12116   [36]   DEBUG   2022-11-29 15:01:13.006   TM1.HttpResponseBody   {"@odata.context":"../$metadata#Rowsets('aJON6jwUAICsAgAg')/Rows","value":

    2. Download CSV doesn't work from pop-up drill window

    CSV download doesn't always work when trying to save drill output for above mentioned scenario. There is no visible action in PAfE, no spinning wheel nor frozen excel. In session monitor I could see GET RowSet request and there are also records in tm1server.log but nothing is downloaded. Strange thing is that the same request is executed twice. Maybe one of a hundred of attempts was successful. After clicking on download button I can see some CPU utilization. When it is "done", in PAfE logs there are two same records about this action.

    Tm1server.log:

    5088   []   DEBUG   2022-11-29 15:11:46.027   TM1.HttpRequest   GET /api/v1/Rowsets('aJON6jwUAICsAgAg')/Rows?$skip=0&$top=8192
2022-11-29 15:19:36 GMT  Shared memory reader PID 8004 started, for shmem "tm1s.exe-11416_16"
5088   [36]   DEBUG   2022-11-29 15:12:52.349   TM1.HttpResponse   200 OK
5088   [36]   DEBUG   2022-11-29 15:12:52.349   TM1.HttpResponseBody   {"@odata.context":"../$metadata#Rowsets('aJON6jwUAICsAgAg')/Rows","value":
5088   [36]   DEBUG   2022-11-29 15:12:52.359   TM1.HttpRequest   GET /api/v1/Rowsets('aJON6jwUAICsAgAg')/Rows?$skip=0&$top=8192
5088   [36]   DEBUG   2022-11-29 15:13:41.308   TM1.HttpResponse   200 OK
5088   [36]   DEBUG   2022-11-29 15:13:41.308   TM1.HttpResponseBody   {"@odata.context":"../$metadata#Rowsets('aJON6jwUAICsAgAg')/Rows","value":


    PAfE log:

    { 
 "Event" : "Exception" , 
 "Method" : "ScriptError(Object message, Object file, Object lineNumber, Object col)" , 
 "Class" : "Browser" , 
 "Namespace" : "Cognos.Office.Framework.UI" , 
 "Assembly" : "Framework.UI" , 
 "Thread" : 1 , 
 "Background" : true , 
 "Pool" : false , 
 "Domain" : "FullTrustSandbox(Excel-DNA: D:\\Apps\\TM1Express_x64\\PAforExcel.xll)" , 
 "Timestamp" : "2022/11/29 16:08:37.873" , 
 "Type" : "ScriptException" , 
 "Message" : "Exception of type 'Cognos.Office.Framework.UI.ScriptException' was thrown.\r\nMessage: Object doesn't support property or method '_onError'\r\nFile: https://server/prism/harmony/paw-url-api/js/com/ibm/pa/tm1/api/url/drillthrough/Controller.js\r\nLine: 122\r\nColumn: 9\r\nHostUrl: https://server/prism/harmony/paw-url-api/UrlApi.jsp\r\n" , 
 "Catch" : [ 
 ] }


    Thank you for any tips or suggestions. 



    ------------------------------
    Matej Soltys
    ------------------------------

    #PlanningAnalyticswithWatson


  • 2.  RE: Strange behavior of drill through in PAfE

    Posted Wed November 30, 2022 08:55 AM
    I was able to find solution for 2nd issue after I checked PAW logs. There is ProxyTimeoutSeconds config parameter for PAW, default is 120 seconds and in that scenario it took longer. Once I set it higher, I am able to download CSV file. But even though it is quite slow and the same GET request is executed twice as mentioned above. 

    ------------------------------
    Matej Soltys
    ------------------------------

    Original Message