I have a Pivot Table in Quicksight which has,: Display Setting:-
Layout - Hierarchical
Values - Columns Fields
Rows - 9 dimensions with collapse feature enabled. The visual is collapsed at the highest granularity level., When we drill down, we will be able to see the following dimensions.
Values: 13 measures in values with subtotals enabled. The measured contains some % calculations. all the calculations are doing at dataset level to reduce any performance issue.
Here at the highest granularity level we have around 10 -11 Rows. But its taking more than 5 to 10 mins to load all the rows here. Could you please help us to identify how can we reduce this performance issue since impacting badly in user experience. We also have duplicates of this same report which have only 5 columns for measures and 5 levels for rows hierarchy, and that report is loading quickly. But here we cannot eliminate any of the metrics . How can we resolve this issue.
Pivot tables generally take longer to load than other visuals, especially if you add a lot of fields from your dataset, include calculated fields, and have conditional formatting. Even while using SPICE, pivot tables with lots fields, especially in the VALUE field well, will load slowly.
I know that you said you can not make changes, but ultimately reducing or breaking up the pivot table would be the biggest improvement on speed. Also, applying filters to reduce the amount of data loaded when the dashboard is first opened would help as well.
