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.
The Agile ArchiveWhen it comes to managing data, donít look at backup and archiving systems as burdens and cost centers. A well-designed archive can enhance data protection and restores, ease search and e-discovery efforts, and save money by intelligently moving data from expensive primary storage systems.
2014 Analytics, BI, and Information Management SurveyITís tried for years to simplify data analytics and business intelligence efforts. Have visual analysis tools and Hadoop and NoSQL databases helped? Respondents to our 2014 InformationWeek Analytics, Business Intelligence, and Information Management Survey have a mixed outlook.
Join us for a roundup of the top stories on InformationWeek.com for the week of December 14, 2014. Be here for the show and for the incredible Friday Afternoon Conversation that runs beside the program.