Faster PivotTables in Excel 2016
16 July 2016
PivotTables are now faster for those working in Excel 2016 with connections to Online Analytical Processing (OLAP) servers, either directly to an OLAP server or through a collection of data tables with relationships - known as a Data Model - in Excel. The recent Microsoft update contained several query and cache improvements:
- Improved query efficiency: Excel will query for subtotals only if they are needed to render the PivotTable results. This means less of a wait for the OLAP server to finish processing the query and for the results to transfer over your network connection
- Reduced the number of queries required: 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 actually 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.
For more information on these improvements, which versions of Excel these updates apply to and how to access them, check out the Microsoft link here. For more information on PivotTables, check out our Thought article >here or drop us a line at firstname.lastname@example.org.