Guest Post by Seth Strandin
Early in my Power Pivot journey, I found Rob’s “What is PowerPivot?” post. Here’s an excerpt from that post:
1. It’s a Free Extension to Excel 2010 and 2013, Built by Microsoft
Yeah, it’s free, and it’s from Microsoft, as opposed to a third party. It dramatically extends the powers of “normal” Excel, and more importantly, the powers of people who use it: people like me, and probably you too if you are reading this.
It snaps right into Excel as if it had been there from the beginning:
After spending some “quality time” with Power Pivot, I now believe Rob is wrong about that. Here’s what I think that post should say instead:
Power Pivot is NOT just an extension or add-in to Excel, it’s a new animal that only uses Excel as its UI!
Perhaps it’s a subtle distinction, but in many ways I think it’s a crucial one. I will explain.
I’ll start this with a fairy tale.
Once upon a time the richest man in the world, let’s call him Bill (ref: Forbes) called the Ferrari Formula 1 Team and asked Alberto (yeah, couldn’t resist that one) to build him the world’s most awesome performance car. “There is only one restriction”, he said, “You have to fit it into a Pontiac Aztec. This has to be true for all things visible, input devices such as steering wheel and pedals as well as the ribbon, sorry the dashboard, must be Aztec.”
Alberto talked it over with Marco and they decided it could work if they used the OLAP chassis with the DAX adaptive suspension and the SSAS powertrain, so they set to work.
The Italians did a fantastic job and Bill was very happy, but the question the rest of the world must ask is…
“Is this still a Pontiac Aztec?”
I am just starting my journey into the future, i.e. Power Pivot, but every new page I read and every challenge I throw at it I become increasingly convinced that this is a Ferrari and not an Aztec.
A few examples:
Data Modeling – the concept is brilliant for extended and awesome analysis and it has never been possible in Excel before Power Pivot. Bill Jelen has a lesson where he combines sales data with weather service data for staffing planning, wow! (That’s where I got hooked).
Calculated fields – structured references giving us Pivot tables with %-growth YoY, I have never been able do that in the plain vanilla Excel. In Power Pivot it took me less than 10 minutes.
DAX Formulas – CALCULATE, FILTER, SAMEPERIODLASTYEAR and all those other awesome functions give us flexibility that has never been available to mere Excel mortals before.
Cube Formulas – Bringing values out of the data model into a single or series of cells in a regular worksheet without using the Pivot Table. I think “GetPivotData” just retired from the game!
My very personal conclusion is that Power Pivot is using Excel as its interface because Excel was already there and already being used be gazillions, with a stock of a few zillion real Professionals who had gone beyond the mundane and entered the world of Pivot Tables, VLOOKUP, etc.
I am convinced that Power Pivot could have had its own interface, but that would have made it less available and much less used. Now that Power Pivot uses Excel as its interface there were a zillion Pros out there waiting with their tongues hanging out to grab all this awesome power and get airbourne.
I am not a technician, so I can’t see what’s lurking under the surface, but to my untrained eye it seems that Power Pivot works very independently from Excel, only using formatting and such.
A Life Changing Tool
So why engage in semantics? Well, Rob addresses this in chapter 21 of the book where he identifies the hidden enemy – perception. I see Power Pivot as a rocket sled to greater economic rewards and a future on a mountain top with people making pilgrimages to sit at my feet saying things like “Oh great Power Pivot Pro, tells us how…..?”, before paying me LOADS of money for my answers.
To pave my own way forward I will never again refer to Power Pivot as an add-in to Excel. Instead I will treat it as its own entity, the greatest tool on earth (for analysis anyway). I will never admit that it isn’t prohibitively hard to learn, but I will always offer to teach others how to use it and how this knowledge will help them become awesome.
