The current king of business intelligence (BI) tools is Microsoft Excel. Microsoft states that there are over 150 million Excel users, with many of them using it for reporting and analysis of corporate data.

For many years BI vendors have been building front-end tools to try to replace spreadsheets for querying, reporting and analyzing data results. But despite the fact that tens of thousands of BI tool licenses have been sold, spreadsheets are still the most pervasive and dominant tool.

What makes Excel so dominant? It’s on practically everyone’s PC; there are no extra costs; it’s easy to learn; it does the data manipulation and data graphics companies need for most reports and analysis; and many business people have become spreadsheet “jocks.” Even if “real” BI tools were free, Excel already has enough momentum that people would still use spreadsheets for much of their reporting and analysis tasks.

So how do BI vendors feel about this? They’ve initially responded in two ways. First, they tried to develop Excel-like interfaces. Close, but no cigar. Why learn another interface that’s sort of like what you want, but isn’t and can’t do all of what you have been able to do for years? Second, they “integrated” with spreadsheets by exporting data in spreadsheet formats like CSV (without formulas or formatting). This one-way transfer often required business users to reformat and recreate formulas, which didn’t make them or their IT people happy.

The bane of the IT department

Why does IT hate spreadsheets? In addition, why do spreadsheets make some of the people responsible for Sarbanes-Oxley (SOX) compliance cringe? The answer lies in the “worst” practices often associated with spreadsheets and their use in analyzing data.

Data used in the spreadsheet may have been extracted from the corporation’s single version of the truth, i.e., its corporate data warehouse (DW) via a BI tool. This extraction is a one-way trip to a business user’s PC where he manipulates the data to generate the reports and analysis he shares with peers and management. With repeated manipulation and messaging, the quality and integrity of the data begins to degrade.

The fact that multiple business people may extract data from the same source, but use different extract criteria or different manipulations, helps produce inconsistent numbers. Then they spend time reconciling these numbers to determine who is right. These “multiple versions of the truth” become spreadsheet “silos” or “spreadmarts.” It’s a nightmare for data consistency, data integrity and SOX compliance.

Compounding the data integrity problems is the simple fact that although people find time to manipulate data with their spreadsheets, they hardly ever find time to document what they do.

Death to spreadsheets?

Should IT ban the use of spreadsheets? Disable the ability to extract to them? Neither approach is practical, nor would they stop business users from getting data into spreadsheets anyway. Plus, it would be foolhardy — most businesses have already invested in spreadsheets and the business expertise that goes along with them.

Should BI vendors add even more features into their BI tools to try to be better than spreadsheets? Not smart. While beating other BI vendors in head-to-head evaluations is an ego-boost, they are in denial mode if they think they can truly supplant spreadsheets.

The problem is not spreadsheets themselves, it’s the bad habits business people have gotten into when they use them. In fact, BI vendors and the IT staff helped enable this behavior when they promoted Excel integration. What they were really offering was one-way extracts to those spreadsheet silos — a one-way ticket to spreadsheet hell.

Spreadsheets without tears

Is there any way to use spreadsheets for BI environment without making the IT group and auditors run for cover? Yes, you start by making sure you can share and exchange data used in spreadsheets. This sharing needs to include formulas, formatting and graphics, not just raw data. In addition, it’s important to share the intelligence about dimensional and hierarchical structures for such subjects as customers, products and sales territories.

This two-way exchange of data with your DW shifts the data back to the DW, but keeps the strength of spreadsheets at the business user’s fingertips. It also provides a collaborative environment where business people can share data and communicate updates. This creates a closed loop system for BI, avoiding a proliferation of undocumented data silos and the questionable data that goes along with them. Most significantly, it makes business users more productive because it lets them choose their own BI tools. And it supports IT’s efforts to manage and control the spread of data, and comply with SOX.

Several BI vendors have finally embraced spreadsheets. They understand that no matter how cool their own BI tools are, business users think that spreadsheets are cooler. Hyperion Essbase is an example of a BI vendor that has long understood the role spreadsheets play. One of the keys to Hyperion’s success, especially with finance departments, has been its Excel integration. Also, Hyperion has not been afraid to let the spreadsheet be the primary interface that the business user sees. A few of the other vendors that have spreadsheet religion include Actuate, AnalySoft, Applix, Business Objects, IntelligentApps, MicroStrategy, OutlookSoft, QlikTech, ProClarity and, of course, Microsoft.

A final word of caution is that no single BI tool, not even the mighty spreadsheet, is sufficient for a company’s BI. In other articles I will discuss other categories of BI tools and functionality that you should incorporate into your BI portfolio.