SQL Server Executive: Spreadsheets Are Not the Problem

Information Management Magazine, September 2007

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