THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

SQLBI - Marco Russo

Please visit the same blog at - comments are now disabled here but you can comment on the new blog (all blog posts available here are available there, too).
You can follow me on Twitter: @marcorus

PowerPivot Workbook Size Optimizer #powerpivot #tabular

Microsoft released the Workbook Size Optimizer for Excel, the first version of an Excel add-in for Excel 2013 that inspects the data model and suggest possible optimizations. Fundamentally, it tries to apply the best practices descripted in a white paper I mentioned a few weeks ago, removing useless columns and changing granularity to those that could reduce the overall memory cost of a table.

imageThere are different setup available in the download page, depending on operating system (Windows 7 or Windows 8) and on Office version (32 or 64 bit). Once installed, you have a new tab in the Excel ribbon, called Workbook Size Optimizer, showing a single button that starts a wizard.

I tried to run the optimizer with a workbook where I imported several tables from Adventure Works Data Warehouse sample database. The first page shows a few information about the workbook size and the option of automatic detection or manual choice of rules. The latter is an option you can request also later, so I started with the default.


After a short analysis, I received three smart suggestions (considered the model I have). We might wonder that removing UnitCost is a smart thing, because it could be required in order to perform calculations and rounding the value might be not correct for our analysis.


Since I requested to apply some changes, I have the option of changing which rules to apply. This corresponds to the choice you have if you choose “Let me choose the rules myself” in the first screen of the wizard.


I kept all the rules and after I click Next I had to wait several seconds in order to complete the optimization process. The result shows a few information about the result of the job.


This is a good starting point. Don’t blindly trust any suggestion and try to consider carefully the rules to apply in order to avoid losing important data for your analysis. Moreover, you might have a better knowledge of your data model than a wizard and consider the deletion of many useless columns (for your analysis) that are not identified by the wizard. My article Checklist for Memory Optimizations in PowerPivot and Tabular Models contains several best practices that you can apply to your data model.

Published Tuesday, April 30, 2013 1:50 PM by Marco Russo (SQLBI)
Filed under: , ,



Jeff Elderton said:

Excellent.  Thanks for your continued leadership, Marco.

April 30, 2013 8:57 AM

Landon said:

Hi, I downloaded the optimizer and closed and opened excel 2013, but don't see a new add-in under Options, and don't see a new ribbon component.  How does this add-in get enabled?

December 16, 2014 12:51 PM

Marco Russo (SQLBI) said:

Did you run the setup of the optimizer? It should appear as a separate tab in the ribbon with the name "WORKBOOK SIZE OPTIMIZER"

December 16, 2014 2:02 PM

Bart said:

I see the Tab and the button in the ribbon, but when i click it, nothing happens...

July 9, 2015 3:53 AM

Andrew Simmans said:

Same for me - still does not appear to be working - when I click on the button nothing happens - what a shame

October 31, 2015 4:42 AM

Mauro said:

I installed it and it doesn't appear on the ribbon.

It is on the addins list of installed complements.

April 26, 2017 11:38 AM

Marco Russo (SQLBI) said:

I don't know if MS still supports this addin. I suggest you to take a look at VertiPaq Analyzer:

May 4, 2017 10:57 PM
New Comments to this post are disabled

About Marco Russo (SQLBI)

Marco Russo is a consultant, writer and trainer specialized in Business Intelligence with Microsoft technologies. He runs the SQLBI.COM website, which is dedicated to distribute resources useful for BI developers, like Integration Services components, Analysis Services models, tools, technical information and so on. Marco is certified as MCT, MCDBA, MCSD.NET, MCSA, MCSE+I.

This Blog



Privacy Statement