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

“I’ve always believed that the mind was the most dangerous weapon.”

-John Rambo

       …which explains the huge muscles and machine gun, right John?

A View of BI that Henry Ford would have Loved

In part one I described how I grew a bit disheartened by BI back in 2005 – a lot of good thinking was going on, but it wasn’t the kind of thinking that got me jazzed about going to work.

Back when the term was fresh, the word “Intelligence” was so much more energizing than what I ultimately saw in practice.  Where, exactly, was this “intelligence” I’d been promised?

Instead, I saw data being squeezed into fixed molds, tightly secured, modeled by IT (and by committee) and then ultimately delivered to knowledge workers via a handful of tightly managed, supercharged data browsers.

Again, don’t get me wrong.  I understood the necessity of those kinds of stacks.  I understood why the Wild West chaos that grew in the absence of such stacks needed to be reined in.  And I had also personally worked for two teams at MS (I won’t name them) who collected usage metric data by the ton, and yet had no respectable way to explore it (both have since been addressed, but boy was it infuriating at the time).  That all made sense to me.

Bring back the human intellect!

But I had been hoping for better.  In a nutshell, it seemed like centralized BI efforts were spreading the following philosophy:  “Leaving things to human ingenuity has led to chaos, so clearly, the answer is to marginalize the role of the human beings as much as possible.”

Why does it have to be so extreme?  It doesn’t.  We all just needed to break out of our little boxes and see that there were other things possible.  PowerPivot is a HUGE step toward getting the human brain back into the loop.  Even in the short existence of this blog, I’ve shown a number of places where a human brain, operating close to a business problem, can do amazing stuff when it is cut loose.  A few examples, for refresher’s sake…

Breaking out sales data by temperature at the point of sale (using Intenet data):

Joining disparate data sets that IT would never warehouse:

The Bing test team slicing/grouping their data in ways they had not even imagined the day before they tried PowerPivot:

Using conditional formatting and DAX measures to make previously unseen comparisons jump right off the page:

Creating custom calculated groupings in less than a minute using something as simple as the IF() function and a calc column:

And then today, a friend of mine quickly discovering that he has indices in his SQL server that were not helping, just sucking resources.  (As a bonus, he learned this from the slicers themselves, he really didn’t even need a table or chart!)

Self-Service BI = QUALITATIVE Advances, not just Quantitative

Even for a longtime member of the team, this was a relatively recent revelation.  (Alliteration for the win!).

When we started PowerPivot, “Self Service BI” was the mantra.  It still is, to a large degree.  And for us, that generally meant “look at all these business needs that go unmet because of insufficient capacity!  We can fix that!”  So even though we never said it this way, increased Quantity of reporting/analysis was the driving force.

But more generally, we were knocking down a wall.  Yes, that wall was holding back a large quantity of data analysis and reporting.  But you never really know what’s behind a wall, until you knock it down.  Cut people loose, and not only do unmet requests suddenly become met, but previously unformulated questions get asked, and quickly answered.

That’s worth restating, so I will :)

(As an interesting parallel, the same thing happened with us switching to an in-memory store for PowerPivot.  Take off the multi-decade straitjacket enforced by a physical rotating disk with a mechanical seeker head, and what happens?  A series of successive Eureka! moments – “Oh, YEAH, we can do THAT, TOO!  WOW!” – and by the time we were done, we had an engine whose feature set and characteristics far exceeded the original vision.)

So…  a greater quantity of questions get answered.  Questions get answered more quickly.  And perhaps most significantly, better questions get formulated and answered.

Sounds like Agility.  And dare I say it, Intelligence.

I’m glad to be back in BI.

Coming in Pt Three:  Blending the Strengths vs. Turning Back the Clock

When I started to write about this particular topic, I thought it was just going to be a single post.  It turned out to be more :)

So to keep things from running too lengthy, I’m gonna stop here and pick it up next week, when I will cover how this empowerment does NOT mean a reversion to the Wild West of Data.

I remember the first time I heard the term “Business Intelligence.”  I had been working on Excel for awhile at that point and loved crunching data, but boy, “Business Intelligence” sure had an intriguing sound to it.  It conjured up visions of darkened CIA situation rooms, Tom Clancy novels, technology that bordered on sci-fi, and a young Alec Baldwin (who in subsequent movies, transformed into a not so young Harrison Ford, then into Ben Affleck, and now… Captain Kirk?).

“Intelligence” sounded like a brand new direction, an empowering evolution to the Excel toolset that already turns mere mortals into business saviors.  “Sign me up,” I said, and became the lead program manager in charge of Excel 2007’s BI feature set.

Two years later, I was sick of BI and leaving the Excel team to do something less corporate.  It all had just turned out to be so much more formulaic and rote than what the term “Business Intelligence” had promised.  In short, here is what I had learned:

What I Had Learned about BI, Circa 2005

1. “Real” BI was the domain of the IT department, which rightly sought to standardize as much as possible
2. Excel usage, while empowering, was something that IT often regarded as a liability
3. Real BI tools were usually just the visible component of a highly premeditated, IT-prescribed, multi-layer stack
4. Excel users themselves had zero interest in “real” BI tools, and were disappointed that we’d done so much BI work in Excel 2007 instead of other spreadsheet features

I want to be absolutely, 100% clear:  I did not disagree with anything I had learned.  By then I had seen, with my own eyes, the real-world factors driving those trends, and had become convinced they were necessary.  There was nothing wrongheaded about standardization, centralization, and discipline.

No, I was not opposed to what I had learned.  Instead, I was just really disheartened by it.  The whole thing was like Kryptonite to a personality like mine.  I am very much “of the people and for the people.”  Big, top-down, standardizing efforts are not my cup of tea.  If it wasn’t their goal to drain all of the fun and creativity out of things, it certainly seemed like an accepted side effect.  If these things are necessary, was my thought, so be it, but let someone else lead that charge.

So off I went, in 2005, to pursue things that better suited my democratic mindset.

Lo and behold, a couple years later, I was back in BI.  And excited about it.  PowerPivot, of course, was what brought me back.  But I want to be more specific than that, and will do that in part two.

“The thing is, winning covers up a lot of sins.”

-Former Chicago Bull BJ Armstrong, when asked why Phil Jackson was such a great coach

With that interview response, BJ Armstrong became one of my favorite NBA players of all time.  The honesty, the subtlety, and the guts that it took to say that… I loved it.  Phil Jackson had been lionized for years after winning so many titles with the Bulls, and later the Lakers.  And here was a bit player from those Bulls teams basically saying, “Phil wasn’t as impressive as you guys made him out to be.”

So, Phil Jackson looked like a genius to outsiders.  But the most important thing, as implied by BJ Armstrong, was that Phil Jackson had coached a man named Michael Jordan.  Jordan was going to make any coach look good, and a coach with Jordan on his team could do things that would lead other teams (and coaches) to ruin.

Phil continued that particular brand of wisdom in later years, by agreeing to coach the Lakers, who had Kobe Bryant and Shaquille O’Neal.  We never saw Phil take a terrible team and try to rescue it.  Maybe Phil truly was wise, and understood his own good fortune better than the media did.

What does this has to do with BI?  “Winning covers up a lot of sins.”  During boom years, such as the mid to late 90’s and the mid 2000’s, everyone made money.  Financial advisers all looked like geniuses during those years, no matter what stocks/funds they picked.  Real estate investors were whiz kids.  And consumer spending was so hot, you didn’t have to run a tight ship in order to make money.  A rising tide raises all boats.

When we were planning the feature set for Excel 2007, I remember us looking at IT spending trends during the dot-com bust.  Overall IT spend was sharply down.  And all segments of that spending were down as well.  Except BI.  BI spending was up.

The same thing is happening now, and for the same reasons.  Winning covers up a lot of sins.  And adversity demands efficiency.  Effective BI investments are orders of magnitude cheaper than making bad business decisions.

As a software pro, there’s no place I’d rather be than business intelligence.

Adm. Painter: What’s his plan?
Jack Ryan: His plan?
Adm. Painter: The Russians don’t take a dump, son, without a plan.

This article on “10 signs your enterprise might not have a BI strategy” was all over “tweetspace” yesterday, and for good reason – it’s down to earth, concise, insightful, and thought-provoking.

http://blogs.forrester.com/business_process/2009/11/ten-strong-hints-your-enterprise-may-not-have-a-bi-strategy.html 

Do YOU have a plan, like the Russians of Clancyville?  And how does that plan change with PowerPivot?

Here’s the list and my comments on each from a PowerPivot perspective.  Remember, these are just my opinions – they are not the official MS party line.  (They are better, haha). 

And my opinions are subject to change – meaning if you offer an opinion that I like better than mine, I’m happy to adopt it as if it had been mine all along :P

1) Your end users keep pointing to IT as the source of most BI problems

I hear this a lot, even though it isn’t fair in many cases – see 3), below.  The perception is that IT is often where you go to get told “no, we can’t.”

PowerPivot is aimed squarely at allowing the more-numerous business users to share the load with IT – making the business units happier, better-informed, and agile, while IT can focus more attention on the back-end, BI-enabling investments like the data warehouse.

2) Your business executives view BI as another cost center

This certainly contributes to 1), above, don’t you think?  And I suspect that 1) also contributes to 2).  Nice cycle of negative reinforcement there.  Let’s start unwinding it shall we?  Even better, let’s creative a positive cycle of reinforcement.  I suddenly feel like John Lennon.

3) IT staff keep asking end users for report requirements

I assume the author means “IT should be so closely aligned with the end users that they read their minds” or perhaps “there should be an advisory panel of power users from the business unit advising IT at all times.”

Both are good practices – especially mindreading, that’s hot stuff.  But the fact is, there will always be FAR more demand than supply when it comes to reports and analysis, as long as IT is viewed as the only supplier.  Again, one of the driving forces behind the development of PowerPivot.

4) Your BI is supported by IT help desk

Unless it’s a server that’s down, etc., of course.  I think the author means that most BI “service requests” are actually requests for brand new services (sometimes in disguise), and these cannot be serviced by a general-purpose helpdesk, nor can the BI specialists in IT keep their finger on the pulse of the business needs if all that user feedback is going through a filter.

PowerPivot brings much of the report/analysis “supply” much closer to the end consumers – all feedback loops get tighter that way, so no IT requests are required at all in many cases (helpdesk or otherwise).  And those requests that DO flow back to IT can do so through a smaller number of jointly-invested power users.

5) You can’t tell the difference between BI and Performance Management

I’d say this was the question I got the most at the SharePoint conference:  “Wow, PowerPivot sure produces dashboard-style views in a hurry, when do I use this versus PerformancePoint?”

I’ve been getting that kind of question for years, just with different products.  But for once I had an answer I believed in – PerformancePoint is a great example of centralized, premeditated IT-backed BI (ok, Performance Management).  The metrics by which you decide whether your business is performing well – I kinda don’t think you’re going to trust that to a power user.  Centrally defined and centrally delivered.

And of course, BI is a tool for improving performance.  If you’re just measuring, well, there isn’t a lot of intelligence going on is there?

6) You can’t measure your BI usage

No measurement means no customer feedback, and that’s pretty much the death of most anything.  PowerPivot hits this from two sides:  by giving you built-in instrumentation of ALL published reports, and again, by bringing the suppliers closer to the consumers, which brings the benefit of organic, tightly-looped feedback.

7) You can’t measure your BI ROI

When I saw this one, my reaction was basically, “Ah, SWEET!  This guy Boris has been so insightful thus far, I can’t WAIT to read his approach to measuring ROI.”

…and then, the answer to that was behind the Forrester premium content wall.  See, I told you, this Boris guy – VERY wise indeed :)

8) You think your BI strategy is the same as your DW strategy

Yeah.  DW (data warehousing) enables BI.  That remains true, perhaps even more than before, in a PowerPivot environment.

9) You don’t have a plan to develop, hire, retain and grow BI staff

Boris was padding his list here :)  I can pretty much say “if you don’t have a plan to develop, hire, retain and grow people for X, you are going to suck, in epic fashion, at X.”  But even though it might be obvious, I do think it bears repeating.  So Boris gets a pass here :)

10) (My personal favorite) You actually don’t know if your enterprise has a BI strategy!

That was Boris’ favorite, not mine.  I suspect it was his favorite because it was the tenth one :)