We've had a lot of discussion about the evils of spreadmarts and the trouble with spreadsheet hell. If you're not familiar with the phenomenon, it goes as
follows. Last quarter, the regional sales manager challenged her store managers to have a great Q3. She promised a reward for the store manager with highest quarterly sales. At the next quarterly meeting, Manager A says, "Here's my spreadsheet. As you can see, my store had the highest quarterly sales." Manager B says, "No, wait; that's total sales. But I have the smallest store. As you can see from my spreadsheet, I had the highest sales per square foot." Manager C says, "Wait. As my spreadsheet shows, you need to net out returns. When you do that, I have the highest sales per square foot." Finally Manager D says, "Ah, but my store uses 45 square feet for that branded credit card booth. When you take that out, I have the highest quarterly sales."

This happens all the time in real businesses. But the spreadsheet, while very visible, is not the problem. The real problem is an imbalance between formal and informal business intelligence (BI). So that begs the obvious question, what is formal BI and what is informal BI?

Before diving in, I'll admit these terms are not particularly precise. Employees with access to data (corporate or otherwise) and some motivation often create spreadsheets or other reports to help them drive hunches they have into insight. We haven't yet found a way to build software to make people more insightful. But software that lets a person run with a hunch, find data easily and shape that data into something that leads to insight does exist. For most people, that is the spreadsheet. When individuals gather and assemble data, perform analysis on that data and present the results, they are doing BI using ad hoc or informal processes.

Contrast that scenario with a company that manages an enterprise data warehouse and provides reports for its employees. Employees receive reports, read them and perhaps decide on some course of action. Who's doing the BI in this case? Is it the company or the employee? Both entities, the company and the employee, are doing some work or activity. But I would say in this scenario, the activity is applied to an issue of concern to the company or organization. In the first case, certainly when an employee is following a hunch, I would say the activity is applied to an issue of concern to the employee.

So we have a spectrum of focus from employee-centric to company-centric. We also have a spectrum of ad hoc versus prescribed usage of data and tools. Planning, budgeting and forecasting are examples of using prescribed tools and processes with a company focus. Even a company using thousands of spreadsheets to pull together its annual budget is likely using templates to drive some amount of consistency. The users typically invent nothing; they fill in the templates. These formal, company-centric uses of BI are efficient, consistent and accountable.

For an example of an informal use of data and tools to solve an individual's issue, consider the case of a salesperson trying to decide which client to take to tomorrow's baseball game. If you asked five salespeople who they would take, they would all likely mention in some way taking their best customer. But they could easily come up with five different definitions of best. One might take the client who bought the most recently; another might take the client with the largest pending order. Neither of these people is more right than the other. If each grabs a spreadsheet, hunts down the relevant data, optionally creates new calculated columns, sorts it and selects the client at the top of the list, each has done the right thing for his or her business. And I tend to doubt the company cares much that each had his or her own definition of best. These informal, individual-centric uses of BI are agile and creative. They are generally not efficient or accountable.

The case I opened this article with is an example of informal processes applied to an issue with a company focus. More accurately, it's a departmental focus, but the key point is that the store managers using informal and individual processes were working on something shared. You can fault the regional manager for starting a contest without sufficient initial clarity. It happens. Regardless, the scenario illustrates the potential for conflicting results when individuals pursue independent, informal processes to address shared problems. This is the real core of the spreadmart/spreadsheet hell scenario that everyone wants to avoid. To get back to the title of this column, the spreadsheet is not the problem. The real issue is the misapplication of informal processes to team or company problems.

What can we recommend in this situation? Obviously, the same manager should define the goal more specifically. Her employees would likely appreciate if she built a shared scorecard, maybe in a spreadsheet or maybe in a scorecarding program. In doing so, she would define the goal, its calculations and the required data set. Without calling IT, she would have created a little bit of formal BI. A great Microsoft-specific example is managers writing specific queries against the bug database to provide a consistent measure of bugs-per-person, bugs-remaining and other calculations. If you ever want to see some good lawyering, go to a bug meeting without a consistent and shared definition of zero bug bounce. Hit me up in email if you want to know more about ZBB and other bug esoterica.


If you're keeping score at home, we have a matrix of informal versus formal processes applied to company or individual concerns (Figure 1).


Figure 1: Matrix of Informal versus Formal Processes


We discussed three of these boxes. What about the fourth quadrant? Can the company's formal BI infrastructure be relevant to individuals solving individual problems? Can it lend context, consistency and accountability? I think it can and should. To allow the creativity and agility we get from individuals working in the moment, we need to give them simple, intuitive access to corporate data and corporate definitions in tools they already know. For most users that tool is the ... spreadsheet. If we make it easy for users to find and grab data from the corporate BI infrastructure and to use corporate definitions for things such as sales per square foot, we've solved the spreadmart problem using spreadsheets. I've written in the past about creating a monitor-analyze-plan loop. The data and tool integration that supports M-A-P is exactly the infrastructure needed to support the fourth quadrant.

This simple grid shows another opportunity for companies. Can we take the best of what our employees do informally and promote it to apply to the company as a whole? Certainly when clever employees find better practices, we'll discover them, eventually, by noticing better performance. But we can be more agile. Go back to the opening scenario. Maybe the regional sales manager neglected to fully define the contest and measurement up front. But if she agreed with Manager D's definitions, she should be able to promote his analysis to at least the team level. The simplest way to do this is literally share his document via the team's portal site. Going beyond that, can we allow businesspeople, not IT, to define key measures and calculations? If the key business rules that drive the corporate BI infrastructure come from business users in the first place, the company will have the mechanism it needs to capture the best ideas and best practices from its best employees. This suggests new requirements for performance management applications.


Companies that manage to balance informal and formal BI will increase agility and accountability with no loss of consistency or compliance.


Register or login for access to this item and much more

All Information Management content is archived after seven days.

Community members receive:
  • All recent and archived articles
  • Conference offers and updates
  • A full menu of enewsletter options
  • Web seminars, white papers, ebooks

Don't have an account? Register for Free Unlimited Access