Analyze in excel - refresh take a long time

[amanjhanwar_02]

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.

[v-stephen-msft · Accepted solution]

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.

[amanjhanwar_02]

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]

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]

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.