Jumping back into Powerpivot


There can’t be a better poster child for professionals working in BI than Powerpivot – I used to think that Excel’s ability to use pivot tables linked to SSAS databases was the tipping point for ubiquitous BI – but that title now has to belong to Powerpivot.  This is the tool that will be bringing BI to the masses.

In my last blog I talked about how I had let work and life in general side track me and take me away from BI these past 2 years but now am re-focusing on this exciting field.

I spent some time this weekend re-acquainting myself with PowerPivot which I had played with when first released (back when it was known as Gemini).  This time though, rather than just jumping in with no regards to following in the footsteps of others, I chose a series of 3 videos made of Rob Collie’s Deepdive into Powerpivot talk made during the Swedish SQL User Group back in Jan. 2010 to get me properly re-introduced to Powerpivot.


Rob Collie was formerly the Senior Program Manager of the Microsoft Powerpivot team and now CTO of Pivotstream LLC and along with Kasper de Jonge, the pros behind the PowerPivotPro site (http://powerpivotpro.com/about/ )

The 3 videos can be found here

These videos were a great use of an hour or so of a rainy Saturday afternoon as Rob did a great job diving into Powerpivot and I highly recommend these videos.

Things I learned from watching these videos

  • Extraordinary opportunities for Mashups – this has become far easier than I originally thought, previously I had seen Rob’s example project which had mashed together sales data and weather data but now after watching the video and hearing him talk about how he acquired the data and linked the 2 datasets together, it clicks – now when I see different datasets I find myself already thinking of the joiner field that could be used to mash them up and the actionable data that a mashup could reveal. 
  • Crazy-good memory compression offered by the Vertipaq engine inside of Powerpivot  – ~15 times compression so with the 2 GB maximum Powerpivot file size (a Sharepoint limitation), that means a single Powerpivot file may contain 30-40 GB of actual data
  • Composition of an XSLX excel file – I had first seen this mentioned in the PowerPivotTwins blog post   http://powerpivottwins.com/2009/11/07/for-excel-powerpivot-the-database-is-in-the-workbook/  – a screenshot from their blog shown below does a great job displaying what Rob Collie was explaining in the video


In fact as Rob tells us in the video, Item1.data is actually a SSAS .ABF-formatted file – cool.

  • The importance of learning (and the power of) the CALCULATE function – I was never a Excel pro – Excel was simply a transport media of data for me until Excel Pivot tables came around and then I could connect them to datamarts – but now its obvious that to unlock the power of Powerpivot – the roads pass thru CALCULATE(). 
  • Finally – Rob’s discussion on Cube Formulas really made sense and gave me a great base to start learning from – and being an avid fantasy football player, I appreciated the examples revolving around fantasy football stats Smile

All in all, a great intro – now it’s time for me to revisit using Powerpivot on some of my companies data and show off what we can do with this tool

Next up

A few months back I had bought the highly recommended Powerpivot book – “PowerPivot for Excel 2010 – Give Your Data Meaning” for my Kindle 3 but had not finished it yet – My goal is to get this book finished during the next 2 weeks.  I really like it so far but I should not have bought the Kindle version – despite my admiration of what I can do on my Kindle 3 and how much I love reading on it – I have learned that good quality geek/reference books are best left in paper despite my wish to carry everything with me on the Kindle – I just miss the ability to jump around a reference source such as this book as well as the ability to better view images/diagrams so I know the paper version will be on my bookshelf soon.



Have a good week and if anyone has any great Powerpivot links or resources that will help educate me and help me maximize the power of Powerpivot – please shoot them my way as I always appreciate those and I promise to share the results.

And great videos @powerpivotpro – enjoyed them and learned alot.



About bradosterloo

.NET Software Developer working for Innovative Systems, LLC in Mitchell, SD
This entry was posted in BI, Software Development and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s