Forum Discussion

amanjhanwar_02's avatar
amanjhanwar_02
New Member
5 years ago
Solved

Analyze in excel - refresh take a long time

Every change in matrix takes 4-5 seconds to come and the same in case I have to replace or remove a set of matrix.

and this will make this beautiful tool so slow, pls suggest a way where refresh time in every change can be minimized.

 

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi amanjhanwar_02 ,

     

    You can refer to this artilce.

    Possible optimizations

    In order to improve the performance of an Excel report based on a PivotTable, consider the following best practices:

    • Only in SQL Server Analysis Services: Reducing the number of measures included in a PivotTable. (No longer required in Power BI and Azure Analysis Services since October 2020).
    • Optimizing the performance of the measures used in a PivotTable.
    • Avoiding any CallbackDataID especially in measures that are referenced multiple times in the same report.

     

    Best Regards,

    Stephen Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    There can be a number of reasons why the connection is slow. Is the PBI report as slow?

    Does it automatically refresh?

     

    4-5 seconds is not a slow refresh if it's done manually, but having real time data refreshing that often is quite resource intensive.

     

     

    • amanjhanwar_02's avatar
      amanjhanwar_02
      New Member
      5 years ago

      Thanks Wrench for immediate responce!

      PBI report refreshed manually and that refresh we do once in a month but in excel when I go and play with different variables in Pivot than for each change in variable takes 4-5 seconds versus in case of normal excel pivot variable change immediately.

      Please help on this.

  • amanjhanwar_02's avatar
    amanjhanwar_02
    New Member
    5 years ago

    Hi All,

     

    In Analyze in excel Pivot user have to to wait 5-10 seconds when they slice and dice the each filters, is there any other way where I can reduce this time or where I can change all slice and dice once and then OLAP query runs at once only.

     

    Pl help on this.

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi amanjhanwar_02 ,

     

    You can refer to this artilce.

    Possible optimizations

    In order to improve the performance of an Excel report based on a PivotTable, consider the following best practices:

    • Only in SQL Server Analysis Services: Reducing the number of measures included in a PivotTable. (No longer required in Power BI and Azure Analysis Services since October 2020).
    • Optimizing the performance of the measures used in a PivotTable.
    • Avoiding any CallbackDataID especially in measures that are referenced multiple times in the same report.

     

    Best Regards,

    Stephen Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.