I am pretty excited about PowerPivot

I spent the last 12 days writing a book about PowerPivot, a new add-in from Microsoft for Excel 2010.

While all of this is still fresh in my mind, I wanted to share some facts about PowerPivot.

1) The client version of PowerPivot is free for anyone with Excel 2010.

2) It is written in VSTO and feels almost like a part of Excel.

3) It won't be on the Office 2010 CD, but it will be a free download.

4) You are not constrained by the 1,048,576 row limit in Excel. While writing the book, I loaded up an 18 million row text file into a grid. I could scroll, sort, filter that data set. If you have the 64-bit version of Excel 2010, you can in theory load up data sets with hundreds of millions of rows.

5) When the workbook is open, the data is stored in memory in a columnwise fashion. Think about a huge column of data. There are usually some columns that contain similar values over and over and over. In my 18 million row data set, I have one column with only 11 unique values. PowerPivot then can almost create rainbow of stripes in that column. When I look for "X", they know that they can filter to this big stripe of similar data. I can't say that I understand exactly what is happening behind the scenes, but I know this: The 18 million row data set took 58MB of hard drive as a text file, and in the Excel workbook, it compressed to 4MB. Microsoft is calling this VertiPaq compression. It allegedly was invented by the PowerPivot team.

6) You can load up multiple tables into PowerPivot. They will attempt to figure out the relationship between the tables or you can explicitly define a relationship in three clicks.

7) When you return to Excel to create a pivot table from the data in PowerPivot, a bizarre version of the PivotTable Field List appears with all columns from all tables in the list. If you choose something from three different tables, those fields are reported in the same pivot table without ever writing a VLOOKUP formula.

8) There is a new function language called DAX (Data Analysis Expressions) with 134 functions. 81 of these are lifted directly from Excel. The new ones are stunning. Some of these functions are for adding formulas to the grid. Other functions are for calculating values in the pivot table. We can FINALLY calculate Count Distinct! We can invent time comparisons like "running 30 days" or "compare to parallel period from N years ago". The CALCULATE function has parameters where you can say to respect the filters applied on this cell in the pivot table, or override the filters. Regular pivot tables let you do "% of Total". DAX will let you do "% of Red Units Sold".

9) You can have 1, 2, or 4 (or more with a little work) pivot tables and/or pivot charts that respect the same filters, but each show something different.

10) Microsoft had a blog post about 4 months ago where they described something new in pivot tables called Named Sets. These allow for asymetric reporting, where you might have 2009|Budget, 2009|Actual, 2010|Budget, 2010|Actual and only want to show 2009|Actual, 2010|Budget. Not supported in regular pivot tables, but you can do it with a named set in Excel 2010. Problem: they don't work with regular Excel data . They only work with OLAP data sets. So...along the way, I discovered that named sets work with PowerPivot data, so I've considered running all of my regular Excel data through PowerPivot just to enable the Named Sets feature.

I hope to come up with more blog posts, but you can see an example of a PowerPivot mashup where I took daily sales data for 3 years and mashed it up by the amount of rainfall and daily high temperature to see if I could find any trends. See that guest blog post here: http://powerpivotpro.com/2010/02/11/jewelry-mashup-for-alpha-geek-challenge/

And, in a gratuitious bit of self promotion, I would love to win the trip to New Orleans, so please consider voting for my entry in the PowerPivot contest here: http://tinyurl.com/yhovp9e

Bill Jelen

Bill,

I stumbled upon this post while trying to figure out the size limits of PowerPivot. I have a text file, delimited by a "|" (pipe), which has 22 columns and a little under 30 millions row. The text file is about 4.5 GB is size.

I started to import the file, and all was going well, until it hit about 18 million records (or what I presume to be 2GB in size).

I have two questions, first how was your text file of 18 million records only 58MB in size?

Secondly, how is this tool any more useful than access, if you are still bound by the 2GB size limitation (as taken from PowerPivot help, see below).

PowerPivot Help -> Section Learn About PowerPivot Capabilities
"...PowerPivot supports files up to 2GB in size and enables you to work with up to 4GB of data in memory..."

I look forward to hearing how your text file was saved.

Thanks,
-Aaron

Nice one Bill I'm really looking forward to laying my hands on your new book. PP is one of those things I have yet to cover in any detail. I still don't have a single client on 2010. I still have a client on 2003 and they are planning an upgrade to 2007.

I went to that promo link by the way and got this:

The promotion you tried to access is either inactive or does not exist.

Click to expand...

Jon von der Heyden said:

I went to that promo link by the way and got this:

Click to expand...

Might be that Bill's post was from February so the contest is probably done and dusted by now.

Sheesh!

Hehe ... got me too.
Note: I'm definitely seeing PowerPivot when I look into my crystal ball for a glimpse of my future - imagine, Excel AND heavy-duty data-crunching. Does it get any better?

Oh I agree. I really must find the time to get to grips with PP, not as a user but as a developer. I foresee many solutions involving PP just as soon as my client make the move to 2010...

Thought I was having deja vu reading Bill's post.

I've recently installed it at home but as it's likely to be a year until I get 2010 at work have done very little beyond opening it and having a quick look round. It didn't help that I had no internet connection for a couple of weeks after I installed so couldn't get at any of the help files.

Dom

Don't get too excited about Office 2010 - it does have a few issues.
Microsoft actually recommends 32-bit Office 2010 on Windows 7 (http://office.microsoft.com/en-us/w...-version-of-microsoft-office-HA010369476.aspx)
and, although that is what we use at work, we have experienced a number of problems when printing and this is believed to be a driver issue with 32-bit Office running on 64-bit Windows 7.
Microsoft Outlook seems to suffer most (perhaps it is the cause?) and it is necessary to stop and restart the Print Spooler as an alternative to rebooting the machine.
I have come across problems with third-party add-ins on 64-bit and also where VBA code developed on 32-bit failed when run on 64-bit.

Regarding the size limits: on page 200 of his book, Pivot Table Data Crunching (Microsoft Excel 2010), Bill says that you are still constrained to a 2Gb file size limit but, because PowerPivot can compress data, it is possible to fit 10 times that amount of data in a PowerPivot file. He also says that if dealing with millions of records, then you want to go with the 64-bit versions of Office and PowerPivot. But see my comments about '64-bit on 64-bit' above.

Further information about PowerPivot limits can be found at:
http://powerpivotgeek.com/2010/08/22/how-much-data-can-i-load-into-powerpivot/

Regarding learning to use PowerPivot: I have Bill's book but found that "Practical PowerPivot & DAX Formulas for Excel 2010" better for practical exercises because it uses Access and Excel data from the Northwind database. Data for all examples is therefore available.

Nice reply, thanks Derek.