Dan English's BI Blog

Welcome to my random thoughts in regards to Business Intelligence, databases, and other technologies

Excel PowerPivot SQL 2012 vs. Excel 2013 Performance Demo

Posted by denglishbi on July 30, 2012

I just wanted to do a blog post to demonstrate a performance situation I ran into last week.  I had created a PowerPivot model in Excel 2013 and it performed very well (quite the understatement, phenomenal) when creating PivotTables and Power View reports.  I then went ahead and recreated the model in Excel 2010 with the SQL Server 2012 PowerPivot add-in.  The model creation went fine, but when it came to trying to replicate the report I ran into a slight snag.  Well I actually couldn’t even complete the report, I ran out of memory on a box with 8GB of memory.image

We decided to upgrade the box and gave it an additional 4GB of memory, but that didn’t change anything, the query still consumed all of the memory and wouldn’t complete.  We decided to test this out and migrate the model to a Tabular SSAS instance on a server with 64GB of RAM.  Well the query for the PivotTable just kept running and we watched the Analysis Services process climb to over 18GB and it was still climbing with no results coming back.  Needless to say I was confused and a bit embarrassed.  I decided to give it a go in Excel 2013 again and once again no issues and the results were very fast (might even say blazing fast).

To demonstrate this I have gone ahead and created a demo of the exact scenario with a very comparative sample so you can get a feel for what I was seeing, just take a look at this video:

Excel PowerPivot Performance Demo from Dan English on Vimeo.

In reaching out to the product team I discovered that in Office 15 there have been some performance improvements when it comes to dealing with filters.  Well we can definitely see that based on my results:)  It turns out that these same performance improvements will be coming eventually to the current version as well, we just need to wait for the release of SP1 for SQL Server 2012.  Some of the improvements seen will also be available to querying the data in Analysis Services as well, both Tabular and Multidimensional!  So just hold on until SP1 is released.  If you want to checkout the performance improvements today have a go with PowerPivot for Excel 2013 (you will not be disappointed).

About these ads

2 Responses to “Excel PowerPivot SQL 2012 vs. Excel 2013 Performance Demo”

  1. Rob said

    Can’t wait for the update – I’ve noticed how filters seem to slow powerpivot down. Hard to push a company to Office 2013, but a sp1 update may be something I can get done…

  2. Alex said

    I’ve done exactly the same wit 64bit of excel, and the memory consumption was – 1500 mb
    on a vm with 10gb of ram.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 89 other followers

%d bloggers like this: