Microsoft is tweaking its Excel product to bring customers faster PivotTables on OLAP servers. According to a post issued today, July 7, 2016, on the Office Blogs, customers should see improvements to their PivotTables due to some caching improvements, thanks to the Office team.
More to the point, the updates, which are being made to Office 365 subscribers, for now, include the following details on exactly what and how the improvements benefit customers directly.
About the improvements
We have made significant improvements in three major areas while querying OLAP servers.
- Improved query efficiency—Excel will query for subtotals only if they’re required to render the PivotTable results. This means you wait less for the OLAP server to finish processing the query and spend less time waiting for the results to transfer over your network connection.
- Reduced the number of queries—Excel is smarter when refreshing your data. It now uses the LAST_SCHEMA_UPDATE property of a connection to limit schema requests to those times when it has, in fact, changed.
- Smarter caches—When the schema is retrieved, it is now shared across all of the PivotTables on that connection, further reducing the number of queries.
Also, the Office team walks customers through how they can tweak their PivotTables to squeeze even more performance out of the tool while working with larger data environments. Apparently, adjusting or disabling the subtotals and grand totals of pivots will also lead to an additional boost in performance. Visit the Office Blogs to see the easy step-by-step process that helps increase the effective speed of PivotTables in the latest update for Excel 2016.Further reading: Excel 2016, Microsoft, Office 365, Office Blogs, PivotTables