Business Intelligence and Excel: Happily Married?

In the world of business intelligence, Microsoft Excel is considered by many to be the number-one BI tool. Business people and IT have a love-hate relationship with Excel when it comes to BI because the one thing it does so well — enable rich, individual analysis — can wreak havoc on an organization’s efforts to ensure consistent information. Spreadsheets gone wild undermine business intelligence. Multiple versions of the truth abound, data chaos ensues and organizations end up dumber than when they were without that Excel-enabled analysis.

I've worked with companies that have banned exporting data from BI tools to Excel altogether. I’ve also worked with users who have decried other BI tools, demanding that all data be delivered directly to Excel.

After years of haggling, it seems everyone can now have what they want, with the latest round of BI-Excel integration efforts solving all the world’s problems (okay, not all, but most). Here's a closer look at what leading vendors including Cognos, Business Objects, SAP and Microsoft are doing to make BI and Excel a happy couple.

BI and Excel: The Early Days

The integration of spreadsheets and BI is nothing new and, in fact, has been evolving over the last 20-plus years. It started in the early 1980s when users would take printed green-bar reports and re-key data into the then leading spreadsheet, Lotus 123. Finally, users could make sense of static data, filtering it, creating graphs, enhancing the information with formulas, and performing what-if analysis! The ability to manipulate the data, design reports and gain insight without having to rely on IT made the spreadsheet the most popular interface for data access.

In fact, love of spreadsheets partly explains Oracle-Hyperion Essbase’s immediate popularity. The initial interface to Essbase (short for Extended Spread Sheet database) was a spreadsheet. Users didn’t have to re-key data for analysis; corporate data was immediately presented to them live from within a spreadsheet. It was only later that the company added a dashboard-style viewer.

Not all the world’s data is in a cube, so striving to satisfy customers, BI vendors added easy ways to export data from a BI report to Excel, opening up a Pandora's box in the process. No more re-keying. No more export, import, convert, save as. But if that didn’t create data chaos fast enough, some vendors have gone so far as to support scheduled exports. So now you can have that data chaos at the speed of a clock. Speaking to users at various conferences, I often hear estimates that more than half of all BI reports are exported to Excel.

Coping With Data Chaos

So what’s wrong with this picture? Users got what they asked for. BI vendors should be applauded for delivering on those requirements, right? The problem is that the ease with which data can be exported to Excel means multiple versions of the truth can be created at a rapid pace. Companies cringe at the prospect of thousands of uncontrolled, unvalidated spreadsheets residing on corporate networks. These uncontrolled, unvalidated silos of information support critical business decisions.

The fall out from errors can be huge. For example, in 2005, gift retailer RedEnvelope shares plummeted when cost of goods sold was underestimated due to a spreadsheet error. The CFO resigned. In another example, Kodak had to restate earnings because too many zeros were erroneously entered in a spreadsheet. While such cases make headlines, a more prevalent problem is that business users spend an inordinate amount of time debating whose spreadsheet is right and where their data came from. These discussions detract from time better spent on the business issues at hand.

To address this problem, some IT departments have gone to the extreme of attempting to ban exports of data to Excel. BI vendors have been working to come up with a better solution, one that gives users the flexibility and familiarity of an Excel interface while ensuring data integrity.

Integrations Improve

Over the last three years, leading BI vendors have introduced new products and capabilities that let users have their data in Excel while ensuring data integrity. For the most part, the products are Excel Add-Ins or Plug-Ins, with the exception of Microsoft, which provides native data access via Excel. Vendors have been steadily improving these add-in capabilities with each new release. Recent examples include:

- Cognos 8 BI Analysis for Microsoft Excel (set for release this week) adds the ability to slice, dice and drill on OLAP data sources, whether Cognos PowerCubes, Microsoft Analysis Services or relational data that is dimensionally modeled. As shown in the screen shot at right, the same Framework Manager model displayed to Web-based BI users appears directly within Excel.

- Business Objects XI Release 2 Productivity Pack and Live Office (released this Spring) introduced the ability to create new queries from within Excel while leveraging the product’s "Universe" business metadata layer, (shown at left). Previously, users could only access existing report definitions in either Crystal Reports or Web Intelligence documents. Also in the latest release, users can bring in individual report parts from Web Intelligence in addition to Crystal Reports. So, for example, a chart created in either module can be presented and refreshed directly within PowerPoint.

- Niche BI-Excel vendor XLCubed acquired MicroCharts in August, adding its advanced visualizations such as spark lines (a trendline the size of a word) and bullet graphs (horizontal bars that combine actuals and targets).

- Excel 2007 (released last year) provides more intuitive access to OLAP data, displaying attributes as tool tips, with conditional formatting based on targets of key performance indicators. In this release, Microsoft has focused on usability, replacing the former drag-and-drop capabilities with a simpler point-and-click interface in which users readily see all available cube content from a new PivotTable Field List dialog. This same dialog helps users design report layouts and filter data. Using technology acquired from SoftArtisans in June, Microsoft plans to extend BI-Office integration beyond Excel to Word and PowerPoint.

- Also looking beyond Excel, SAP and Microsoft jointly released Duet, which provides access to reports and alerts through Outlook (as shown at right). When users open a Duet-powered report or alert via email, the content is refreshed live with data from within the Netweaver BI environment.

All of these innovations would make it sound like the road to BI-Excel integration is now wedded in bliss. And yet, the usual squabbles prevail. From a technology point of view, there is little consistency in how these add-ins arrive at users’ desktops. Some products use Smart Client technology that lets users self-install. Other products require an IT-push install. With Smart Client technology, users must first ensure a compatible version of the .Net Framework is installed and that upgrading to a compatible version doesn’t interfere with another application.

Ease of use is sometimes forgotten in the race to make all this integration better. For example, with one product, a new feature is meant to make it easy to filter BI queries based on values in a spreadsheet cell. Instead, a confusing dialog about “bounded” and “unbounded” properties appears to confuse. Another tool requires explicit changes to Microsoft Office Templates in order to work correctly. There is also the issue of product packaging and pricing. Why should users pay for an add-in when the export capability is free? All of these issues have led to modest adoption of the latest solutions as users say doing a conventional export seems easier and cheaper. If only more companies did a cost-benefit analysis of the ensuing data chaos!

BI, Excel and Planning

BI and Excel, then, still have their squabbles to settle. Add planning to the mix and it gets even more complicated. Excel is widely used for planning purposes. In preparing plans, people often start with actuals and develop forecasts from those actuals. Where do these actuals come from? A BI report! In the absence of a planning system, there is no better place to enter plan data than in Excel. Planners need Excel in the BI mix. It’s perfect for entering data, doing what-if-analysis or producing forecasts based on formulas. It only becomes a nightmare when someone has to consolidate, reconcile and version multiple plans. In other words, all the time!

All of these issues are at the heart of Microsoft’s major product release, Performance Point Server 2007. PerformancePoint attempts to take two of Microsoft's greatest assets, Excel and Analysis Services, and address a need that every organization has. While the BI market is still ripe for opportunity and growth, some executives and organizations don’t quite get the value of BI. But planning is something everyone does. Streamlining and bullet-proofing that process would seem a smart thing to do.

In this regard, all those actuals that planners currently get from a BI report reside in a specialized Analysis Services cube. There is no manual export from a BI report, then import into Excel.

Actual and plan data are presented within a spreadsheet as part of the PerformancePoint Planning application (as shown at right). All the what-if analysis that, to date, has been done in a disconnected way is integrated with a server-based model in PerformancePoint. If a manager wants to test the impact of increasing headcount, they can immediately see the impact on margin. Rules from the server-based model highlight margin in green if the change is good, and red if it is below target. Once satisfied with the plan, data within Excel is then submitted to the same Analysis Services cube from which it originated. It’s not the messy, manual reconciliation process that many companies currently go through.

In this way, Microsoft is attempting to marry the best of all worlds:

- Planners get their data in an interface they are most familiar with and in a tool that provides what-if analysis, a powerful formula language, and data entry capabilities,

- Information is stored centrally in an Analysis Services cube to ensure consistency and common business rules, and…

- BI users can access and analyze this information via their preferred tool, whether a report, dashboard, scorecard or Excel.

While all of this would sound like a match made in heaven, this addresses only part of the picture here: the Excel integration. Excel integration with Microsoft Analysis Services and Performance Point gives Microsoft much to brag about, but the same is not true of relational data. Excel, for example, cannot access the business model used in Reporting Services. As well, BI competitors have gone further in the integration of BI with PowerPoint, Word and Outlook.

Excel for Everyone?

While all this integration — from Microsoft and from leading BI vendors — bodes well for Excel users, not everyone is an Excel user. BI penetration has been deepest for power users but modest for executives and paltry for front-line workers. Providing these users with the right interface will be key in ensuring that BI becomes something for everyone.

In the past, BI and Excel were often viewed as separate, competing tools that interfered with each other. Excel could undermine BI’s credibility. Conversely, Excel loyalists often perceived BI as an unnecessary middle-man when it came to access to the data. With all vendors working on ways to improve the integration between the two, such integration can help eliminate data chaos and satisfy a powerful group of BI users.

Cindi Howson, Founder, BIScorecard , a Web site for in-depth BI product reviews Author: Successful Business Intelligence: Secrets to Making BI a Killer App Author: Business Objects XI (R2): The Complete Reference