, , ,

What is Power Pivot?

So what is Power Pivot?

I think a lot of people arriving at this site for the first time already know quite a bit about what Power Pivot is, but a few people have expressed curiosity:  it would be easier to understand why the site is called PowerPivotPro if we knew what Power Pivot was in the first place. So what is Power Pivot?

There are a lot of good places to read about Power Pivot, so I won’t go super deep here.  Instead I’ll give you a summary in my own words, and then point you elsewhere for the details and the snazzy demos.

First, the (previously missing) sizzle

I realized the first version of this post contained ZERO picture, just a wall of text.  I have higher standards than that.  So quickly, here:

This is the sort of thing that PowerPivot produces.  That’s a screenshot taken straight out of Excel, believe it or not.

OK, now imagine…

  1. That same report, published as an interactive web app, with a single click
  2. The consumers of that web app never needing to know it was produced in Excel
  3. The report itself literally taking less than 5 mins to produce (and they can look much better – this is actually one of my “zero effort invested” samples)
  4. Prepping the data behind that report taking less than 30 minutes
  5. The report being based on your company’s sales data, even up to hundreds of millions of rows’ worth (!), but still very fast
  6. The report automatically refreshing with new data every night, hands free
  7. IT loving this flavor of Excel reporting, rather than worrying about the risks

…and you’ve got the spirit of what Power Pivot can do.

Filtering by Temperature???

I can’t resist pointing out that this report takes a company’s sales data (in this case, from the sample db AdventureWorks) and “mashes that up” with real data I pulled down from the US Meteorological Site.

In about ten minutes’ time, now I had the ability to filter my sales data by the temperature that was recorded on the date of the sale, in the location that the sale was made.

If that doesn’t excite you, perhaps you have not understood it 🙂  Which I understand – it deserves more explanation really.

(Now I’m tempted to dive in and just show everyone that example, it’s so much fun.  I’ll save it for later.  Tomorrow, I’m back to the football stuff.)

“You still haven’t answered the “what” Power Pivot is, Rob!”

Yeah I know.  Short and sweet, Power Pivot is two things:

1) An add-on for Excel 2010 and Excel 2013 that helps you design reports in Excel, based on killer data models that get built as you build the report. Power Pivot is included natively in Excel 2016.

2) An addon for SharePoint 2010 that enables the reports to render interactively (in conjunction with Excel Services), as well as some other nifty server-side capabilities

…what follows, from my original post, could almost be titled “Why is PowerPivot?”

Quick Refresher:  What is Business Intelligence (BI)?

Broadly defined, BI encompasses any data-driven analysis or reporting for business purposes.  If you’re looking at data in an attempt to make better business decisions, or doing something as preparation for looking at data for that purpose, well, that’s BI.

But traditionally, when someone has said “BI,” typically they’ve been referring to those BI activities that are executed by the IT department:  the construction and delivery of standard reports, scorecards, certain flavors of applications, and the plumbing that makes it all possible.  Things that require developers and database pros, in other words.

Of course, the IT department only has so much time and resources.  Of all the needs for digestable data in the company, they maybe have time to address 5%.  (Some people might say less than that).  It’s the most important, most central 5%, but there’s still a lot of unmet need.

What happens to the other 95%?

If IT can’t do it, what happens?  The short answer is that the business units do it themselves, without IT involvement.  There are a number of tools that they use, but by far the most common is Excel.

Excel gets the job done.  A tremendous percentage of the world’s business decisions are brought to you by Excel.  It’s a great tool – flexible, powerful, end-user friendly.  Excel’s even taught in business schools, and it’s sometimes called the “language” of business.

There are, however, some problems that arise.  Here are a few:

  1. Usually, people share their Excel reports and models by simply emailing the files around.  This carries risk, since the data is often sensitive.
  2. When the author of the Excel report updates it (or fixes it!) there’s no good way to make sure everyone is using the latest.
  3. Keeping the report up to date as conditions change is often a lot of work, especially as the number of reports grows over time.
  4. If the report author leaves the company, or is even just out sick on the wrong day, key business processes can grind to a halt.
  5. These reports often get their data through very clever methods that the IT department is unaware of.  So IT often unknowingly breaks them by making a change to backend systems (and then IT often gets blamed for it).

As a result, there has been a longstanding but low-grade tension between IT (who sometimes wish that Excel would just disappear) and the business units’ Excel power users (who sometimes wish that IT would stop complaining).  Neither side is right or wrong really – it’s just a natural, inevitable tension, given the state of things.

PowerPivot magically fixes all of this, right?

Why, of course it does 🙂  OK, it’s not going to fix everything, and the things it does fix, will take some adaptation on the part of both sides of the business.  But PowerPivot does indeed bring a number of things to the table that have never existed before, and that will certainly improve the situation dramatically when properly deployed.  Here are a few:

  1. More powerful tools for the Excel authors.  Things that used to take hours will now take minutes (or seconds), and some things that used to simply be impossible become possible.
  2. A secure and “instant update” method for sharing the reports.  No more sharing the files directly – now you can publish the workbook to SharePoint, where it becomes an interactive web application.  The consumers get all of the benefit, but they never download the file.  Less risk to sensitive data, and when I publish a new version, everyone gets it the next time they visit the site.
  3. Scheduled, automatic report refresh.  You can configure the reports to automatically refresh nightly (or on any other interval) without human intervention.  Less hassle for the publisher, and less risk of an outage.
  4. Transparency for IT.  Since these reports are all stored and executing on centralized servers, IT has the ability to see what apps are out there “in the wild,” which they cannot do today.  They furthermore can see which apps are being used most frequently, who is using them, and inspect them to see what systems they rely on.  Again, less risk of outage.

That list of benefits typically only come with a “real” BI solution.  This is why PowerPivot is often referred to as “Self-Service BI.”

Lastly, it’s crucially important to note that PowerPivot delivers all of those benefits without requiring the business users to adopt some new IT-approved tool.  PowerPivot is merely an extension to Excel, and an extension to paradigms that are already well-known by those users.  …Which is particularly exciting for me, because, hey, now even I can use it 🙂

Rob, you said it was going to be brief!

OK, yeah, it ran a little longer than I wanted.  But there’s a lot to cover here, and I want to make sure everyone has the chance to understand – Excel users, BI pros, and SharePoint pros alike.

As always, questions welcome.

For more info…

A few links here, but you can search on “Microsoft PowerPivot” or “Microsoft Gemini” (the old codename) and find a lot of great stuff.

Official site:  https://powerpivot.com/
PowerPivot Team Blog:  https://blogs.msdn.com/gemini/
Twitter:  powerpivot
Facebook:  https://www.facebook.com/PowerPivot
Donald Farmer’s Video Series: Geminute

Read more on our blog

Get in touch with a P3 team member

  • Hidden
  • Hidden
  • This field is for validation purposes and should be left unchanged.

This field is for validation purposes and should be left unchanged.

Related Content

Cube Functions, the good the bad and how to make them great

I’ve written in the past about using the CUBE functions in Excel.

Read the Blog

Reducing DSO using Power Pivot

One of the many things which amazes me about Power Pivot time

Read the Blog

Power Pivot Cracks Direct Method Cash Flow Conundrum

Many accountants consider that cash flow statements are the most important indicator

Read the Blog

Using Dax To Handle Multiple Parent Hierarchies

A common accounting task is to consolidate the financial results of subsidiary

Read the Blog