Planning Analytics

 View Only
Expand all | Collapse all

Report refresh in PAfE takes a long time

  • 1.  Report refresh in PAfE takes a long time

    Posted Tue November 08, 2022 06:53 PM

    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


  • 2.  RE: Report refresh in PAfE takes a long time

    IBM Champion
    Posted Wed November 09, 2022 01:00 AM
    Does your report have volatile functions? This caught me early on with PAfE.

    https://learn.microsoft.com/en-us/office/client-developer/excel/excel-recalculation

    ------------------------------
    George Tonkin
    Business Partner
    MCI Consultants
    Johannesburg
    ------------------------------



  • 3.  RE: Report refresh in PAfE takes a long time

    Posted Wed November 09, 2022 12:22 PM
    Hi George,

    Thank you very much for your response and this great tip, and also the link to Microsoft help site. I shall do some testing based on this help document.

    There are no highly volatile functions like dates, but the file has links to a small parameter sheet for selecting year, period and reporting unit. Also the file pulls data for multiple years and months. There are only about 600 lines, and we have had no issues refreshing much larger files designed in a similar way. However, I'll take a closer look on the formulas based on your suggestion.

    Thank you again for your help.
    Vincent

    ------------------------------
    Vincent George
    ------------------------------



  • 4.  RE: Report refresh in PAfE takes a long time

    Posted Thu November 10, 2022 02:22 AM
    Hi Vincent,

    Recently i observed few pafe reports taking longer or not refreshing. while they worked fine on perspective. Upon enabling debug logs i found out there were elements in the reports which referenced to values not in dimensions. eg some versions which are now deleted. while perspective would just skip over and show the value of cells without issues, pafe is more sensitive. something you may want to try.

    thanks
    Saurabh

    ------------------------------
    Saurabh Dwivedi
    ------------------------------



  • 5.  RE: Report refresh in PAfE takes a long time

    Posted Mon November 14, 2022 05:25 PM

    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
    ------------------------------



  • 6.  RE: Report refresh in PAfE takes a long time

    Posted Tue November 15, 2022 04:25 PM

    Hi Saurabh,

    Thank you for your response. Yes, we are aware of the sensitive nature of PAfE, and this report has no invalid elements. We'll do some further testing.

    Thank you.

    Vincent



    ------------------------------
    Vincent George
    ------------------------------



  • 7.  RE: Report refresh in PAfE takes a long time

    Posted Tue November 15, 2022 04:39 PM

    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
    ------------------------------



  • 8.  RE: Report refresh in PAfE takes a long time

    Posted Thu November 24, 2022 09:29 AM

    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
    ------------------------------



  • 9.  RE: Report refresh in PAfE takes a long time

    Posted Mon November 28, 2022 11:50 AM
    A few things that would be worth checking, from easiest to most complicated. Now that you know the query response is taking the longest you need to figure out what the real root cause is.
    • Does the MDX query include NON EMPTY statements so that empty cells are not returned?
    • Does your Perspectives report return 37,000 cells of data, is that volume comparable across add-ins?
    • Does the query return all leaf level data, or does it contain consolidations? (MTQ only works for consolidations)
    • Does the query seem "reasonable", some items that might indicate it is a query issue
      • Large numbers of cross joins
      • Repeated MDX operations like DrillUp(DrillDown(DrillUp(....
      • missing Non Empty Statements
      • empty WHERE clauses
    • Enable some query enhancements in PAfE that might speed things up ( https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=started-manually-enabling-features-in-tm1featuresjson-file )
      • "r50_EnableMDXCleanupUtility" : true,
      • "r52IF1_DisableQRStargateOpt" : true,
      • "r60_UseQuerySharding" : true,
      • "r41_EnableConstrainedCalcScope": true
    Hope these help point you in the right direction.

    ------------------------------
    Ryan Clapp
    ------------------------------



  • 10.  RE: Report refresh in PAfE takes a long time

    Posted Mon November 28, 2022 12:35 PM

    Hiyo,


    a few remarks on the flags Ryan mentioned

    r41_EnableConstrainedCalcScope is superseded by the r56 flag which better aligns with Excel operations and offers range constraint for suitable gestures (even tighter operational envelope)

    i advise against setting r52IF1_DisableQRStargateOpt, without a deeper understanding.

    the other two are both valid (depending on version and such) but apply to Exploration Reports and Quick Reports respectively and so may not be especially topical here. 

    --

    It'd be helpful to see the log material you gathered to advise further/more specifically. to that end, I'd suggest opening a support case with that material. A 40 second query time is a promising lead, but doesn't account for the 5-10 minutes you mentioned earlier.



    ------------------------------
    Ted Phillips
    IBM
    ------------------------------



  • 11.  RE: Report refresh in PAfE takes a long time

    Posted Tue November 29, 2022 10:48 AM
    Thanks for the updated feature flags Ted.

    ------------------------------
    Ryan Clapp
    ------------------------------



  • 12.  RE: Report refresh in PAfE takes a long time

    Posted Thu December 01, 2022 05:39 AM
    Edited by System Fri January 20, 2023 04:22 PM
    Dear Ryan, Ted, 
    thank you for your response. Let me answer your questions and provide more details. 

    Our report is pure DBRW report and quite static. I use the same report for comparison in Perspectives and also with same parameters. In report only variable reference is Year and Country that can be selected. Report contains 96 columns - 8 years with 12 months and additional references to different Versions. There are about 400 rows with combination of Account, Billing Type, Product and so on. Nothing really special and no specific excel formulas or functions. Generated MDX looks fine from my point of view - correct where clause for title dimensions, just one cube and no joins or anything else. Original MDX contains thousands of selected cells and is quite huge. Report contains consolidations and also calculated cells.  

    I tried your suggestions for query enhancements in PAfE but response time is always the same, no improvements. 

    { "MDX" : "EnableSkipStargate : SELECT 
    	{
    	([Year],[Period],[Version],[Account],another dims),
    	([Year],[Period],[Version],[Account],another dims),
    	......,
    	} DIMENSION PROPERTIES MEMBER_NAME ON 0 
    	FROM [cube] 
    	WHERE ( [Source],[OrgUnit],[Currency], another dims )  CELL PROPERTIES CELL_ORDINAL, VALUE"
    }​


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



  • 13.  RE: Report refresh in PAfE takes a long time

    Posted Thu December 01, 2022 10:00 AM
    That MDX is really interesting. 2 things stand out to me
    1. It only uses 1 axis to represent the data rather than 2+. This sort of makes sense when dealing with DBRWs but is interesting anyway,
    2. The MDX query explicitly tells the server to not use a stargate when querying the data. Typically this would lead to a much slower query, but not in all cases.
    Can you/have you tried to run the same query with the EnableSkipStargate removed?


    ------------------------------
    Ryan Clapp
    ------------------------------



  • 14.  RE: Report refresh in PAfE takes a long time

    Posted Thu December 01, 2022 03:01 PM
    Edited by System Fri January 20, 2023 04:45 PM
    Ryan,

    I've seen EnableSkipStargate in nearly all PAFE queries.

    I've asked about it and was told there is a good reason it's there but I don't remember why.

    Perhaps @Hubert Heijkers and @Ted Phillips can provide some further background on how tm1 server "reacts" to EnableSkipStargate and why PAFE asks for that setting, often.

    ------------------------------
    Adam
    ------------------------------



  • 15.  RE: Report refresh in PAfE takes a long time

    Posted Mon November 28, 2022 07:26 PM
    Edited by System Fri January 20, 2023 04:48 PM
    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
    ------------------------------



  • 16.  RE: Report refresh in PAfE takes a long time

    Posted Wed November 30, 2022 11:21 AM
    Hi Matej and Vincent,

    1. I'm curious which version of PAFE are you testing ?
      I have observed significant differences in performance between versions 2.0.70.4, 2.0.77.3, and higher (we are on 2.0.77.3 at the moment)  
    2. I'm assuming your Custom Report is using properly constructed VIEW formulas
    3. Can you try to tweak CellsetChunkSize parameter on the client side: https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=settings-in-cognosofficereportingsettingsxml-file
    4. Can you try to tweak VMT, VMM settings on the cubes used by report


    ------------------------------
    -----------------
    Mariusz Perz
    ------------------------------



  • 17.  RE: Report refresh in PAfE takes a long time

    Posted Wed November 30, 2022 02:42 PM
    Dear Ted, Ryan, Adam and Mariusz,

    Thank you very much for all your help. We shall do some testing based on your suggestions and see if we can find some solution.

    Thank you.
    Vincent

    ------------------------------
    Vincent George
    ------------------------------



  • 18.  RE: Report refresh in PAfE takes a long time

    Posted Thu December 01, 2022 11:04 AM
    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/$count
    2268   [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 Created
    2268   [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/$count
    524   [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 Created
    524   [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
    ------------------------------



  • 19.  RE: Report refresh in PAfE takes a long time

    Posted Thu December 01, 2022 12:47 PM

    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
    ------------------------------



  • 20.  RE: Report refresh in PAfE takes a long time

    Posted Fri December 02, 2022 07:12 AM
    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:
    • in PAfE logs I can identify SendRequest to execute MDX - POST request with path and full MDX - "/tm1/server/api/v1/ExecuteMDX?$expand=Cells/$count" 
    • at the same time in tm1server.log there is HttpRequest for that POST request - /api/v1/ExecuteMDX?$expand=Cells/$count
    • after about 50s in tm1server.log there is HttpRequestBody with MDX
    • after another 15s in tm1server.log there is HttpResponse 201 Created and HttpResponseBody with CellSet ID
    • after about 1s in tm1server.log there is HttpRequest GET /api/v1/Cellsets
    • after about 1s in tm1server.log there HttpResponse 200 OK and HttpResponseBody  with expanded CellSet 
    • after another 2s in PAfE logs there is result with expanded CellSet
    • report is refreshed 

    When I send request via Postman;
    • in tm1server.log there is HttpRequest POST /api/v1/ExecuteMDX?$expand=Cells/$count
    • after about 65s there is HttpRequestBody with MDX
    • almost immediately there is HttpResponse 201 Created and HttpResponseBody with CellSet ID 

    So difference between refreshing report in PAfE and sending request via Postman is:
    • POSTing MDX is faster via PAfE, this is as I understand from your post that PA is using some compression
    • 15s difference to get CellSet ID - this is strange and I don't understand why it is like that 


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



  • 21.  RE: Report refresh in PAfE takes a long time

    Posted Tue December 06, 2022 10:04 AM
    What version of PAfE are you running?

    We are having some performance issues with v80 in one environment; currently reviewing with IBM to come up with root cause. We have temporarily rolled our users back to a prior version.

    ------------------------------
    Matthew Berson
    ------------------------------



  • 22.  RE: Report refresh in PAfE takes a long time

    Posted Tue December 06, 2022 10:52 AM
    We are using PAW and PAfE 2.0.74

    ------------------------------
    Vincent George
    ------------------------------