We can continue here, I believe there are already valuable informations in this thread for this community. For specific details and for sharing full logs I opened case today.
So far what I can identify from logs is this sequence of actions when I refresh report in PAfE:
So difference between refreshing report in PAfE and sending request via Postman is:
Original Message:
Sent: Thu December 01, 2022 12:46 PM
From: Ted Phillips
Subject: Report refresh in PAfE takes a long time
I'll reiterate that an 'all' level log bundle from PA for Excel for the relevant gesture sequence you're concerned about is likely the most effective way to assess this concern. I'm happy to have that discourse publicly (particularly for this group which includes many sophisticated users), but if the necessary details trend to the specific - it may become sensitive.
in general terms:
review the HAR produced by PA for Excel to understand the timeliness of the api traffic, be aware of ASYNC activity and treat that as a overall elapsed time. the 'gaps' between the api traffic bursts can be generally be considered to be 'time spent processing in excel' (where no other further progress is possible). review the proportion of 'time spent in api' vs 'time spent in excel' (for an atypical performance concern it's likely an edge case in one category or the other). if excel-time is the concern, consult the application .json log file which tracks the activity happening in an excel-facing way; if api-time is the concern, can drill into the specifics details of or patterns of the api requests that are accounting for disproportionate time.
lastly, regarding the above post, be careful that an MDX replay for outside assessment is matching some of the niceties of transport that PA will leverage, such as request/response HTTP compression; otherwise your numbers may be skewed.
------------------------------
Ted Phillips
IBM
Original Message:
Sent: Thu December 01, 2022 11:03 AM
From: Matej Soltys
Subject: Report refresh in PAfE takes a long time
Dear Adam,
thank you for sharing your ideas. I think you pointed to the right direction. I sent the same REST query via Postman once from my local computer and then directly from the server. From my computer it took about 55s and from the server 15s. I also enabled http loggers and below you can see that posting request body took long time. I tried to save body into txt file and it has 11.3MB, so MDX query is really quite huge.
Postman from local computer:
2268 [27] DEBUG 2022-12-01 14:27:27.102 TM1.HttpRequest POST /api/v1/ExecuteMDX?$expand=Cells/$count2268 [27] DEBUG 2022-12-01 14:28:23.009 TM1.HttpRequestBody { "MDX": "EnableSkipStargate : SELECT ...2268 [27] DEBUG 2022-12-01 14:28:23.010 TM1.HttpResponse 201 Created2268 [27] DEBUG 2022-12-01 14:28:23.010 TM1.HttpResponseBody {"@odata.context":"$metadata#Cellsets(Cells)/$entity","ID":"oCOc8-kZAIAFAAAg","Cells@odata.count":37824}
Postman from server:
524 [23] DEBUG 2022-12-01 14:32:59.697 TM1.HttpRequest POST /api/v1/ExecuteMDX?$expand=Cells/$count524 [23] DEBUG 2022-12-01 14:33:13.676 TM1.HttpRequestBody { "MDX" : "EnableSkipStargate : SELECT ...524 [23] DEBUG 2022-12-01 14:33:13.676 TM1.HttpResponse 201 Created524 [23] DEBUG 2022-12-01 14:33:13.676 TM1.HttpResponseBody {"@odata.context":"$metadata#Cellsets(Cells)/$entity","ID":"oCOc8-kZAIAHAAAg","Cells@odata.count":37824}
------------------------------
Matej Soltys
Original Message:
Sent: Mon November 28, 2022 07:26 PM
From: Adam Havas
Subject: Report refresh in PAfE takes a long time
Dear Matej and Vincent,
I would like to preface that you should definitively go through what Ryan and Ted are recommending because they are truly experts in this topic.
I wanted to offer some thoughts on something I took away from your post... You are saying:
After that I tried to check MDX query that was created by PAfE and I executed it in Postman. It took about 40s to get the result. MDX returned about 37000 cells. So it looks like that executing MDX via REST api is slow.
You're running a REST API call and it's taking 40 seconds to get the results back?
Is this kind of behavior and speed normal?
Just for testing as a part of trying to help you, I constructed an MDX view that results in 36 columns by 29,357 rows, so a total of 1,056,852 cells. In that MDX query, I made sure to strip out all the dimension properties / cell properties that PAFE typically adds to get a nice clean simple MDX statement. I also specified NON EMPTY on both columns and rows. I also specified only leaves in both columns and rows.
When I ran the API call, I immediately switched to (the tm1 session monitor / thread viewer) to see how long the REST API call takes to "POST" on the server side. Took 10 seconds (server side).
On the client side I was running Fiddler, and the query there also took about 15 seconds to return the result (client side). -> I haven't gotten into parsing the results, so don't read too much into this (yet).
When you run the MDX query through Postman, on the server side does it resolve immediately (1-2 seconds) --or-- is it also taking 40s+? That would help to know.
------------------------------
Adam
Original Message:
Sent: Thu November 24, 2022 09:28 AM
From: Matej Soltys
Subject: Report refresh in PAfE takes a long time
Hi All,
I am colleague of Vincent and I did some testing in PAfE with enabled higher level of logging. For comparison I run the same report in Perspectives and it is refreshed in about 5s, in PAfE it takes about 50s.
From logs I was able to identify:
- Run worksheet method with timestamp
- After about 4s there is visible constructed MDX query and Execute MDX and SendRequest methods
- Returned results are visible in the log file after about 64s. When running refresh with higher logging it is slower.
- Between sending request and results there are only methods like RetrieveSSOInfo, TM1ActiveProcess and so on. Nothing really suspicious.
After that I tried to check MDX query that was created by PAfE and I executed it in Postman. It took about 40s to get the result. MDX returned about 37000 cells. So it looks like that executing MDX via REST api is slow.
Is this kind of behavior and speed normal? Do you have any suggestions for making it faster? Thanks.
------------------------------
Matej Soltys
Original Message:
Sent: Tue November 15, 2022 04:39 PM
From: Vincent George
Subject: Report refresh in PAfE takes a long time
Hi Ted,
Thank you for your response.
Since the report is a template that would be used by several users in multiple countries (whose data is sitting in different cubes), creating such a report using Exploration or Quick is not easily doable. Custom report is the most suitable in this scenario.
We shall try to do the logging you had suggested and see if we get anywhere with the findings. While these experiments can take some time to find the root cause, it is much easier to run the report in ISB that will do the trick under 10 seconds ;)
Thank you.
Vincent
------------------------------
Vincent George
Original Message:
Sent: Mon November 14, 2022 05:24 PM
From: Ted Phillips
Subject: Report refresh in PAfE takes a long time
Exploration Reports is most comparable to ISB, I'm curious about the performance there.
Putting that aside, there's a variety of techniques to profile workbook performance. many good ideas already in the thread here, but my addition would be to run the application at 'all' level logging and the log+har material can be reviewed to make it evident where the delay is happening or if it's evenly spread across the workload.
I would be fairly optimistic based on your remarks that there a likely some low-hanging improvements to be had via configuration. Interested to hear more.
------------------------------
Ted Phillips
IBM
Original Message:
Sent: Tue November 08, 2022 04:42 PM
From: Vincent George
Subject: Report refresh in PAfE takes a long time
Hi,
One of our small report files (817 KB), with DBRW formulas, takes 5 to 10 minutes to refresh in PAfE. With ISB, it refreshes within 10 seconds. Can you please suggest any tips/ideas for speeding refresh time in PAfE.
Thank you.
------------------------------
Vincent George
------------------------------
#PlanningAnalyticswithWatson