Between my Dashboard book for Dynamics GP and my Analytics product I think it’s time to add some regular Excel and dashboard stuff to the blog. So I’m starting a Dynamic Dashboards segment.
Let’s start by talking about size. Excel’s limit is a million rows right? Yes. Excel slows down when building Pivot Tables off of a couple of million rows right? Generally, yes. If you want to use more than either of these limits you must use Power Pivot right? Uh, no. That doesn’t mean you can’t or you shouldn’t, but you don’t have to.
First, you don’t have to bring data into Microsoft Excel to create a pivot table. If it’s not in a worksheet, neither the 1mm row limit nor the ~200k performance threshold apply. If you connect to a data source, like SQL Server, you can choose to create a pivot table report or pivot chart directly, without bringing the data into Microsoft Excel. This is also without adding data to the Data Model, since adding to the Data Model is the same adding to Power Pivot.
What kind of performance you get depends on your machine, but I’ve refreshed an Excel dataset with 3 million rows in about 30 seconds. That’s acceptable for most people. Pivot Tables and charts had a slight lag, but not enough to cause a problem. This also means that you can use Pivot Table groups and formulas.
As with Power Pivot, the important factor is the number of columns. Narrow the data by bringing in the fewest number of columns possible for the analysis. Number of columns is more important than number of rows.
Does Power Pivot offer some distinct advantages over regular pivot tables? Yes. Does DAX crush old style pivot table formulas? Yes. Is Power Pivot always the answer? Given Microsoft’s odd licensing, I have no, not always. You can still do some crazy stuff in Excel without Power Pivot.