Back
Excel

Faster OLAP PivotTables in Excel 2016

Share on Facebook Share on Twitter Share on Linkedin Share via OneNote Share via Email Print

If you work with connections to OLAP servers, your PivotTables are now faster. The June 2016 update contains query and cache improvements to this powerful feature. You could benefit from these improvements, whether you use PivotTables to answer one-off questions or build complicated workbooks with dozens of PivotTables.

These updates are available to Excel 2016 users as part of an Office 365 subscription. If you are already an Office 365 subscriber, find out how to get these latest updates.

You’ll also need a workbook that has PivotTables connected to either the Data Model or to an OLAP server.

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.

Getting the most from the improvements

No two workbooks are alike; some have received a bigger benefit than others.

It doesn’t matter if they’re connected to a tabular or multi-dimensional model. All of your PivotTables connected to Microsoft SQL Server Analysis Services, third-party OLAP providers or the Power Pivot Data Model will likely give you fresh data, faster.

Additionally, your pivots with subtotals and grand totals disabled, could be much faster to work with. Get insights more quickly when refreshing, expanding, collapsing and drilling into your data. The bigger the pivot you’re working with, the bigger the potential improvement.

To disable subtotals and grand totals, follow these simple steps:

  1. Under the PivotTable Tools section, select the Design tab.
    Faster OLAP PivotTables in Excel 2016 1
  2. Under the Subtotals button, select the Do Not Show Subtotals option.
    Faster OLAP PivotTables in Excel 2016 2
  3. Under the Grand Totals button, select the Off for Rows and Columns option.
    Faster OLAP PivotTables in Excel 2016 3

Try them yourself!

Try these new improvements and see how they can help you improve your analysis performance. Do you have ideas on other improvements that you’d like to see in Excel? Visit the Excel UserVoice and tell us about them.

—Alexander Lahuerta, program manager for the Excel team

Top

Join the conversation

12 comments
  1. I work with SQL Azure connection for excel connecting multiple tables and inside excel re-creating (!) relationships hence getting my pivot tables the way I want – but it takes ages to refresh! At the era of Azure, 3 or even 5 minutes are ages! Is this being considered by you to just get a fresh data when I hit the button refresh? Why I also have to re-create relashionships?

    • Hello Yahya,

      I’m not sure if you’re using SQL DB or SSAS. The performance improvements we’ve made benefits SSAS.

      However, in general, performance depends upon a variety of factors. They generally are:
      1. Complexity of queries. These impact the amount of time the SQL server in Azure spends preparing your data. For example lots of joins or “group by” statements can be very slow. This is also impacted by the layout of the remote server such as size of the data set, and cpu/memory resources, and utilization.

      2. Amount of data being retrieved. This is the amount of data being sent over the wire. For example, large pivots can involve lots of data

      3. Speed of your network connection.

      If you can identify a root cause, then I can get you started in the right direction to address it.

      As for the issues your having with relationships between tables. PowerPivot in Excel, in some cases, can detect relationships . You can find additional information at the link below.
      https://support.office.com/en-us/article/Relationships-between-tables-in-a-Data-Model-533dc2b6-9288-4363-9538-8ea6e469112b#__automatic_detection_and

      Thanks,
      Alexander

  2. Honestly I don’t know how much the speed has improved as I am just now really getting into Power Pivots in Excel 2016, but I can say this – in a query with 5 tables and just over 1,000,000 rows from a SQL server, the latest insider build of Excel absolutely smokes Access 2016 in terms of speed. Access takes over 10 minutes to pull the query, whereas Power Pivot takes maybe a minute or two.

    Very impressive!

  3. Will these changes eventually be available for Office Professional Plus (via WSUS)? If yes, any ETA?

    • I’m interested in the answer to this also.

      • Hi Alexander,

        Yes, we’re using SCCM, but we do not have Office 365. We have the “offline” suite – Office Professional Plus. I was wondering when this update will be available for that suite.

        • Hi Blaž,

          These improvements are currently only available to Office 365 subscribers. However, they be included in the next major release of Office.

          • Thanks Alex, by major release do you mean Office 2018 or a service pack for Office 2016 offline version? Given that the vast majority of folks who would benefit from this patch are enterprise customers I hope it is pushed to us “offline” Office users soon.

      • These improvements won’t be released in a service pack. Instead, they’ll be in Office 2018 or 2019 (or whatever the next major version is called).

  4. Thanks a lot – we have been waiting for this for quite a while.

    Hope you put even more effort into SSAS and excel Pivot tables, because our customers like it a lot and every improvement is highly appreciated.

    Best regards

    Simon

Comments are closed.