Make Peace In The BI-Versus-Excel BattleMake Peace In The BI-Versus-Excel Battle

To fully appreciate Excel's role in BI, start by understanding why users love their spreadsheets. Sometimes it may be personal and job security. Knowledge is power, and being the designated source of data is an enticing role. Beyond job security, there are a number of valid reasons that Excel plays a powerful role in business intelligence, such as:

Familiar user interface - Attendees acknowledged that Excel is the preferred interface for power users, but managers and front line workers prefer dashboards and easier- to- use tools (they were wowed by SAS's Email integration)
Ability to tweak a report, whether to sort, filter, pivot, or remove a column
Extensive formula library
Access to multiple data sources - Excel's ability to combine data from multiple data sources is a must-have requirement for many types of analyses
Ability to "massage" the data - Few attendees said they use Excel for data cleansing, but several people spoke of changing group roll ups.
Ideal prototyping environment where users themselves can build applications.

For any given requirement, assess whether Excel is the best solution or if the BI tool would be better. For example, newer version of BI software allow users to filter, sort, and interact with a report via a browser. Habits are sometimes hard to change, and if users don't know about these capabilities, they will fall back to what they have done for years (click that Export button).

The multiple-data-source issue is probably the biggest challenge. BI teams don't like personal or departmental data to be accessed from a central business view and certainly not from within the data warehouse.

Excel is ideally suited for joining data from multiple data sources for a one-off analysis. The scalability improvements with the PowerPivot feature in Excel 2010 make it even more suitable.

However, for recurring analyses that require joining data from disparate systems, multi-source becomes a problem for the entire organization. The BI team has to recognize this or they risk being less relevant and the cause of spreadsheet chaos. Don't back business users into a corner.

Most BI platforms now offer an Excel add-in that supports data integrity while giving users their data in the familiar Excel interface. It surprized me, though, how few attendees were either aware of or using these add-ins.

Why aren't add-ins being used? In some cases, it's performance and licensing problems. In other cases it seemed to be lack of awareness. There also seemed to be a degree of fear: BI teams shy away from Excel because they've been burned in the past.

My recommendation is to recognize Excel as part of the BI environment. Manage the spreadsheets, whether via SharePoint, a content management system, or the BI portal. Consider carefully when it's appropriate to use Excel versus the BI tool; and in all cases, make available add-ins part of your BI tool portfolio.

By the end of the morning, attendees were more positive, with two thirds agreeing that Excel and BI can be friends. The remaining third are still wary. Where do you stand?

Cindi Howson is the founder of BI Scorecard , an independent analyst firm that advises companies on BI tool strategies and offers in-depth business intelligence product reviews.