PowerPivotPro

REDMOND, WA – New programming language will revolutionize business application development     

OK, yeah, that’s not a real story.  I just made it up, sorry :)

But imagine how perceptions would change if it were true, and instead of being an Office app, Excel were to morph into a professional development tool.  Maybe we’d call it Business.NET or something like that instead of XL#.  A flexible programming environment for codifying business rules, crunching raw data, and visualizing results.  With a rich Windows client runtime library as well as a webserver runtime for processing and rendering on beefy, rack-mounted server farms.

That would be pretty cool wouldn’t it?  Excel gurus could transition from their Excel environments over to Visual Studio without missing a beat, and suddenly they’d be given new job titles like “Lead Business Logic Developer,” “Senior Analytics Programmer,” or “Rapid Modeling Engineer.”

In their new roles, they might be re-org’d into IT.  But they’d retain a very close affinity with the business units themselves – no matter who they reported to, they’d sit on the boundary between IT and the business unit, serving as both diplomat and translator between those two worlds.

And the apps those folks produced would be regarded as very different animals than they are today.  No longer would they labor to produce mere documents.  They would be producing business applications.  And given that increased gravity, everyone, including this new class of developers, would treat these applications in a more disciplined manner.  They wouldn’t just be anonymous files living in shared folders or inboxes somewhere, ready to cause trouble when an external change breaks their assumptions and disrupts the business operations.

Of course, the punchline is, most everything above is already true, except for the human perceptions.

Excel files are NOT documents!  They are applications!

                  Pop Quiz for IT:  Can you spot the critical business app?

Yeah, I know.  Excel is part of the MS Office suite, just like Word, PowerPoint, Outlook, and OneNote.  So clearly, Excel files are just document content, just like those other applications, right?

Nope.  Excel is a development tool.  And no, I don’t mean “if you use VBA.”  The sheets are data structures.  Cell addresses are variable names.  Formulas are the core programming language.  Things like relative reference and formula filldown are the iterative constructs.  Charts and Pivots are data-bound controls.  Excel.exe is both the programming environment as well as the runtime.  (And now Excel Services serves those roles on the web).

(In fact, when I worked on Excel, one of the exercises we used to run through was to compare Excel to other programming platforms, and use those other platforms’ features as inspiration for new spreadsheet features.)

Excel has certainly benefited from being an Office application, so don’t get me wrong.  It would not be remotely as widely-adopted as it is today had it been marketed as a dev tool rather than as an Office app.  But I do want to point out that there as a side effect of being an Office app, the world’s most popular programming tool is perceived incorrectly.  It does not produce documents, it produces applications…  and then those applications get treated with the same informality that documents do.  Like an alien hiding amongst the stuffed animals in Drew Barrymore’s closet.

“OK Rob, I get it.  What’s your point though?”

I’m still thinking my way through this, but I’m pretty sure it is leading me somewhere worth going.

So far, I’m basically thinking that the most successful PowerPivot deployments will involve a new cultural view of the Excel professional.  A view that is at least very similar to the “what if” exercise I played out at the top of this post.

This line of thinking is also in some ways a “sequel” post to the “I” in “BI”, Part Three post.

What does everyone else think?  Are perceptions malleable enough for us to get there?

And credit where credit is due:  Dick Moffat was the inspiration for this post.  If you want to hear someone rail (quite convincingly) about the improper respect given to Excel applications and programmers, look him up on Skype or drop him a line on his blog.

Or even better, drop in at Tony Packo’s next time we get together, like we did this week with Mr. Excel.  Good times, good food, and even better conversation :)

“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.

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.

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!

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 :)