BI Scorecard: Excel Integration - InformationWeek

InformationWeek is part of the Informa Tech Division of Informa PLC

This site is operated by a business or businesses owned by Informa PLC and all copyright resides with them.Informa PLC's registered office is 5 Howick Place, London SW1P 1WG. Registered in England and Wales. Number 8860726.

IoT
IoT
Software // Information Management
News
4/19/2004
07:42 PM
Connect Directly
Twitter
RSS
E-Mail
50%
50%

BI Scorecard: Excel Integration

When choosing a business intelligence product, it's easy to skip over what many users care about most: Microsoft Excel integration.

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) 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.

Figure 1 With the new MicroStrategy Office, shared reports on the server populate Excel spreadsheets. Each worksheet represents one report. One workbook can contain multiple reports.

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 [email protected] 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).

We welcome your comments on this topic on our social media channels, or [contact us directly] with questions about the site.
Previous
2 of 3
Next
Comment  | 
Print  | 
More Insights
InformationWeek Is Getting an Upgrade!

Find out more about our plans to improve the look, functionality, and performance of the InformationWeek site in the coming months.

Slideshows
Blockchain Gets Real Across Industries
Lisa Morgan, Freelance Writer,  7/22/2021
Commentary
Seeking a Competitive Edge vs. Chasing Savings in the Cloud
Joao-Pierre S. Ruth, Senior Writer,  7/19/2021
News
How CIO Roles Will Change: The Future of Work
Jessica Davis, Senior Editor, Enterprise Apps,  7/1/2021
White Papers
Register for InformationWeek Newsletters
Video
Current Issue
Monitoring Critical Cloud Workloads Report
In this report, our experts will discuss how to advance your ability to monitor critical workloads as they move about the various cloud platforms in your company.
Slideshows
Flash Poll