This blog examines past, current, and best practices, techniques, and lessons learned of various business intelligence implementations.

Excel

June 22, 2008

Many experts consider Microsoft Excel to be the most commonly-used tool for data manipulation and analysis.Why?Because it’s familiar and inexpensive (most users already have the Microsoft Office Suite installed on their desktops, so technically, it’s “free”).In fact, in 2005 Microsoft claimed that there were over 150 million Excel users around the world, most of whom are leveraging the tool for reporting purposes.

However, relying on Excel alone for business intelligence can create several major problems.The first is usability.While financial staff and business analysts may be proficient in the most sophisticated Excel features, which are required for the kind of in-depth analysis needed to support effective strategic decision-making, the average business user is not.

Another issue is consistency.If multiple users maintain spreadsheets on separate PCs, there are bound to be concerns regarding accuracy and latency.Data warehousing and business intelligence guru Claudia Imhoff states that Excel is “largely responsible for ‘single version of the truth’ failures in business intelligence”.This can be particularly detrimental when it comes to Sarbanes Oxley compliance, where the validity and integrity of financial data is critical.

Over the past decade, business intelligence vendors have recognized the popularity of Excel as a data analysis vehicle, and realized that spreadsheets simply aren’t going away anytime soon.Excel and BI solutions must work together, linking seamlessly to provide everyone – from the executive to the power user to the front line worker – with timely and valuable insight in the format they’re most comfortable working with.

Today, the various BI applications on the market offer varying degrees of Excel integration. The available Excel-related features can be broken down into three levels:

Simple. Some vendors don’t actually integrate their BI offerings with Excel, but provide an Excel-like interface and spreadsheet-style output.This approach hasn’t been particularly successful, because many die-hard Excel users won’t switch to another tool, no matter how similar and intuitive it is.

Moderate.Many BI solutions provide the ability for users to generate reports in Excel format.This method will certainly satisfy the needs of users who want to analyze information in Excel by allowing them to perform one-time extractions of data into spreadsheets, then save the spreadsheets to their desktops before further manipulating that data.There are some serious limitations, however.For example, there is no central repository of queries and data requests, and no audit trail.

Sophisticated. Some business intelligence applications deliver truly deep Excel integration.The BI tool tracks all extraction and manipulation activities such as ownership, calculations and formulas, formatting, and more.They even support PivotTable output and other advanced features.

But most importantly, Imhoff and other experts agree that it takes more than just seamless integration between a BI environment and Excel to make these two work together the way they should.Internal controls – such as tracking exports, limiting what data can be populated into Excel spreadsheets, and linking the spreadsheets back to the original data source and performing automatic refreshes at periodic intervals – can help guarantee consistency and accuracy in reporting and analysis processes, no matter which tool users prefer.