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.

Software // Information Management
07:42 PM
Connect Directly

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.

Part four in a series of reviews evaluating business intelligence suites one functional area at a time.

One of the biggest mistakes you can make with BI is forgetting the big picture, the strategic goals of what you hope to accomplish by accessing this information. Are you hoping to increase sales by knowing the customer better or by discovering a new business opportunity? Are you hoping to better control costs to provide a competitive advantage? Will you use BI to measure performance against a number of goals?

In evaluating BI toolsets from a functional perspective, it's easy to get so caught up in the feature-by-feature comparison that you lose sight of the business goals of your BI implementation. I've compared selecting a BI tool to buying a car. In buying a car, we seldom talk about how we'll use the car or about the right way to drive. (A Hummer really may not be ideal for commuting 120 miles on the highway each day.) In buying a car, best practices and uses are partly assumed and partly forgotten amidst the excitement of the tangible features: It's so rugged, roomy, trendy! In selecting a BI tool, there's one user-wanted function area in particular in which we tend to skip completely, diving straight into the bells and whistles: It is Excel integration.

Although Excel may be the undisputedly leading BI tool, it's also the leading cause of multiple versions of the truth: Two users execute the same query against one central data warehouse and dump the data into Excel. One user filters the data in Excel with a particular set of criteria and adds some personal formulas. The second user filters it slightly differently and perhaps makes a mistake in a formula. Whose spreadsheet is right? An inordinate amount of time is spent reconciling the multiple versions rather than on gleaning business insight. The same process is repeated each time the query is refreshed and the results re-exported. With the Sarbanes-Oxley Act (SOX), using these processes for financial reporting can cause noncompliance as there is little assurance as to which spreadsheet is valid. Yet financial analysts are often the savviest Excel users. It is the tool they are most comfortable with. Although SOX may cover financial reporting, other regulatory reporting - whether through the EPA for environmental factors or the FDA for food and drugs - has equally strict validation requirements. One department of a pharmaceutical company I've worked with has specifically disabled the ability to export data to Excel just to ensure reporting compliance.

The Importance of Excel Integration

Despite the validity issues around spreadsheets, there are a number of compelling reasons why Excel integration is so important to a BI tool selection:

Tool familiarity. Users have so little time to get the data and then to analyze it that it's often easiest to use a tool they're already familiar with.

Ability to "massage" the data. "Massaging" the data includes re-sorting, filtering, creating formulas, and in some cases, fixing bad data. All of these things should ideally be done at early points in the BI process. In the "Reporting" segment of this BI Scorecard series, I talked about interactivity capabilities of a BI tool that let users re-sort, filter, or hide individual columns within the native BI tool. When this capability is disabled or unavailable, users have little choice but to dump the data into Excel. The more drastic task of actually correcting bad data within an Excel spreadsheet is clearly a nightmare for data consistency. However, if the processes aren't in place to fix bad data at the source or to correct programmatic ETL errors, users will do whatever it takes to create a usable report.

Better charting. Excel charts and all the controls over scaling, axes, and labels have become a de facto benchmark. If the BI tool doesn't provide robust charting capabilities, it's clear that users will want to export the data to Excel to access its charting capabilities. Some BI vendors provide charting options superior to Excel's; for others vendors, Excel is the standard.

Briefing books. Excel's ability to have multiple worksheets stored in one workbook file makes it appealing for management briefing books. Users can access all their data, perhaps populated from multiple data sources and queries in one file, offline. Few BI vendors can replicate this functionality natively. Dashboard capabilities are a robust alternative, but often require a network connection. Here, the manual processes to build Excel-based briefing books must be carefully evaluated against alternative solutions. A few vendors reviewed here offer spreadsheet add-ins that can help automate the process of building and refreshing Excel-based briefing books.

Reduced licensing costs. Companies have already incurred Excel licensing costs. If they can minimize the number of BI users by better leveraging Excel, then the theory goes that they can save on BI licensing costs. However, BI vendors are increasingly broadening the definition of "user." A BI user is no longer an individual who logs into the BI tool, but rather, any user who receives output (including spreadsheets) from the BI tool. Thus this last criterion is an invalid requirement.

One Version of the Truth

The preceding list offers some compelling reasons for integrating Excel with your BI tool. Unless there are strict regulatory requirements, restricting Excel use is pretty much a losing battle. The key, then, is to look for capabilities that provide Excel integration while also ensuring one version of the truth. There are a number of different ways in which a vendor accomplishes this goal. At the "zero support" end of the spectrum is the one-time export into Excel in which there is no audit trail of this export nor is there a link to the centrally maintained query. At the other "good support" end of the spectrum, the BI tool tracks ownership and changes made to the Excel spreadsheet, then stores the spreadsheet within the BI repository. The Excel spreadsheet can be refreshed with new data, typically by maintaining a link to the original query file. As the scorecard in Table 1 shows, there isn't one single feature that guarantees "one version of the truth"; it is fulfilled in part by features the vendor provides and in part by processes that you must implement.

Table 1 Scorecard comparing Excel integration in several BI suites.

We welcome your comments on this topic on our social media channels, or [contact us directly] with questions about the site.
1 of 3
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.

Remote Work Tops SF, NYC for Most High-Paying Job Openings
Jessica Davis, Senior Editor, Enterprise Apps,  7/20/2021
Blockchain Gets Real Across Industries
Lisa Morgan, Freelance Writer,  7/22/2021
Seeking a Competitive Edge vs. Chasing Savings in the Cloud
Joao-Pierre S. Ruth, Senior Writer,  7/19/2021
White Papers
Register for InformationWeek Newsletters
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.
Flash Poll