PowerPivot – Part 1 of 4

After the MVP Summit I was motivated to further explore a Microsoft add-in for Excel 2010 called PowerPivot. I don’t know all its capabilities but at the very least it can analyze millions of rows of data. If you are using Excel 2010 you can download the add-in from http://www.microsoft.com/downloads/details.aspx?FamilyID=48a5b47b-8c9c-450f-ab6e-178600a733ca&displaylang=en

Do keep one thing in mind. The Community Technical Preview (CTP) version of the PowerPivot addin that is currently available is incompatible with the currently available version of Office 2010, i.e., the Release Candidate (RC). The problem seems to lie in that the data in the PowerPivot tables are not stored in the Excel file as they should be. So, on reopening the file, there is no database for PP to use! I assume (hope?) Microsoft will fix this incompatibility soon.

A day after returning from the Summit, I ran into a contest organized by Microsoft on creative uses of PowerPivot. I scrambled to put together an entry since the contest closed in 2 days. First, I had to install PowerPivot. Then, I decided to use census data as the source for some kind of analysis. It took me quite a while to figure out how to get that data. Then, I analyzed the data set with PP. Finally, I decided to marry the “%change in population from one census to the next” with a solution I had posted on my web site to conditionally color shapes in an Excel worksheet (http://www.tushar-mehta.com/excel/charts/0301-dashboard-conditional%20shape%20colors.htm).

I will post more detailed reports on my experience with PP and the census data. Part 2 will be about my experience getting the census data. Part 3 will be about the add-in to conditionally color shapes, and Part 4 will be about using PP in a relatively basic manner while integrating the result with the add-in.

For now, here is an image representing the result.


For those curious about the contest (now closed), here is a link: http://wildfireapp.com/website/6/contests/19877/voteable_entries/3165298

Posted in Uncategorized

9 thoughts on “PowerPivot – Part 1 of 4

  1. Dennis, I’ve read some posts at your blog. Most of it is over my head. I could take the time to do some background reading on the topics. Or, I could just leave a comment: “rather poor blog post.”

  2. This won’t work in 2007? Or was built only for 2007? Any ideas? I though it was good blog post. thanks

  3. Kevin, no, it won’t work in 2007. PowerPivot is an Add-in for Excel 2010 only. But even there, it takes some time to get your head around it.

  4. John,

    Yes, I can agree that it would be inappropriate if You made a comment like that on my blog. After all, while I understand the subject perfectly well here You seems to lack all the basic knowledge of .NET.

    Like You I have had the privilege to evaluate the first beta version of Excel 2010 together with this plug-in. I have done my home work and therefore have my own experience of it. What’s Your opinion about it? For me it’s an excellent tool and deserves to be better presented. OK, all statistic power users, like Tushar, will be thrilled about it but it also exist other groups that can use it.

    The main question will be how MSFT will make it available. Of course, If they will ship it with some versions of Office 2010 than it will be of high interest for the major group of Excel user. But if it will be bundled together with SQL Server and/or SharePoint Server than it will be a different case.

    Is there any information available about how it will be shipped?

    Kind regards,

  5. Hi All,

    Dennis my belief is that is that it wont ship with the Excel install like solver but will have to be downloaded form Microsoft. Although this is only infered, and maybe wrong.

  6. Bugs in PowerPivot.
    When will next version beta be available of PowerPivot?
    I have experienced some serious bugs then loading data from a table in a database.

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.