What is Power Pivot?
The two most important ingredients in your data revolution: people, and Power Pivot!
Without doubt, the most crucial ingredients in revolutionizing your relationship with data (and your organization’s relationship with data) are fundamentally human: curiosity, passion, and desire for improvement. If you are reading this, chances are good that you possess those qualities, and you also may know of others within your organization who share those traits. So you are well along your way already.
Power Pivot, then, is best viewed as the “missing link” in your relationship with data.
In our experience over the last five years, no other technology comes close to its game-changing capabilities, precisely because of the way in which it empowers human beings to realize their full potential, and to fully harness those three qualities listed above.
Depending on your background, there are a few “best” ways to understand what Power Pivot is, its role, and what it can do for you.
Power Pivot is all of these things, which we will briefly explain in more detail in the sections below:
- The best new feature of Excel in 20 years
- The brain (and the heart!) of Microsoft’s Power BI Suite
- A Much More Agile & Cost Effective Approach to Business Intelligence
1. The best new feature of Excel in 20 years
We agree wholeheartedly with this statement, but cannot claim credit for coining it. MrExcel himself, Bill Jelen, proclaimed this upon Power Pivot’s release in 2010:
That’s right, it’s part of Excel, and is built by Microsoft itself.
Power Pivot dramatically extends the powers of “normal” Excel, and more specifically, the powers of people who use it. Anyone who regularly uses PivotTables and/or VLOOKUP is very much the “target audience” for Power Pivot: those people who are already, ahem, “pivotal” to an org’s data workflow today. Power Pivot imbues those people with data superpowers.
It snaps right into Excel as if it had been there from the beginning:
Click here for instructions on how to get Power Pivot for yourself.
It EXTENDS the things you already know about Excel, rather than starting from scratch.
Power Pivot is still PivotTables. It’s still formulas.
But it adds a number of important new capabilities that forever turbocharge your ability to turn data into meaningful, actionable insight:
Those three new capabilities are absolutely transformational in practice. Analyses that previously took days in traditional Excel (and Access) are often reduced to less than an hour with Power Pivot, but even more importantly, adjustments to those analyses now take mere seconds! (In the traditional spreadsheet space, an adjustment often took as long to execute as the original analysis itself). In our experience, no analysis or report is ever complete in its first iteration – it is a simple human fact that no one truly knows what they need until they have seen what they asked for, and given the opportunity to ask additional questions.
This ability to adjust on the fly – to new questions and/or actual changes in the business – is something you have to experience in order to fully grasp. It will quite literally change the way your business thinks, at all levels, and in a trustworthy manner.
When paired with the Power BI.com platform (or an “on premises” SharePoint server), Power Pivot workbooks then “light up” with these two additional game-changing benefits:
- Automatic refresh
- Web reach
Power Pivot is now also included in Excel 2016 (in the “Professional Plus” and “Excel Standalone” versions) as well – the new “Data Model” features of pivots in 2016 are really just Power Pivot, and the remaining functionality of the Power Pivot add-In can be enabled with a single checkbox (under Add-Ins).
2. The Brain (and Heart!) of Microsoft’s BI Suite
Everything in Power Pivot revolves around the Data Model. You connect to your data through Power Query, which can be used to help with data discovery, shaping, and import. Data is then loaded and enhanced (via smart formulas and table relationships) to form data models, which you can think of as hyper-flexible “answer machines.” Tailored to your business’s needs, nuances, and goals, these data models bring near-magical clarity to longstanding questions – clarity that you can trust, clarity that persists over time, and clarity that grows with your business.
(In our widespread experience, we’ve seen that Power Pivot’s ability to answer questions also enhances your organization’s ability to ask better questions. Unshackled from the manual tedium of traditional spreadsheets and the institutional inertia of traditional BI, your “question-forming muscles” are put to better use, and quickly get stronger.)
The Excel visualizations (PivotTables, Charts, Graphs, etc…) then connect to your Power Pivot data model(s) as your means of asking questions, and also of seeing the answers. So while Power Pivot can be understood as being “behind the scenes,” it is very much the engine that drives the entire system, and it is essential. It is not a stretch to say that the Power Pivot data is nothing less than extraordinary!
You may have noticed something called Power BI in the diagram as well. The beautiful thing Microsoft did was give you a set of BI tools that works in two products! Both Excel and Power BI Desktop use the SAME architecture. They both use Power Query to connect to and shape data, and both use the same Power Pivot Data Model and formulas to create those hyper-flexible “answer machines”. The only real difference is in their visualizations and online capabilities. More information about Power BI can be found on the “What is Power BI?” page.
3. A Much More Agile & Cost Effective Approach to Business Intelligence
Take a deep breath and consider the following statement: the entire Business Intelligence industry is dysfunctional. BI projects’ already-massive “sticker costs” represent only a fraction of their actual costs. Those same projects are typically estimated to require execution times measured in months, and then tend to run even longer. Perhaps worst of all, the eventual results are underwhelming relative to initial expectations – an impression secretly held by the entire organization, but rarely shared out loud.
Whether you are on the “Business” or “IT” side of the fence, we believe that story sounds familiar to you. But it is not our intent to point fingers or accuse anyone of failure, because that reality is ultimately no one’s fault. Yes, the shortcomings of BI projects do generate friction between Biz and IT, but in our wide experience, everyone involved in BI projects, on both sides, goes into the process in good faith.
The problem is not the people. It’s the tools and methodologies. We no longer have to live in the world pictured here:
Instead, we can now operate in this world:
And the results of these new tools and methodologies are dramatic, to say the least:
With two weeks of Power Pivot work, we implemented an enterprise-wide scorecard project that had been estimated to take 1.5 YEARS using our traditional BI tools, and the quality of results we got is actually BETTER than what we would have gotten from the BI approach. There’s no such thing as a free lunch, but this is awfully close.
Continuous Improvement Champion at a Multi-Billion Dollar Corporation