“All we are saying, is give peace a chance.”

-J. Lennon

In Part One I described how standardized corporate BI bummed me out my first time around, then in Part Two described how PowerPivot recharged my batteries and brought me back into the fold.

This time, my first order of biz is answering the question:

Why shouldn’t all of this self-service make IT really nervous?

Short answer:  it’s all about the server – PowerPivot for SharePoint.

Think of it this way:  most of the “trouble” with Excel as a reporting and BI tool has nothing at all to do with Excel the application.  The trouble, actually, is with Excel files.

    Excel Files are the Problem With Excel BI

When you use PowerPivot for SharePoint, though, Excel Files become PowerPivot Web Applications.  And that makes all the difference in the world.

What do I mean by that?  Let’s step back for just a moment and consider the common complaints about Excel.

Excel as a reporting/BI tool – the three common complaints

1) “We don’t even know these files exist”

Yeah.  Spreadsheets can become awfully important to the business without IT ever hearing about them.

That’s because files are sneaky like that.  They hide in doclibs and shared folders, right next to the static docs produced in Word and PowerPoint.  They flow from person to person in emails.  So IT never hears about them until something goes wrong.

A published web app, by contrast, cannot hide.  It relies on the server to execute it.  And servers don’t hide like files do.  In fact, IT’s help is often required to get the server in the first place.

   PowerPivot Reports Rely on the Server to Execute

Even better, the PowerPivot server provides a usage dashboard that shows all of the PowerPivot reports published to that server, how often they are being used, by who, etc.

2) “Spreadsheets break”

Yes they do that on occasion.  But why?  It’s not like the contents in the file simply go rotten.  Most often, something external changes.  A data source that the spreadsheet relies on gets moved or renamed.  The spreadsheet author responsible for updating it each day is out sick (or leaves the company).  Another business process, policy, or business rule gets updated without consideration of the spreadsheet.

Notice how most of those are an awareness problem.  If IT knew about the spreadsheet in question, they would be much better-prepared to avoid changes that break them.

And the other source of problem – the author is not around to update them with new data – is precisely the reason why the PowerPivot server has an automatic data refresh feature that updates reports on a scheduled basis.  You know, just like all other respectable data-driven web applications.

3) “Spreadsheets get out of date, and out of hand”

This is another drawback to using files as business apps.  You create a spreadsheet, make sure it contains the latest data and logic, then you give it to people…  and immediately lose ALL control over it.  Some examples of why that isn’t fun:

  1. Those people can then modify the spreadsheet in ways that give them incorrect results (usually, this is unintentional, but on occasion is rooted in unethical motives).
  2. Your data and business logic can then walk out the door – those files contain input data and results data that are often quite sensitive.  And even the formulas themselves represent a lot of intellectual property.  All of those can leak once they are packaged in a file.  And again, that can be either accidental or malicious.
  3. Good luck pushing out updates – when you revise an existing spreadsheet, how do you make sure everyone adopts that latest version?  Older versions are sitting in dozens or potentially hundreds of inboxes and hard drives.  Ready?  Go!

If your method of sharing is to publish it to a PowerPivot server, though, all of that goes away:  Users can interact with the reports but cannot download the files!

    PowerPivot Server Interaction Model

No more accidental or malicious modifications.  Accidental leaks go away, and malicious leaks are confined to results data only – no input data or formulas are visible in the web reports.  And users simply cannot help but always get the latest.

See that, IT?  Those Excel users that are such a thorn in your side (and vice versa) basically have the same problems you do.  The conflict between you over the years – all you needed was better tools.

It’s going to take some adjustment of course.  But peace, cooperation, and better results are worth it 🙂