The Great PowerPivot FAQ

My favorite comment of the new year 🙂

1) Data volumes. PowerPivot is not limited to 1M rows like Excel. And yes, Excel’s pivotcache can handle more than 1M rows, but if you want to add custom calc columns, you have to drop the data into a sheet, which caps you at 1M. PowerPivot can handle 100 times that. And even better, PowerPivot has incredible compression, so its files are a lot smaller than regular Excel files at the same data volume.

2) Relationships and multi-table pivots. Excel can only pivot one table at a time. If you want to include another table of source data in the pivot, you have to VLOOKUP each column you want from table 2 into table 1. PowerPivot, you make like 3 clicks and now EVERY column in table 2 is available in your pivot field list.

3) Time series calculations in pivots. PowerPivot adds a calc engine capability to pivots that dwarfs the “custom item/custom field” capability that Excel offers. Wanna do a “Year over Year growth” value? A running total? A comparison with the previous period? There are like 40 functions for handling time, alone. This is actually more than 1 item, then, but I’ll count it as one 🙂

4) Custom ratio calcs in pivots. Ever wanted to do a custom % of total calc in a pivot that compared the current cell to the grand total? Yep, you can do something like that today, but PowerPivot gives you total control over which fields get set to grand total and which get left in the current cell context. You can even do fixed comparisons like “Growth since 2007”

5) The ability to publish to a server that automatically refreshes your data every night. That same server gives you a rich report gallery with auto-generated thumbnails of your report sheets, and a usage dashboard that lets you see who is using what reports and how often.

Not to mention how much easier it is to design the report itself in PowerPivot. Multiple pivots all laid out nicely with regard to each other, all sharing the same “slicer” graphical filters. This is simply a question of saving your time however, and can technically be done without PowerPivot.

1) Yes, Sam. I know. I’ve worked on the Excel team for years. The data does get pulled into the file, as a PivotCache. That’s all I meant. But you can’t add calculated columns to a PivotCache. And Excel users like to add calc columns. Apparently you do all of your work in SQL.

2) If you are able to do this in SQL today, good for you. But most people can’t.

3) Again, if you can handle time series calcs in SQL today, excellent. Keep doing it. This product is aimed at Excel users – and only a small fraction of those know SQL.

4) Show me how you can do my “Average sales per day” calc field in Excel 2010 without resorting to SQL. You can’t. There are many, many calcs you cannot do in plain Excel that can be done in PowerPivot. You should dig into and see.

5) If your intent is to share your workbook with others, there is no way to autorefresh what everyone sees.

6) – 7) I counted the clicks one time to create 4 charts laid out nicely that all shared 3 slicers that were laid out nicely as well. It was on the order of 100 clicks. Same thing in PowerPivot is less than 10 clicks.

8) I’ll dig up some examples of compression. Keep in mind that it’s not just on-disk compression. It’s in-RAM compression as well. Excel gets good file compression but completely uncompresses on file open. PowerPivot partly uncompresses into RAM (like 2x) but the majority of compression is preserved in RAM (sometimes we get 15x here).

Sam, I detect in you that you might simply be determined to disagree. You are bordering on impolite. I am here to answer your questions, but this is my personal site that I pay for and maintain. This is not an official MS site. I’m happy to take some flack as long as both sides remain respectful and truly try to understand what the other is saying. So, please decide which one it is. I have not banned anyone yet, but I am here to be helpful, not to provide a public forum for geeks to fight with each other. Make sense?