I have a dataset for transactions that includes criteria and columns for the transaction line. That means multiple rows returned for a single transaction. In the workbook I'd like to display the transaction and a count of lines. A pivot chart seems like the way to do that. Unfortunately the pivot chart takes about 10 minutes to run for the dataset of about 140 lines. That type of performance makes pivots unusable. Any other options or ideas?

Hi Ben, have you tried to turn on Cached response on your dataset? Or do you require real-time data in your case?

Thanks for the reply. I have not tried caching. The dataset itself runs quite quickly (1-2 seconds). It's just the pivot that struggles. Caching the pivot would make sense. Does caching the dataset also impact the pivot that way? FYI the caching feature isn't currently enabled in our account so I'll need to coordinate with the other admins to make that change, probably in sandbox first. Just want to have the right expectations as I do that.

Hi Ben, caching of datasets might definitely have positive effect on pivot execution time, but there are more factors that might impact pivot performance, so my suggestion is let's give it a try and turn on caching for dataset and if the effect is not sufficient, let me know and we can discuss what else we can do to speed it up 😉

Initial testing in sandbox shows much better performance (<5 seconds vs 10-15 minutes). It's surprising to me that caching makes that big of a difference on what seem like small datasets (<1k results).

Hello Ben, so the issue is that when adding new filter to dataset (or pivot?), time needed for displaying results is not any shorter with caching on? How long does it take? Are you still testing on sandbox, or is this already your production account?

I have enabled caching in production. There does seem to be improvement but performance is still not good. Sandbox testing seems to be better than production. Here are the two performance issues I'm seeing. • Pivot charts - execution times were almost 15 minutes without caching, still about 4 minutes with caching. This is for transaction searches with fairly small row counts. • Filter by customer - unable to add a filter as the screen to select field criteria and values does not load (I've let it spin for 40+ minutes). No difference between live and cached. Same behavior on different datasets. Have added filters to other columns successfully.