BI Scorecard: Excel Integration 2

MicroStrategy's new Office product is an Excel-add in that lets users query and refresh an existing report from within a spreadsheet environment (see Figure 1) See image here or from within PowerPoint and Word. When the original report definition or the underlying data changes, so does the spreadsheet. The same report view is accessed via the Web, desktop, and spreadsheet, providing users access via their preferred interface yet maintaining one version of the truth.

Business Objects' add-in product, Business Query for Excel, lets users query a universe via a spreadsheet. ("Universe" is Business Objects' name for a business view, or metadata layer, that shields users from the complexities of the underlying database schema. See Part 1, "Query," for more detail.) Although this product has existed for years, it isn't widely used. Instead, users find it just as easy and faster to export data to Excel. (Note: The acquisition of OLAP@Work in 2000 led to a new version of the product, branded Business Query for Excel MD; this product is quite successful but can query only Microsoft Analysis Services.) Crystal version 10 introduced a new add-in as well, Crystal Enterprise Add-in for Use with Microsoft Excel.

Informatica's PowerAnalyzer offers two approaches to Excel integration. The first uses browser-based pivot tables, providing users with a familiar spreadsheet interface. The second uses an Excel-based macro (see the sidebar, "Methodology Note") to let users refresh the data from within a spreadsheet. In theory, the macro approach minimizes software maintenance compared to an add-in, but downloaded macros may be a security concern in some organizations. In addition, Microsoft's SOAP Toolkit needs to be installed. This toolkit is being phased out and replaced by .Net, forcing Informatica to look for alternative ways to deliver this functionality.

Scheduled Vs. One-time Export

Refreshing data directly from a spreadsheet is one approach to Excel integration. Scheduling a spreadsheet to be automatically generated is another. Here, you may start with an existing report layout and then schedule a query refresh along with the spreadsheet generation. The spreadsheet may be stored in the BI repository or distributed automatically via email. As discussed in Part 3 of this series (see Resources), Business Objects supports scheduled spreadsheet generation from the desktop but not via the InfoView portal. MicroStrategy has similar support via its Narrowcast server, which is not as well integrated as it could be. The other products reviewed here support scheduled output to Excel.

A one-time export to Excel is an organization's greatest challenge to maintaining one version of the truth, but it seems to be the most prevalent. If you're viewing a report that isn't filtered or sorted according to your needs, you simply save the data to Excel and do the analysis there. BI teams must be proactive: If many users are working this way, the BI team must provide better interactivity in the native BI tool or modify the standard report definition. If, however, it's an individual need, then the one-time export may be fine.

What's Exported?

When you export data to Excel, either on a scheduled basis or for a one-time save, what exactly is exported? Vendors historically have dumped all the detailed data into a spreadsheet but increasingly are exporting the formatting, charts, and formulas. All the vendors reviewed here now export formatting (bold, underline, column widths, and currency symbols, for example). Charts and formulas, however, have further to go. Business Objects (either desktop or WebIntelligence) and Cognos ReportNet 1.1 are the only products I've reviewed that export a chart as a native Excel chart that can be further modified. MicroStrategy Office can create a native Excel chart, but the export function doesn't. Other vendors export charts as pictures that aren't linked to the underlying data. Informatica PowerAnalyzer doesn't export charts at all and claims there hasn't been customer demand for it.

Report-based formulas aren't generally exported, either; the values are exported, but the formula itself isn't converted to an Excel formula. When the formula is exported, it provides excellent what-if analysis. For example, if you know a particular customer will be placing a large order, you can change the value in the spreadsheet. The new revenue totals are recalculated automatically. Informatica PowerAnalyzer is an exception in that it converts subtotals to Excel formulas. Microsoft Reporting Services has limited formula conversions when a report field is defined in a certain way. While Information Builders' WebFocus and Actuate's e.Spreadsheet Designer weren't reviewed in depth for this series, they both do better at generating Excel-based formulas.

Microsoft Reporting Services has a unique capability in that hyperlinks for subreports are maintained in the spreadsheet. (For more on subreports, see Part 2, "Reporting," in Resources.) Within the spreadsheet, you can click on a hyperlink that generates a subreport with all the correct filters passed through.

Versions Supported

In looking at Excel integration, pay careful attention to which version of Excel is required and whether there are differences in functionality across the versions. Microsoft Reporting Services, for example, requires Excel XP but is working on support for Excel 2000. Informatica PowerAnalyzer requires Excel 2002 to access Web-based pivot tables, though Excel 2000 is sufficient for exporting data to Excel. Cognos Report Net 1.0 exported only basic reports to a comma-separated values file, but the just-released 1.1 exports to native Excel.

CINDI HOWSON is the president of ASK, a BI consultancy. She coteaches The Data Warehouse Institute's "Evaluating BI Toolsets" and is the author of Business Objects: The Complete Reference (McGraw-Hill Osborne Media, 2003).